Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Discovering Unused Indexes


To ensure that data access can be as fast as possible, SQL Server like other relational database systems utilizes indexing to find data quickly.  SQL Server has different types of indexes that can be created such as clustered indexes, non-clustered indexes, XML indexes and Full Text indexes.

The benefit of having more indexes is that SQL Server can access the data quickly if an appropriate index exists.  The downside to having too many indexes is that SQL Server has to maintain all of these indexes which can slow things down and indexes also require additional storage.  So as you can see indexing can both help and hurt performance.

In this section we will focus on how to identify indexes that exist, but are not being used and therefore can be dropped to improve performance and decrease storage requirements.


When SQL Server 2005 was introduced it added Dynamic Management Views (DMVs) that allow you to get additional insight as to what is going on within SQL Server.  One of these areas is the ability to see how indexes are being used.  There are two DMVs that we will discuss.   Note that these views store cumulative data, so when SQL Server is restated the counters go back to zero, so be aware of this when monitoring your index usage.

DMV - sys.dm_db_index_operational_stats

This DMV allows you to see insert, update and delete information for various aspects for an index.  Basically this shows how much effort was used in maintaining the index based on data changes.

If you query the table and return all columns, the output may be confusing.  So the query below focuses on a few key columns.  To learn more about the output for all columns you can check out Books Online.

       I.[NAME] AS [INDEX NAME], 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 

Below we can see the number of Inserts, Updates and Deletes that occurred for each index, so this shows how much work SQL Server had to do to maintain the index. 


DMV - sys.dm_db_index_usage_stats

This DMV shows you how many times the index was used for user queries.  Again there are several other columns that are returned if you query all columns and you can refer to Books Online for more information.

       I.[NAME] AS [INDEX NAME], 
       AND S.database_id = DB_ID()

Here we can see seeks, scans, lookups and updates. 

  • The seeks refer to how many times an index seek occurred for that index.  A seek is the fastest way to access the data, so this is good.
  • The scans refers to how many times an index scan occurred for that index.  A scan is when multiple rows of data had to be searched to find the data.  Scans are something you want to try to avoid.
  • The lookups refer to how many times the query required data to be pulled from the clustered index or the heap (does not have a clustered index).  Lookups are also something you want to try to avoid.
  • The updates refers to how many times the index was updated due to data changes which should correspond to the first query above.

Identifying Unused Indexes

So based on the output above you should focus on the output from the second query.  If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index.  Remember that the data from these DMVs is reset when SQL Server is restarted, so make sure you have collected data for a long enough period of time to determine which indexes may be good candidates to be dropped.

Additional Information

Here are some additional articles about indexes.

Last Update: 3/11/2011

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Friday, November 09, 2018 - 1:08:26 AM - PS Back To Top


Using this query, I found that one index (on Shopping cart table (on column CustomerID Only), which has very frequent inserts, and has number of rows over 2.6 million) has User Seek of 830, User Scan of 107 and UserUpdates of 7,732,212! What do you suggest for such an index? Should we consider modifying it? We can't remove this index completely because we need to search shopping carts for a particular customer.

Thank you..

Tuesday, August 01, 2017 - 9:43:42 AM - Greg Robidoux Back To Top

Hi Vicki,

yes you are correct.  The index is not being used to make queries run faster, but SQL Server still has to maintain the index which is extra overhead for storage and maintenance processing.

Just make sure you have the system running for a good amount of time to collect a good sample of how the indexes are being used before dropping.  Also, it is a good idea to script out the index in case there is a need to recreate it in the future.


Tuesday, August 01, 2017 - 9:06:39 AM - Vicki Back To Top

My DBA and I have both read this article but interpret one line differently.  Can you help? 

 "If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index."

He believes "still needs" to mean the index is important while I believe it means the index is a shore that we really don't need.  Can you clarify?

Learn more about SQL Server tools