SQL Server DMV’ ler ile Eksik Index’ leri Sorgulama Identify Missing Indexes Using SQL Server DMVs

Asıl konumuza geçmeden önce SQL Server’da DMV-DMF kavramı nedir önce buna bir bakalım.

DMV – DMF nedir?

SQL Server da Dynamic Management View(DMV) ve Function lar (DMF), sistemi monitor etmek, herhangi bir problemi teşhis etmek veya sistemi daha performanslı çalışması için tune etmek için kullanılan, sistem durumu hakkında bilgi dönen view ve function’lardır.

Örneğin sisteminizde bir performans sıkıntısı var.

·         Şu an devam eden işler ve çalışan query’ler nedir?

·         Genel olarak SQL Server’ın işi yapmak için beklediği noktalar nereler?

·         Network IO’larında bekleme var mı?

·         Disklerin response time’ları nasıl?

·         Şu an diskte bekleyen herhangi bir IO işlemi var mı?

·         Eksik index’lerimden dolayı mı performans sıkıntım mı var? Bu eksik index’ler nelerdir?

·         Index’lerimin scan,seek yani kısaca kullanım oranları nelerdir?

·         En çok CPU ya da IO tüketen sorgularım hangileri?

gibi performans problemlerini çözmeye yönelik bir çok DMV-DMF SQL Server tarafından bize sağlanmaktadır.

Dynamic Management View(DMV) lara normal view lar gibi select çekilerek erişilebilir.

Select * from sys.dm_os_wait_stats

Dynamic Management Function(DMF) ler ise input parametresi alıp bu parametreye göre sonuç  döndürürler.

SELECT * FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Bu örneklerde sys.dm_exec_requests bir DMV, sys.dm_exec_sql_text ise bir DMF dir.

SQL Server da 2 çeşit DMV-DMF vardır. Bunlar;

·         Server kapsamındaki DMV-DMF’ler. Bu DMV ve DMF’ler server side bilgileri verirler ve bu objeleri kullanabilmek için VIEW SERVER STATE yetkisine ihtiyaç vardır.
GRANT VIEW SERVER STATE TO loginName

·         Database kapsamındaki DMV-DMF’ler. Bu DMV ve DMF’ler ise database level’ındaki bilgileri verirler ve bu objeleri kullanabilmek için VIEW DATABASE STATE yetkisine ihtiyaç duyulur.
GRANT VIEW DATABASE STATE TO user

Tüm DMO listesine aşağıdaki sorgu ile erişebilirsiniz.

select name, type_desc

from sys.all_objects

where name like ‘dm_%’

order by name

Bu sorgu size, sunucunuzda bulunan tüm DMV-DMF listesini verecektir. Bu sorgu benim instance’ımda 136 sonuç getirmekte.

Son olarak şu bilgiyi verelim. DMV ve DMF’lerin ilk harfi Dynamic manasına gelmektedir. Bu view ve functionlar son SQL Server restart’ından sonra oluşan bilgileri sorgularlar. Yani her SQL Server restart’ında DMV ve DMF’ler sıfırlanır.

Şimdi bugünkü konumuza yani DMV’ler ile Eksin Index’lerin nasıl sorgulanacağı konumuza dönelim.

DMV’ler ile Eksik Index’leri(Missing Index) 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

Üzerinde duracağımız kolonlar şu şekilde;

·         index_handle : Bu kolonu master DMV’lere join için kullanacağız.

·         database_id : Eksik olan index’in bulunduğu DB

·         object_id : Eksik olan index’in bulunduğu table

·         equality_columns : Index’te eşitlik olan kolonlar. Birden fazla kolon “,” ile ayrılır.

·         inequality_columns : Index’te eşitlik olmayan kolonlar. Birden fazla kolon “,” ile ayrılır.

·         included_columns : Index te included olması gereken kolonlar. Birden fazla kolon “,” ile ayrılır. Bu 3 kolonu örneğimizde detaylı inceleyeceğiz.

·         statement : Eksik olan Index’in bulunduğu table

sys.dm_db_missing_index_columns

Bu DMF’de eksik olan index’in kolonları hakkında bilgi verir. sys.dm_db_missing_index_group_details ten en büyük farkı her bir kolon için bir satır olarak bilgi döndürmesidir.

select * from sys.dm_db_missing_index_columns(index_handle)

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 avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) 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.

İyi çalışmalar