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