Handling Unused Indexes

What do you do if you see that an index is being updated but is low on seeks and scans? Should you drop it because the table is never queried or a better index is being chosen? If an index doesn’t have updates, seeks, or scans then is the next step to drop?

The answer to these questions is a resounding “not yet!” Getting rid of an index that isn’t getting much use sounds simple but there are careful considerations to make.

Checking Index Usage

To check for indexes not in use, you can use a script like the following:

SELECT object_name(s.object_id) AS 'Object_Name'
	,i.name AS 'Index_Name'
	,s.user_seeks AS 'User_Seeks'
	,s.user_scans AS 'User_Scans'
	,s.user_lookups AS 'User_Lookups'
	,s.user_updates AS 'User_Updates'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
	AND i.index_id = s.index_id
WHERE objectproperty(s.object_id, 'IsUserTable') = 1;
GO

Do not ever run a query to check indexes, check for what appears to not be in use, and start dropping indexes. Information on index usage isn’t kept around forever. Even if you are coming back into the office after a long weekend and a particular index you are checking doesn’t appear to be in use, you should at least check these potential reasons first.

Has SQL Server Restarted Recently?

The stats found in dm_db_index_usage_stats get reset when SQL Server is restarted. If you are seeing very little information in dm_db_index_usage_stats, it may be due to a recent server reboot for patching or another issue that caused SQL Server to restart. This is one of many reasons why knowing your reboot schedule is beneficial.

When is the Index Used?

You may be checking indexes at the end of the month and find one that has been getting updated but has no scans or seeks. You decide to disable it and go home for the weekend. When you come back into the office, you realize that there is a monthly job that relies on that index and all of that processing that was supposed to happen before Monday morning hasn’t completed. Remember that just because an index isn’t in use often doesn’t mean it’s not useful.

Disable Before Drop

Notice that I said disable rather than drop. When you’re confident that an index isn’t needed, start by disabling the index instead of dropping the index. Once an index has been disabled and you’re even more confident that it’s not needed, then I would go ahead with dropping.

If we’re disabling the IX_Test index on the Users table, we can right click on the index and select Disable:

Or run the following:

ALTER INDEX IX_Test ON Users DISABLE;

If we soon encounter an issue where we think the index not being available is the cause, the index is still listed under Indexes and we can be confident in bringing the same index back. You may notice that there is no corresponding “Enable” button. To bring the index back, it will need to be rebuilt either by selecting “Rebuild” when right clicking the index:

Or by running the following:

ALTER INDEX IX_Test ON Users REBUILD;

Rebuilding a disabled index may still take some time to complete; however, it’s much less stressful waiting for an index to rebuild than it is to wonder what the index definition was in the first place.

Thanks for reading!