How to Find Unused Indexes in SQL Server

Finding unused indexes is a proactive measure that DBA’s can take. By removing indexes you can improve performance and free up disk space on a SQL Server.

What are unused indexes?

We would describe unused indexes as those indexes that are not being utilized by any queries.

How to identify unused indexes

The query we use to identify unused indexes is as below. Adapted from MS Sql Tips.

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()
       Order by user_seeks asc

unused indexes

Answer these questions before you remove them.

  • Is the index likely to be used in the future?
  • Does the product vendor allow the removing of indexes?
  • How much overhead is the index taking up?

Risks of removing

The main risk associated with removing an index is if it is ever used. For example there could be reports that are run once a month that use the indexes and will perform poorly without them.

The other risk is if they are an index that has another use. For example a filtered index, or a primary key.

Risks of leaving

None really if you are not having major issues currently. You may suffer a little bit performance wise, and depending on how big the index is, there may be some space issues.

Benefits you can gain by removing

Improved performance because you have removed the overhead of keeping the index updated. Also you will be able to claim back any disk space used by the indexes, which can be a considerable amount. I recently removed an unused index for a client that was taking up 27 GB of disk space.

Other considerations

Always try to remember the saying.. “if it’s not broken don’t fix it”. This can be a great law to live by if you are a DBA.

If you are trying to tune a DB to the greatest potential looking for, and removing unused indexes can be a good move.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply