ÖNEMLİ NOT:
Bu sayfada tablolar düzgün görünmüyor, bu nedenle bu yazının aynısını aşağıdaki adresten okumanızı tavsiye ederim:
http://www.ekremonsoy.net/makaleler/sql/eksik_indexler/eksik_indexler.aspxMerhaba arkadaşlar,
Muhtemelen çoğunuzun da bildiği gibi, SQL Server 2005 ile birlikte Dynamic Management Views (DMV) ve Dynamic Management Functions (DMF) diye adlandırılan kullanımı kolay, pratik ve işlevsel olan araçlar da kullanımımıza sunuldu.
Bu yazımda, tüm DMV veya DMF' lerden bahsetmeyeceğim; hatta sadece DMV' lerden ve yazının başlığından da anlaşılacağı üzere sadece Index' ler konusunda kullanılabilecek 3 tane DMV' den bahsedeceğim sizlere. Bu DMV ler:
- sys.dm_db_missing_index_group_stats
- sys.dm_db_missing_index_groups
- sys.dm_db_missing_index_details
Query Optimizer bir Sorgu Planı (Query Plan) oluşturduğunda, bu plan için en iyi olabilecek Index' leri analiz eder. Eğer kullanılabilecek en uygun Index' ler oluşturulmamışsa, o zaman Query Optimizer elindeki yapıyla oluşturabileceği en uygun planı oluşturur ama yaptığı analiz sonucunda uygun bulduğu en iyi Index' ler hakkındaki bilgiyi de saklar. Yukarıda listelediğim DMV' ler ile de Query Optimizer' ın kaydettiği bu bilgilere ulaşabiliriz ve bu bilgileri yorumlayarak, sistemimize en uygun olabilecek Index' leri kendimiz oluşturabiliriz. Bu DMV' ler bize ihtiyacımız olan çoğu bilgiyi sağlıyorlar. Meselâ hangi tabloda, hangi alanların kullanıcılarca çok sorgulandığı ve eğer xxx alanında xxx alanlarını kapsayan bir Index olması durumunda yapılan sorguların % kaç performans kazanabileceğine kadar...
Bu DMV de neyin nesi?
DMV' ler size, SQL Server sunucunuzun sağlıklı çalışabilirliğini gözlemleyebileceğiniz, sorunları teşhis edebileceğiniz ve performans düzenlemeleri yapabileceğiniz bilgileri verir.
DMV' leri kullanırken sadece isimlerini vererek kullanamazsınız, en azından Schema isimlerini de belirtmelisiniz. Bu nesnelerin tümü, "sys" isimli Schema' nın altındadır ve hepsinin de isimleri "dm_" karakterleriyle başlar. Örneğin: sys.dm_db_missing_index_group_stats gibi.
Bu DMV' den dönen sonuçlar, veritabanlarına yapılan her sorgudan sonra güncellenir. Bu yazıda değindiğim DMV' lerden gelecek bilgilerin, SQL Server Instance' ının servisinin yeniden başlatılması halinde sıfırlanacağını dikkate almanız gerekiyor. Çünkü servis yeniden başlatıldıktan sonra kısa zamanda büyük ihtimalle henüz sağlıklı bir Index oluşturma kararına varabileceğiniz kadar veri birikmiş olmayacaktır. Bu tür analizleri yaparken yeteri kadar verinin biriktiğinden emin olmalısınız.
DMV' leri aynen normal View' ler gibi sorgulayabilirsiniz, örneğin:
SELECT TOP 10 *
FROM sys.dm_db_missing_index_group_stats;
GO
Bazı DMV' ler doğrudan sorgulanamaz ve parametre ister, bunu da not etmekte fayda var.
Ayrıca, DMV ve DMF' lerin iki türü olduğunu da belirtmek lâzım. Bunlar:
- Sunucu düzeyinde çalışan DMV ve DMF' ler. Bu düzeydeki bir DMV ve DMF' i çalıştırmak için kullanıcının sunucu düzeyinde VIEW SERVER STATE iznine sahip olması gerekir. *
- Veritabanı düzeyinde çalışan DMV ve DMF' lerdir. Bu düzeydeki bir DMV ve DMF' i çalıştırmak için ise kullanıcının VIEW DATABASE STATE iznine haiz olması gerekmektedir. *
* Bununla birlikte, elbette ilgili nesne için de SELECT izinlerinin bulunması gerekiyor. Bu yazıda değindiğim dört DMV de, "master" sistem veritabanındadır.
sys.dm_db_missing_index_group_stats
Eksik Index grupları hakkında bilgi verir. Aşağıda, bu DMV' nin içerdiği ve benim en çok işe yarayacağını düşündüğüm alanları ve bu alanların açıklamalarını vereceğim.
Alan adı Veri tipi Açıklama
group_handle int Bir eksik Index grubunu tanımlar.
unique_compiles bigint Bu Index grubundan yararlanabilecek derlenen ve tekrarlı derlenen sorgu sayısı.
user_seeks bigint Gruptaki Index oluşturulsaydı, bu alanda belirtilen sorgu sayısı kadar sorgu için bu Index kullanılabilecekti. (Arama işlemi için) *
user_scans bigint Gruptaki Index oluşturulsaydı, bu alanda belirtilen sorgu sayısı kadar sorgu için bu Index kullanılabilecekti. (Tarama işlemi için) *
avg_total_user_cost float Gruptaki Index' in kullanılması halinde, yapılacak sorgulamalar için ortalama olarak düşürülecek maliyet miktarı.
avg_user_impact float Bu Index' in oluşturulmasıyla kullanıcı sorgularının edinilebileceği ortalama yüzde miktarı. Bu değer şu anlama geliyor: eğer bu Index grubu oluşturulmuş olsaydı, sorgunun maliyeti bu yüzde kadar düşürülebilirdi.
* Bu alanlarda belirtilen Seek ve Scan işlemleri, Index Seek ve Index Scan işlemleridir. Bu işlemler başlı başına ayrı bir konu olduğu için bu yazımda değinemem. Sadece kavramların havada kalmaması için bir not koymak istedim.
** Diğer alanlar hakkında da bilgi almak istiyorsanız, Books Online' daki ilgili sayfayı ziyaret edebilirsiniz: http://msdn.microsoft.com/en-us/library/ms345421(SQL.90).aspx
Aşağıdaki örnekte, kullanıcı sorgulamaları için en yüksek performans artışı sağlayabilecek 10 eksik Index' i buluyoruz:
SELECT s.group_handle, s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek , s.unique_compiles
FROM sys.dm_db_missing_index_group_stats s
ORDER BY s.avg_user_impact desc
Bu sorguyu çalıştırdığınızda alacağınız sonuç tek başına yeterli olmayacaktır. Bu sorguyla birlikte diğer "missing_index" DMV' lerini de kullanmamız gerekecek, böylece hangi alanları kullanarak Index oluşturabileceğimiz bilgisini de edinebileceğiz.
sys.dm_db_missing_index_groups
Belli bir Index grubunda hangi eksik Index' lerin bulunduğu hakkında bilgi verir. Aşağıda, bu DMV' nin içerdiği alanları ve bu alanların açıklamalarını vereceğim.
Alan adı Veri tipi Açıklama
index_group_handle int Bir eksik Index grubunu tanımlar.
index_handle int index_group_handle ile belirlenen gruptaki eksik Index' leri belirler.
Bu DMV için örnek yapmıyorum, çünkü tek başına kullanımı anlamlı hiçbir şey vermeyecektir. Bu DMV dm_db_missing_index_group_stats ile dm_db_missing_index_details arasında köprü kurmak için kullanılabilir. Bir sonraki başlık olan dm_db_missing_index_details örneğinde göreceksiniz.
sys.dm_db_missing_index_details
Eksik Index hakkında ayrıntılı bilgi verir. Aşağıda, bu DMV' nin içerdiği alanları ve bu alanların açıklamalarını vereceğim.
Alan adı Veri tipi Açıklama
index_handle int Belli bir Index' i tanımlar. Tanımlama numarası sunucu çapında eşsizdir.
database_id smallint Eksik Index' in bulunduğu tablonun hangi veritabanında olduğunu tanımlar. Veritabanının ID' sini döndürecektir; eğer veritabanının adını görmek isterseniz o zaman bu alanı SELECT cümleciği içerisinde OBJECT_NAME(database_id) olarak tanımlayabilirsiniz.
object_id int Eksik Index' in hangi tabloda olduğunu tanımlar.
equality_columns nvarchar(4000) Eşitlik şartlarının bulunduğu ve virgüllerle ayrılmış alanların listesini verir. (Örn: tablo.alanAdı = değer)
inequality_columns nvarchar(4000) Eşitlik olmayan şartların bulunduğu ve virgüllerle ayrılmış alanların listesini verir. (Örn: tablo.alanAdı > değer)
"=" den başka tüm operatörler eşitlik olmayan şartlar listesine girer. Operatörler hakkına daha fazla bilgi almak için buraya tıklayın (İngilizce).
included_columns nvarchar(4000) Covering Index* oluşturmak için kullanılacak alanların listesini virgüllerle ayrılmış şekilde verir.
statement nvarchar(4000) Eksik Index' in bulunduğu tablonun adı.
* Özet: Covering Indexes \ Included Columns, SQL Server 2005 ile gelen bir yeniliktir. Özetlemek gerekirse, Key alan olarak bir Index' e en fazla 900 Byte' lık alan ekleyebilirsiniz. Bazı durumlarda bu değerden daha büyük alanları eklemek gerekebilir, işte SQL Server 2005 ile gelen Included Columns özelliği sayesinde artık Index' lere daha fazla alan ekleyebiliyoruz. Bu alanlara da Non-Key Alanlar deniyor. Bu konuda daha fazla bilgi için buraya tıklayın (İngilizce).
Bu DMV' yi tek başına kullanmak bayağı zahmetli olabilir. Bu nedenle ben daha ziyade önceki iki DMV ile birlikte hepsini kullanmayı tercih ediyorum. Meselâ aşağıdaki örneğe bakın:
SELECT d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek ,s.unique_compiles
FROM sys.dm_db_missing_index_group_stats s , sys.dm_db_missing_index_groups g , sys.dm_db_missing_index_details d
WHERE s.group_handle = g.index_group_handle AND d.index_handle = g.index_handle order by s.avg_user_impact desc
Önemli Notlar
Yukarıda bahsettiğim DMV' lerden alacağınız eksik Index bilgileri sadece tablolarınıza karşı çalıştırılan SELECT sorgularından edinilmiştir. Yani bu DMV' ler tarafından verilen bilgilerin INSER\UPDATE ve DELETE işlemlerine ne gibi etkilerinin olacağı bilgisi yoktur. Bu nedenle bu DMV' lerden aldığınız bilgilere %100 güvenerek Index oluşturmamalısınız.
Benim size bu noktadaki tavsiyem dm_db_index_usage_stats DMV' sini kullanarak, oluşturduğunuz Index' lerin kullanımını da gözlemeniz. Eğer bu DMV ile aldığınız bilgilerde, oluşturduğunuz Index' lere karşı yapılan SEEK ve SCAN işlemleri UPDATE işlemlerinden daha az ise o zaman bu Index' in size çok faydalı olacağını söylemek yanlış olurdu.
dm_db_index_usage_stats' i çalıştırmak size zahmetli geliyorsa o zaman SQL Server Management Studio 2005 ile birlikte gelen raporları da kullanabilirsiniz. Meselâ SSMS' i açtıktan sonra bir veritabanının üzerinde farenin sağ tuşuna tıklayın, açılan menüden de Reports -> Standard Reports -> Index Usage Statistics' e tıkladığınızda dm_db_index_usage_stats DMV' si kullanılarak alınmış bir raporu görebilirsiniz. Bu rapordaki veriler ışığında da hangi Index' lerin ne kadar çok, hangilerinin ne kadar az kullanıldığını belirleyebilirsiniz.
Ekrem Önsoy
Resim-1
