Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 13 down vote accepted

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.

share|improve this answer
    
@AaronBertrand So would tracking a history (along with the time of the last restart) be a good addition to this? Thanks for you answer. – DoubleVu Feb 3 at 15:53
1  
@DoubleVu yes, maintaining snapshots of index usage history is likely worthwhile so that you can make educated decisions that aren't affected by anything that might change the output of the usage stats DMV. – Aaron Bertrand Feb 3 at 18:11

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.