Identifying Unused Indexes in SQL Server

By Samir Behara on November 15, 2017 • ( 1 )

Indexes play a critical role in enhancing your database performance by facilitating faster query execution. Having the right indexes in place can speed up the process of fetching data from your underlying tables whereas having redundant indexes can hurt the performance negatively.

In one of my earlier articles, we looked at different ways to identify Missing Indexes in your SQL Server database – Identifying Missing Indexes in SQL Server

In this article, we will focus on an easy way to identify unused/bad indexes which might be hurting your database performance. Dynamic Management Views are great resources to gain insights into what is happening inside your SQL Server instance and capture information to diagnose your database performance issues.

The Dynamic Management View ‘sys.dm_db_index_usage_stats‘ provides information about the index usage in your database. When an index is used, a row is added to this DMV if a row does not already exist for the index, else the existing information is updated.

As a rule of thumb, under proper workload if your indexes have comparatively more writes than reads, then you should revisit them and evaluate their existence.

I have been using the below query to identify the unused/redundant indexes in my database instances and it has always worked great for me.

	— Identifying Unused Indexes in SQL Server
	SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
	i.name AS [Index Name] ,
	i.fill_factor ,
	user_updates AS [Total Writes] ,
	user_seeks + user_scans + user_lookups AS [Total Reads] ,
	user_updates – ( user_seeks + user_scans + user_lookups ) AS [Difference]
	FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
	INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id]
	AND i.index_id = s.index_id
	WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
	AND s.database_id = DB_ID()
	AND user_updates > ( user_seeks + user_scans + user_lookups )
	AND i.index_id > 1
	ORDER BY [Difference] DESC ,
	[Total Writes] DESC ,
	[Total Reads] ASC
	OPTION ( RECOMPILE );

view raw

Identifying Unused Indexes in SQL Server

hosted with ❤ by GitHub

I ran the above query against one of my QA environments which had data collected for over 2 weeks time, and saw multiple indexes on the ‘Address’ table which had zero reads versus 6865 writes. To confirm I executed the query in PROD, and noticed the same pattern. This implied the fact that these set of indexes were redundant and not getting used, so its definitely a safe option to delete them.

Permission required to run the Missing Index DMV

You need to have VIEW SERVER STATE permission to be able to execute DMVs.

Word of Caution –

Keep in mind that the above query is supposed to be a guidance for you to identify redundant indexes in your database. Please do proper analysis before dropping any indexes. Be mindful of the below scenarios –

— Do not drop any indexes on your Primary Key or Foreign key.

— I have bumped into scenarios where an index was created for a critical load process which ran once in a day – as a result the number of reads were far less than write. But dropping that index caused the load process to take lot of additional time to complete, which was not acceptable.

— Information stored in DMVs is lost when your SQL Server instance is rebooted. So if you have server patching scheduled monthly, make sure that while running these DMVs you have data collected for a sufficient amount of time which reflects your workload correctly.

For additional information about diagnosing performance issues using DMVs, I would recommend you to check out Glenn Berry’s DMV toolkit.