DMV-DMF Kullanımı-3(Kullanılmayan Indexlerin Tespit Edilmesi)

Bir önceki makalemde kullanılmayan  veya çok az kullanılan indexlerin query performansını  düşürdüğünü belirtmiştim. Sadece Select işlemleri için değil hatta özellikle insert ve update işlemlerinde kullanılmayan indexler performansı aşağılara çekmektedir.

Indexlerin bütün kullanım bilgileri sys.dm_db_index_usage_stats adlı  DMV'de tutulmaktadır. Bu DMV diğer sys.indexes, sys.objects, sys.schemas ve sys.partitions adlı DMVlerle birlikte sorgulandığında bize index kullanımları ile ilgili ayrıntılı bir bilgi verebilir.

Aşağıdaki script bize index kullanımı ile geniş bir kullanım bilgisi vermektedir. Bu tablo kullanılarak  kullanılmayan indexler silinebilir.  Ancak önceki makalelerimde belirttiğim gibi DMV ve DMFler her SQL Server restart olduğunda yeniden doldurulduğu için SQL Server başladıktan sonra belirli bir süre çalıştırılmış olması Best Practicedir.

SELECT TOP 250 db_name(dm_ius.database_id) As DbName
,o.name As ObjectName
,i.name As IndexName
,i.index_id As IndexID
,dm_ius.user_seeks As UserSeek
,dm_ius.user_scans As UserScans
,dm_ius.user_lookups As UserLookups
,dm_ius.user_updates As UserUpdates
,p.TableRows
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id=dm_ius.index_id and
dm_ius.object_id=i.object_id
INNER JOIN sys.objects o ON dm_ius.object_id=o.object_id
INNER JOIN sys.schemas s on o.schema_id=s.schema_id
INNER JOIN (SELECT SUM(P.ROWS) TableRows,p.index_id,p.object_id
FROM sys.partitions p GROUP BY p.index_id,p.object_id)p
on p.index_id=dm_ius.index_id and dm_ius.object_id=p.object_id
where OBJECTPROPERTY(dm_ius.object_id,'IsUserTable')=1
and i.type_desc='nonclustered'
and i.is_primary_key=0
and i.is_unique_constraint=0
order by (dm_ius.user_seeks+ dm_ius.user_scans+dm_ius.user_lookups) ASC