Best Practices for Managing Unused Indexes in SQL Server

best practicesindexindex-tuningsql server

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?

Best Answer

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.

Related Question