Finding Unused and Missing Indexes in SQL Server

Hello everyone,

In this article, I will try to give information about finding unused and missing Indexes in SQL Server.

In SQL Server, in some cases, you may want to find unused and missing Indexes.

You can easily do this using the code below.

Finding Missing Indexes

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

Finding Unused Indexes

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

Good luck to everyone in business and life.