SQL Server’da Gereksiz Indexleri Tespit Etme ve Temizleme

Veritabanı performansı söz konusu olduğunda indexler çoğu zaman hayat kurtarıcıdır. Ancak her iyi şey gibi, fazlası zarar.

Zamanla oluşan gereksiz veya kullanılmayan indexler:

• Insert / Update / Delete işlemlerini yavaşlatır
• Disk kullanımını artırır
• Backup sürelerini uzatır
• Maintenance maliyetini yükseltir

Peki bu “gereksiz” indexleri nasıl tespit ederiz?

Yaklaşım

SQL Server, index kullanım istatistiklerini sys.dm_db_index_usage_stats DMV’si üzerinden bize verir. Bu DMV sayesinde bir indexin:

• Kaç kez seek aldığı
• Kaç kez scan yapıldığı
• Kaç kez lookup kullanıldığı
• Kaç kez update edildiği

bilgilerine ulaşabiliriz.

Mantık oldukça basit, kullanılmayan ama sürekli güncellenen indexler sislinmeye aday indexlerdir.

Kullandığım Query

Aşağıdaki sorgu, özellikle hiç kullanılmayan ya da çok az kullanılan nonclustered indexleri tespit etmek için oldukça pratik bir yöntem sunar:

SELECT

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

, ‘DROP INDEX ‘ + QUOTENAME(i.name)

+ ‘ ON ‘ + QUOTENAME(s.name) + ‘.’

+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS ‘drop statement’

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 dm_ius.database_id = DB_ID()

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 ,o.name

GO