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

	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, ''), ', ', '_'), '[', ''), ']', '') +
		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, '') +
		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

Finding Unused Indexes

	OBJECT_NAME(sis.object_id) TableName
   , AS IndexName
   ,sc.Name AS ColumnName

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


Good luck to everyone in business and life.


Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!