SQL Server Index Kullanım Bilgileri (sys.dm_db_index_usage_stats)

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';

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir