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.

* 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.

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.

* Ö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