SQL Server’da Kullanılmayan ve Eksik Index’leri Bulmak

Herkese merhaba,

Bu yazıda SQL Server’da kullanılmayan ve eksik Index’leri bulmak ile ilgili bilgi vermeye çalışacağım.

SQL Server’da bazı durumlarda kullanılmayan ve eksik Index’leri bulmak isteyebilirsiniz.

Aşağıdaki kodu kullanarak siz de bu işlemi rahatlıkla yapabilirsiniz.

Eksik Index’leri Bulmak

SELECT TOP 25
	dm_mid.database_id AS DatabaseID
   ,dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) Avg_Estimated_Impact
   ,dm_migs.last_user_seek AS Last_User_Seek
   ,OBJECT_NAME(dm_mid.object_id, dm_mid.database_id) AS [TableName]
   ,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.object_id, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') +
	CASE
		WHEN dm_mid.equality_columns IS NOT NULL AND
			dm_mid.inequality_columns IS NOT NULL THEN '_'
		ELSE ''
	END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'),

	'[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '') +
	CASE
		WHEN dm_mid.equality_columns IS NOT NULL AND
			dm_mid.inequality_columns IS NOT NULL THEN ','
		ELSE ''
	END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
	ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
	ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_id = db_id()
ORDER BY Avg_Estimated_Impact DESC
GO

Kullanılmayan Index’leri Bulmak

SELECT DISTINCT
	OBJECT_NAME(sis.object_id) TableName
   ,si.name AS IndexName
   ,sc.Name AS ColumnName
   ,sic.Index_ID
   ,sis.user_seeks
   ,sis.user_scans
   ,sis.user_lookups
   ,sis.user_updates

FROM sys.dm_db_index_usage_stats sis

INNER JOIN sys.indexes si
	ON sis.object_id = si.object_id
		AND sis.Index_ID = si.Index_ID

INNER JOIN sys.index_columns sic
	ON sis.object_id = sic.object_id
		AND sic.Index_ID = si.Index_ID

INNER JOIN sys.columns sc
	ON sis.object_id = sc.object_id
		AND sic.Column_ID = sc.Column_ID

WHERE sis.database_id = db_id('Northwind') --Veritabanı adı buraya yazılacak
AND sis.object_id = object_id('Categories'); --Tablo adı buraya yazılacak

GO

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.