DMV'ler ile Eksik Index'leri Sorgulama
SQL Server da index kullanımının önemine hemen hemen her yazımda değiniyorum.Etkili index kullanımının performans’a inanılmaz artı etkiler yarattığını hepimiz biliyoruz. Yeni bir uygulama geliştirirken index’leri ihtiyaca göre create ediyoruz. Fakat uygulama yaşamaya devam ettikçe yeni index ihtiyaçları ortaya çıkıyor. Bir kısmı bizim kontrolümüz altında olup farkedilebilirken bir kısmını gözden kaçırmamız mümkün.
Bugün anlatacağım 4 DMV ile SQL Server’ın bize create edilmesini önerdiği eksik index’leri nasıl sorgulayacağımızı göreceğiz.
Aslında mantık çok basit. Her sorgu database engine’den geçiyor ve database engine gelen sorgu için bir query plan oluşturarak en optimum kullanımı bulmaya çalışıyor. Bu optimum kullanımı oluştururken index’leri kullanmaya çalışıyor fakat eğer bizim sorgumuza uyan bir index yok ise bunuda az sonra belirteceğim DMV’lerde log’luyor.
Kullanacağımız DMV ve DMF ler şu şekilde;
-
sys.dm_db_missing_index_group_stats - DMV
-
sys.dm_db_missing_index_groups - DMV
-
sys.dm_db_missing_index_details - DMV
-
sys.dm_db_missing_index_columns - DMF
Kısa kısa bu DMV’leri tanıyalım.
sys.dm_db_missing_index_group_stats
Eksik index’ler hakkında özet bir bilgi sunar.
select * from sys.dm_db_missing_index_group_stats
Üzerinde duracağımız kolonlar şu şekilde;
-
group_handle : Grup’u belirten ID dir. Server bazında unique değer içerir.
-
unique_compiles : Kaç adet compilation ve recompilation’ın bu eksik index’ten etkilendiğini gösterir.
-
user_seeks : Eksik olan bu index’e kaç defa seek yapıldığını gösterir.
-
user_scan : Eksik olan bu index’e kaç defa scan yapıldığını gösterir.
-
last_user_seek : En son ne zaman seek yapıldığını gösterir.
-
last_user_scan : En son ne zaman scan yapıldığını gösterir. Last tarihler bizim için önemli değerlerdir. Seek ve scan adetleri fazla olabilir ama belkide artık ihtiyaç kalmamıştır ve uzun süredir bu eksik index kullanılmıyordur. Bunu kontrol etmek için Last tarih değerlerini kullanabiliriz.
-
avg_total_user_cost : Eksik olan bu index tanımlanırsa eğer query cost’unda ne kadarlık bir düşme olacağını belirtir.
-
avg_user_impact : Eksik olan bu index tanımlanırsa eğer query cost’unda yüzde olarak ne kadarlık bir etki yaratacağını belirtir.
sys.dm_db_missing_index_groups
Bu DMV’yi sys.dm_db_missing_index_group_stats ile sys.dm_db_missing_index_details DMV sini join’lemek için kullanacağız.
sys.dm_db_missing_index_details
Eksik index hakkında kolon bilgileri gibi detaylı bilgileri döndürür.
select * from sys.dm_db_missing_index_details
Sorgu sonucu gelen kolon ve açıklamaları şu şekildedir.
-
column_id : Kolon’un ID si
-
column_name : Kolon’un adı
-
column_usage : Kolon’un query de ne şekilde kullanıldığı. Alabileceği değerler EQUALITY, INEQUALITY ve INCLUDE
Teorik bilgilerden sonra şimdi sorgularımıza başlayalım. İlk olarak eksik index kaydı oluşturmak için AdventureWorks DB sinde 2 tabloya index olmayan kolonları kullanarak select çekiyorum.
select * from AdventureWorks.Person.Contact where FirstName='Ahmet'
select * from AdventureWorks.Person.Address where City='Ankara'
Contact table’ındaki “FirstName” ve Address table’ındaki “City” kolonları index kolonlar değil. Dolayısıyla eksik index sorgularımda bu kolonları göreceğim diye düşünüyorum.
Eksik Index Sorgulama
Aşağıdaki sorgu ile eksik index’leri sorgulayacağım. Sorgumu <I style="mso-bidi-font-style: normal">avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)</I> değerine göre DESC olarak dizeceğim ve eksik olan bu index’lerden en önemli yani tanımladığım anda sistemime en çok yararı olacak ilk 10 index’e bakacağım.
Beklentim Contact tablosu için FirstName kolonu, Address tablosu içinde City kolonunu bu raporda görmek.
select TOP 10 DB_NAME(id.database_id) as databaseName,
id.statement as TableName,
id.equality_columns,
id.inequality_columns,
id.included_columns,
gs.last_user_seek,
gs.user_seeks,
gs.last_user_scan,
gs.user_scans,
gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) as ImprovementValue
from sys.dm_db_missing_index_group_stats gs
INNER JOIN sys.dm_db_missing_index_groups ig on gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details id on id.index_handle = ig.index_handle
order by avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) desc
Beklediğimiz gibi Contact ve Address tablosu için eksik index bilgisi raporlandı. Ve bu kolonların eşitlik kolonları olduğu bilgisini görüyoruz. Şimdi aşağıdaki select i çekelim ve inequality ve included kolonlarını inceleyelim.
select * from AdventureWorks.Sales.Customer where AccountNumber<>'ABC'
Tekrar eksik index sorgusunu çektiğimizde;
Customer tablosu için inequality ve included kolon bilgileri ile bir index tavsiyesinde bulunuldu.
Eksik Index Sorgulama - 2
Bu sorgulamamızda eksik index’in kolon bilgilerini “,” ile değil her birini ayrı bir satırda almak istiyoruz.
SELECT ig.*, statement AS table_name,
column_id, column_name, column_usage
FROM sys.dm_db_missing_index_details AS id
CROSS APPLY sys.dm_db_missing_index_columns (id.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS ig ON id.index_handle = id.index_handle
ORDER BY ig.index_group_handle, ig.index_handle, column_id;
Sizde periyodik olarak yukarıdaki eksik index sorgularını kullanarak sisteminizde olması gerekip olmayan index’leri sorgulayabilir ve performans artışı için bu indexlerin oluşturulmasını gündeme getirebilirsiniz.