Seldom Used Indexes

On occasion you may ask yourself if there are any under used indexes in your database.  If not you, then possibly a manager or client.  Usually this comes up when evaluating for missing indexes or better indexes.

SQL Server provides a means to find the information we seek.  We can query the sys.dm_db_index_usage_stats DMV  to garner much of the information we want.  You can read about this DMV here.

The columns we want to take a look at are the seeks, scans and lookups columns.  In conjunction with that, we want to compare those columns to the updates related columns.

Here is an example query.

[codesyntax lang=”tsql”]


In this query, I am looking at two main components for comparison.  I want to determine where there are more updates on an index than reads.  I also want to see only those indexes that have no reads.

Using a query like this can help you to narrow your search for under-performing indexes.  Indexes returned by this query tend to be more costly to maintain than the benefit they may be providing.  Starting with the indexes returned by this query, you can test and confirm the findings.

7 thoughts on “Seldom Used Indexes”

  1. One of my scheduled tasks for today was to work on a script to do exactly this. Thanks for saving me some serious time! This is great, I see a bunch of indexes in my schema that I can probably 86, once I do a bit of testing.

    1. Thanks. I am glad you said test. I can’t stress enough the importance to research and test before removing any of these indexes.

  2. I do have a follow-up question, if you don’t mind. What exactly is the purpose of this line:

    And (user_seeks + user_scans + user_lookups + s.system_seeks + s.system_scans + s.system_lookups) = 0

    This appears to exclude quite a few results that, on first glance, look fairly significant. Please forgive me if I’m overlooking something obvous.


    1. The purpose for that condition is to eliminate all indexes that have been used in some fashion. In this query, I only want to see indexes that have not been used based on data available in the DMV. Though this could lead one to conclude that an index has not been used, it is possible that the index has been used but that index use was outside the data currently available via the DMV.

  3. Okay, bear with me because I might be a wee bit dense. I’m a bit of a n00b.

    “I want to determine where there are more updates on an index than reads. I also want to see only those indexes that have no reads.”

    So in brief, we want to see indexes where writes > 0 and reads = 0.

    Why is it not helpful to see indexes that have *dramatically* more updates than reads? Why restrict it only to those indexes that have no reads whatsoever? I’m looking at an index right now that, over the past hour, has racked up over 16,000 writes and fewer than 10 reads. That strikes me as something I should be taking a look at, no?

    1. The script can certainly be changed to look at that. I did this script knowing that I only wanted to see indexes that were not being read. I can have a higher confidence level that that index is not being used efficiently in some query at a different point in the day. Also, it is less likely that an index with 0 reads is being used in an query hint.

      But certainly, comment out the additional filter in the where clause to get only where writes > reads. Just be certain to test.

  4. Thanks Jason. I know for certain that none of our queries use hints, so that’s a non-issue. But yes, I will take your advice and test the heck out of any change I decide to implement.

    Great help, thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.