Discovering Unused Indexes


MENU
By:
Overview

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.

Explanation

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.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

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. 

SYS.DM_DB_INDEX_OPERATIONAL_STATS output

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.

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       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.
SYS.DM_DB_INDEX_USAGE_STATS output

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

<< Previous

Next >>



Comments For This Article




Monday, September 9, 2019 - 10:45:06 PM - Jeff Moden Back To Top (82332)

I know it's an older post but referring to the relatively low usage but very high update rate of an index as posted by "PS" below, you have to remember that the columns aren't based on the number of rows... they're based on the number of queries.  The seeks and scans could be a measure of critical reporting queries (as "PS" alluded to) while the modifications could be done in a RBAR fashion over time where each modification worked against only one row.

Yes, it's still worth looking into as indicated by the low usage count compared to the number of modifications but, just like when there are no user seeks, scans, or lookups, it's just an indication to dig further.  You shouldn't (I'll say, "Must NOT") trust index elimination only to what's in sys.dm_db_index_usage_stats.

Monday, September 9, 2019 - 10:30:06 PM - Jeff Moden Back To Top (82331)

I got to this article through a link someone posted.  It's a really good article but I'd like to add a bit of information and a word of caution that I'm sure that Greg and others know but hasn't explicitly stated to warn any newbies that may be reading this article.

First, both the sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats DMVs are certainly emptied whenever the SQL Server Service is restarted but the data in the sys.dm_db_index_operational_stats DMV actually can and usually does have a much shorter lifespan than the spans between two restarts.  The information about any given object in sys.dm_db_index_operational_stats exists only while the object is in cache.  If it's dropped from cache, the information about the object is also dropped from the sys.dm_db_index_operational_stats DMV.  When the object is reloaded into cache at a later time, it starts at zero as surely as if there'd been a restart.

Also, if you count on zero reads in the sys.dm_db_index_usage_stats DMV as being the only necessary condition to determine if an index is being used, you'd be making a serious mistake.  To be sure, Greg points out those reads are only whether or not queries used the index, and that's absolutely correct, but there are other things that make use of indexes, especally (but not limited to) UNIQUE indexes. 

That point is actually accidently exemplified in this very article in the graphic associated with the sys.dm_db_index_usage_stats DM.  The 2nd index in the graphic has no reads... only updates.  That doesn't mean that SQL Server itself isn't using that index.  It simply means that the index wasn't used in any user queries.  If you look at the name of that index, it's prefixed with "AK_", which is the typical and proper Hungarian Notation for an "Alternate Key" and to be a "key", it also has to be UNIQUE.  That index is preserving and guaranteeing uniqueness on a column or columns and could actually be the target of an FK, as well.  Deleting that index just based on no query usage can land you in the world of hurt.

Even disabling such a index could hurt a lot even if an FK isn't involved because the index IS maintaining uniqueness for a reason and dropping it would be a sin against data integrity in it's simplest form.

The same holds true for different reasons with certain "duplicate" indexes, but that's a whole nuther story.

The bottom line is that if you think an index can be dropped based on the content of the sys.dm_db_index_usage_stats DMV, don't.  Disable it first and the let it sit there for a month or three to make sure no fur will fly when you drop it.  If the fur does fly, rebuild the index to re-enable it and to rebuild its stats behind the scenes.

Friday, November 9, 2018 - 1:08:26 AM - PS Back To Top (78197)

 Hi,

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 1, 2017 - 9:43:42 AM - Greg Robidoux Back To Top (63673)

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.

-Greg

Tuesday, August 1, 2017 - 9:06:39 AM - Vicki Back To Top (63672)

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?














get free sql tips
agree to terms