Foliotek Developer Blog

Identifying Unused Indexes in a SQL Server Database

One of our databases began timing out during our weekly index rebuild job, so I began to look into what indexes were getting rebuilt. After looking at the Index Usage Report, I noticed that some of them were not even being used. Some of them were very large indexes as well.

Obviously, an index that is never used doesn’t really need to be rebuilt to improve performance, so I removed a few of them. Then I began to look more closely at how I could identify other indexes that were not being used, aside from looking through every index on the SQL Report.

Unused Indexes with no reads in the Index Usage Stats Table

From research I did online, I saw that the system table sys.dmdbindexusagestats contains usage statistics for indexes. There are two key columns on this table that are of particular interest, namely userscans **and **userseeks. If both of these values are zero, then the system probably isn’t using the index.

It is probably a good idea to at least look at a few other columns as well, including userlookups, systemseeks, systemscans, and systemlookups.

I created a query that identifies unused indexes based on this criteria and outputs them in a pretty nice format. I was unable to find any other queries online that output everything in as nice of a format as this, so I hope you find this helpful.

 -- GET UNUSED INDEXES THAT APPEAR IN THE INDEX USAGE STATS TABLE  
 DECLARE @MinimumPageCount int  
 SET @MinimumPageCount = 500

SELECT Databases.name AS [Database],  
 object_name(Indexes.object_id) AS [Table],  
 Indexes.name AS [Index],  
 PhysicalStats.page_count as [Page_Count],  
 CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Size (MB)],  
 CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Frag %],  
 ParititionStats.row_count AS [Row Count],  
 CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024)  
 / ParititionStats.row_count) AS [Index Size/Row (Bytes)]  
 FROM sys.dm_db_index_usage_stats UsageStats  
 INNER JOIN sys.indexes Indexes  
 ON Indexes.index_id = UsageStats.index_id  
 AND Indexes.object_id = UsageStats.object_id  
 INNER JOIN SYS.databases Databases  
 ON Databases.database_id = UsageStats.database_id  
 INNER JOIN sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL)  
 AS PhysicalStats  
 ON PhysicalStats.index_id = UsageStats.Index_id  
 and PhysicalStats.object_id = UsageStats.object_id  
 INNER JOIN SYS.dm_db_partition_stats ParititionStats  
 ON ParititionStats.index_id = UsageStats.index_id  
 and ParititionStats.object_id = UsageStats.object_id  
 WHERE UsageStats.user_scans = 0  
 AND UsageStats.user_seeks = 0  
 -- ignore indexes with less than a certain number of pages of memory  
 AND PhysicalStats.page_count > @MinimumPageCount  
 -- Exclude primary keys, which should not be removed  
 AND Indexes.type_desc != ‘CLUSTERED’  
 ORDER BY [Page_Count] DESC

This query includes the following helpful information on the unused indexes:

  • Table Name
  • Index Name
  • Page Count
  • Total Index Size (MB)
  • Fragmentation (%)
  • Row Count
  • Index Size/Row (Bytes)

Unused Indexes with no entry in the Index Usage Stats Table

For part of my research on other sites, I noticed that there are some indexes that somehow never get listed in the dmdbindexusagestats table. So, running this first query is not really enough. In fact, the SQL Index Usage Report doesn’t list all the indexes either. So, there is another query that I created to identify these types of indexes. I joined on several tables again to make the output more useful.

 -- GET UNUSED INDEXES THAT DO **NOT** APPEAR IN THE INDEX USAGE STATS TABLE  
 DECLARE @dbid INT  
 SELECT @dbid = DB_ID(DB_NAME())

SELECT Databases.Name AS [Database],  
 Objects.NAME AS [Table],  
 Indexes.NAME AS [Index],  
 Indexes.INDEX_ID,  
 PhysicalStats.page_count as [Page Count],  
 CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],  
 CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]  
 FROM SYS.INDEXES Indexes  
 INNER JOIN SYS.OBJECTS Objects ON Indexes.OBJECT_ID = Objects.OBJECT_ID  
 LEFT JOIN sys.dm_db_index_physical_stats(@dbid, null, null, null, null) PhysicalStats  
 ON PhysicalStats.object_id = Indexes.object_id  
 AND PhysicalStats.index_id = indexes.index_id  
 INNER JOIN sys.databases Databases  
 ON Databases.database_id = PhysicalStats.database_id  
 WHERE Objects.type = ‘U’ — Is User Table  
 AND Indexes.is_primary_key = 0  
 AND Indexes.INDEX_ID NOT IN (  
 SELECT UsageStats.INDEX_ID  
 FROM SYS.DM_DB_INDEX_USAGE_STATS UsageStats  
 WHERE UsageStats.OBJECT_ID = Indexes.OBJECT_ID  
 AND Indexes.INDEX_ID = UsageStats.INDEX_ID  
 AND DATABASE_ID = @dbid)  
 ORDER BY PhysicalStats.page_count DESC,  
 Objects.NAME,  
 Indexes.INDEX_ID,  
 Indexes.NAME ASC

When to Remove an Index

Just because an index is not being used does not necessarily mean it should be removed. The queries above ignore the clustered indexes on the tables, since there are very few times, if any, when a table should not have any index at all. There may be other reasons to keep an index around as well. Those decisions are best made by those that have a knowledge of how the data is used and will be used in the future.

Conclusion

I was able to use these two queries to get rid of a lot of dead weight in several of our databases. In one of them, in particular, I was able to remove almost 1 GB of space. That’s 1 GB less for every backup, so it adds up over time.

One last note… After removing indexes, it’s probably a good idea to run SQL Profiler, just to be safe, on the database to catch any queries that may be expensive and somehow used an index that should not have been deleted. See my other post on SQL Optimization – A Beginner’s Guide

For more tips on managing a SQL Server Database, see Managing a SQL Server Database: Tips for Beginners