Indexes That Have Not Been Used but Are Being Updated

We all know indexes are important for improving your query performance, but to store and maintain indexes SQL Server requires disk space and compute resources.  If you have indexes that are not being used, they are costing you valuable resources and causing your update statements to run longer.  Those updates run longer because they have to maintain those unused indexes.  If your application is not using an index, then there is no value in having that index.

Periodically you should review your index usage statistics to determine how your indexes are being used.  You can do that by using the sys.dm_db_index_usage_stats dynamic management view (DMV).  This view will show you if your indexes are being used in a seek, scan, or lookup operation, and whether or not the indexes have been updated.  Remember DMVs only track information since SQL Server started.  Therefore, you need to consider running this DMV after SQL Server has been up for a reasonable time in order to get an accurate picture of how your indexes have been used.

By using the sys.dm_db_index_usage_stats you can identify those indexes that have never been used.  In the following code there are two SELECT statements with a UNION clause between them.  The first SELECT statement identifies those indexes that have never been used but have been update in your databases.  The second SELECT statement identifies those indexes that have never been used and have not been updated.  By “updated” I mean the index has either been maintained due to an INSERT, UPDATE or a DELETE operation.  You should consider running this query periodically to identify those indexes that are providing your application no value but are costing you compute and disk space resources. Once you know what indexes are not being used, then you can determine whether or not you even need those indexes.

-- indexes that have been updated and not used
select SCHEMA_NAME(o.schema_id) as [schema_name], 
       OBJECT_NAME(s.object_id) table_name,
       i.name index_name, 
       s.user_seeks, 
       s.user_scans, 
       s.user_lookups,
       s.user_updates,
       'yes' Index_updated_but_not_used
from sys.dm_db_index_usage_stats s
join sys.objects o
on s.object_id = o.object_id
join sys.indexes i
on s.index_id = i.index_id
and s.object_id = i.object_id
where 
(s.user_seeks = 0
  and s.user_scans = 0
  and s.user_lookups = 0) 
and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
UNION
-- indexes that have not been updated or used 
SELECT
       SCHEMA_NAME(o.schema_id) as [schema_name], 
       OBJECT_NAME(o.object_id) table_name,
       i.name index_name, 
       0  as user_seeks, 
       0 as user_scans, 
       0 as user_lookups,
       0 as user_updates,
       'no' as Index_updated_but_not_used
FROM
sys.indexes i
JOIN
sys.objects o
on i.object_id = o.object_id
  
WHERE
  i.index_id NOT IN (
SELECT s.index_id
FROM sys.dm_db_index_usage_stats s
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id
AND s.database_id =  DB_ID(DB_NAME()))
and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
order by Index_updated_but_not_used desc;

See all articles by Greg Larsen