Unused Indexes Best Practices

by DoubleVu   Last Updated January 11, 2018 18:06 PM

Based on this query, if I see a low amount of total reads (very close to 0 or 0, like 1 or 2) and a high or moderate amount of user updates (I couldn't find inserts or deletes with this query) with a large row count, I should in theory remove the index.

SELECT DISTINCT
    OBJECT_NAME(s.[object_id]) AS ObjectName
       , p.rows TableRows
       , i.name AS [INDEX NAME]
       , (user_seeks + user_scans + user_lookups) AS TotalReads
       , user_updates UserUpdates
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 
    INNER JOIN sys.partitions p ON p.object_id = i.object_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
       AND s.database_id = DB_ID()
       AND i.name IS NOT NULL
ORDER BY (user_seeks + user_scans + user_lookups) ASC

I want to cross-check the accuracy of this assumption here. For instance, an index that has existed for over a year but has never been read, but highly updated, seems like it would be a bad idea to have. Is there a scenario where this assumption is invalid?



Answers 2


This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch.

More importantly, the rows in this view for any specific index are removed when that index is rebuilt (but not when it is reorganized). If you are performing regular index maintenance, it might be useful to look at the maintenance logs and see if any of the indexes you're considering dropping might have been rebuilt recently.

So, making decisions based on low reads since the last restart, when the last restart may have been for last week's patch Tuesday updates or yesterday's service pack, might not be wise. Or when you are performing index maintenance, since the last rebuild. There may be a report that is only run once a month, or once a quarter, or once a year, and it is run by an important and impatient person.

Further, an index might be there for something that will happen in the future that you don't know about - a series of reports being prepared for tax season, let's say.

So, my advice is:

Use the DMV to identify candidate indexes to remove, but don't make that decision in a bubble - you need to do the legwork to determine why an index might exist before dropping it, even if it looks like it isn't currently being used.

Aaron Bertrand
Aaron Bertrand
February 03, 2016 15:33 PM

Yup the view only has statistics since the last reboot. To help mitigate that I setup a job that ran a query like the one you posted monthly in the morning before our maintenance window started to capture the information every month before the server rebooted. That allowed me to go back further and look at trends over time. I also had a second query that ran looking for possibly missing indexes.

Another thing to consider is what other index(es) are on the table. It might not be getting used because it is mostly or completely a duplicate of another index. Yes. SQL server lets you create two different but identical indexes so it is possible for it to be completely redundant.

You can also look at what the query plan might end up being for a query that used that index if that index was removed. Would it have another index to use or would it likely have to fall back to a full table scan.

Indexes ends up being as much art as it is science because it is really hard to know everything about what might be run and it ends up changing frequently anyway.

Evan Steinbrenner
Evan Steinbrenner
February 03, 2016 17:51 PM

Related Questions



Rebuild Very Large Primary Key Index

Updated September 02, 2017 23:06 PM

SQL Server 2008 - Question about index behaviour

Updated June 16, 2015 12:02 PM

Ola Hallengren Index Script not Reindexing

Updated August 12, 2015 19:02 PM

Clustered Index Help, where did i go wrong?

Updated August 13, 2015 18:02 PM