Hoþgeldiniz           
   
"SQL Server baþvuru kaynaðýnýz"
Skip Navigation Links
=========
Anasayfa
Makaleler
Hatalar \ Çözümler
Duyurular
Diðer
Ýpuçlarý
Yararlý Adresler
Mesaj TahtasýExpand Mesaj Tahtasý
HakkýmdaExpand Hakkýmda
Ýletiþim
Kullanýcý Adý:
Þifre:
 

Ne Mutlu Türküm Diyene!

SQL Server ile ilgili soru sormak için buraya týklayýn!


Yazýlarýmý nasýl buluyorunuz?






En Son SQL Server ile Ýlgili Okuduðum Kitaplar
- Inside Microsoft SQL Server 2005: Query Tuning and Optimization - MS Press (Ýngilizce)
- Accelerated SQL Server 2008 - Apress (Ýngilizce)
- Designing and Optimizing Data Access by Using SQL Server 2005 - MS Press (Ýngilizce)
- Microsoft SQL Server 2005 Database Solutions Design - Wiley Publishing (Ýngilizce)- Optimizing and Maintaining a Database Administration Solution by Using SQL Server 2005 - MS Press (Ýngilizce)- Designing a Database Server Infrastructure Using SQL Server 2005 - MS Press (Ýngilizce)- SQL Server 2005 Implementation and Maintenance - MS Press (Ýngilizce)
- SQL Server 2005 Administrators Companion - MS Press (Ýngilizce)


                          
Eksik Index' ler ve DMV' ler

Son güncelleme tarihi: 26 Ocak 2009
  


Merhaba 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
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
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


Anasayfa


 
Bu sitenin tüm haklarý, Ekrem Önsoy' a aittir.