Veritabanı sistemlerinde index oluşturmak önemli bir performans adımıdır. Ancak her tablo üzerinde mutlaka index oluşturulmalı diye bir kural bulunmamaktadır. Önemli olan doğru tablo üzerinde doğru index’leri oluşturmaktır. Oluşturduğumuz indexler okumada hız kazandırabilir ancak yazmada gecikmeye neden olmaması için belli dönemlerde indexleri gözden geçirmede fayda vardır. İndexlerin kullanım bilgilerine sys.dm_db_index_usage_stats DMV’den erişebiliriz. Bu view, indexlerin ne zaman oluşturulduğunu, güncellendiğini, kaç defa scan veya seek edildiğini, sistemin veya kullanıcının ne zaman başvurduğunu döndürür. MSDN’den alınmış aşağıdaki script’i kullanarak atıl indexleri bulabiliriz.
SELECT CAST(OBJECT_NAME(idx.OBJECT_ID) AS VARCHAR(30)) AS 'Object', CAST(idx.name AS VARCHAR(50)) AS 'Index', idx.index_id AS 'Id', CAST(idx.type_desc AS VARCHAR(20)) AS 'Type', CAST(col.index_columns AS VARCHAR(180)) AS 'Columns' FROM sys.indexes idx LEFT outer join sys.dm_db_index_usage_stats usg ON usg.OBJECT_ID=idx.OBJECT_ID and idx.index_id=usg.index_id and usg.database_id = DB_ID() LEFT outer join (SELECT DISTINCT OBJECT_ID, index_id, STUFF((SELECT ','+COL_NAME(OBJECT_ID,column_id ) AS 'data()' FROM sys.index_columns idx_col2 WHERE idx_col1.OBJECT_ID = idx_col2.OBJECT_ID and idx_col1.index_id = idx_col2.index_id FOR XML PATH ('')),1,1,'') AS 'index_columns' FROM sys.index_columns idx_col1 ) col ON col.index_id = idx.index_id and col.OBJECT_ID = idx.OBJECT_ID WHERE OBJECTPROPERTY(idx.OBJECT_ID, 'IsIndexable') = 1 and usg.index_id IS null and idx.type_desc not in ('heap','clustered')
Örneğin ay sonlarında bu scripti çalıştırarak o dönemde hiç kullanılmamış indexleri bulup DROP etmekte fayda var.
Aynı şekilde bu DMV’yi kullanarak tablo ve onlara bağlı index’lerin kullanım bilgilerini izleyebiliriz. Aşağıdaki script tablo / index lerin kaç defa yazıldığını ve okunduğunu listelemektedir.
SELECT CAST(OBJECT_NAME(usg.OBJECT_ID) AS VARCHAR(30)) AS 'Table', CAST(idx.name AS VARCHAR(30)) AS 'Index', usg.user_seeks + usg.user_scans + usg.user_lookups AS 'Reads', usg.user_updates AS 'Writes' FROM sys.dm_db_index_usage_stats AS usg INNER join sys.indexes AS idx ON usg.OBJECT_ID = idx.OBJECT_ID and idx.index_id = usg.index_id WHERE OBJECTPROPERTY(usg.OBJECT_ID,'isusertable') = 1 ORDER BY 'Table';