Which Indexes are not Used?

During the development cycle, indexes will be placed on SQL Server tables to speed up performance when searching for records. Lots of thought probably has gone into creating just the right mix of indexes based on how developers think customers will use the system. But how do you tell if all the indexes are being used once your database has been implemented into production?

After a new application has been in production you should verify if your indexes are being used. If you have unused indexes they are just wasting disk space and valuable computer resources to maintain them. It is fairly simple to determine which indexes are not being used by looking at the index statistics exposed by querying the sys.dm_db_index_usage_stats DMV.

The sys.dm_db_index_usage_stats DMV provides a number of columns that identify how many times an index is used in a seek or a scan operation, or if an index has been used in a bookmark lookup or update operation. The code below uses the sys.dm_db_index_usage_stats DMV to determine which indexes have not been used in the AdventureWorks2012 database. Keep in mind that the sys.dm_db_index_usage_stats DMV only tracks index usage since start up. If your instance has just started up then this query will report that most of indexes have not been used. Therefore you want to run this query only after your system has been up for a while.

USE AdventureWorks2012;
GO
SELECT o.name Object_Name,
       SCHEMA_NAME(o.schema_id) Schema_name,
       i.name Index_name, 
       i.Type_Desc,
       CASE WHEN (s.user_seeks > 0 
               OR s.user_scans > 0 
               OR s.user_lookups > 0) 
              AND s.user_updates > 0
            THEN 'USED AND UPDATED'
            WHEN (s.user_seeks > 0 
               OR s.user_scans > 0 
               OR s.user_lookups > 0) 
              AND s.user_updates = 0
            THEN 'USED AND NOT UPDATED' 
            WHEN  s.user_seeks IS NULL 
              AND s.user_scans IS NULL
              AND s.user_lookups IS NULL
              AND s.user_updates IS NULL
            THEN 'NOT USED AND NOT UPDATED' 
            WHEN (s.user_seeks = 0 
              AND s.user_scans = 0 
              AND s.user_lookups = 0) 
              AND s.user_updates > 0
            THEN 'NOT USED AND UPDATED' 
            ELSE 'NONE OF THE ABOVE'
            END AS Usage_Info,
       COALESCE(s.user_seeks,0) AS user_seeks, 
       COALESCE(s.user_scans,0) AS user_scans, 
       COALESCE(s.user_lookups,0) AS user_lookups, 
       COALESCE(s.user_updates,0) AS user_updates   
 FROM sys.objects AS o
     JOIN sys.indexes AS i
 ON o.object_id = i.object_id 
     LEFT OUTER JOIN
  sys.dm_db_index_usage_stats AS s    
 ON i.object_id = s.object_id   
  AND i.index_id = s.index_id
 WHERE o.type = 'U'
 -- Clustered and Non-Clustered indexes
  AND i.type IN (1, 2) 

ORDER BY user_seeks+user_scans+user_lookups+user_updates ASC

See all articles by Greg Larsen