Tip: Finding Unused Indexes

Tip: Finding Unused Indexes

Find your unused indexes

After your SQL Server system has been in use for a while you might find there are a number of indexes that exist on the system that aren’t being used. Fortunately, SQL Server provides a Dynamic Management View (DMV) named sys.dm_db_index_usage_stats that tracks your index usage. This DMV shows when an index is being updated but not used in any seek, scan or lookup operations. In the DMV user_seeks, user_scans, and user_lookups counters reflect the usage of each index.  If the value of the counters is 0 then the index hasn’t been used. The user_updates counter shows when the index needed to be updated due to an insert, update, or delete operation on the base table. The following query can help you find the indexes that aren’t being used. However, you still need to use good judgment. Just because an index may be rated high on this query that doesn’t necessarily mean it should be dropped.

SELECT object_name(i.object_id) AS TableName, i.name AS [Unused Index]
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id
      AND i.index_id = s.index_id
      AND s.database_id = db_id()
WHERE objectproperty(i.object_id, 'IsIndexable') = 1
AND objectproperty(i.object_id, 'IsIndexed') = 1
AND s.index_id is null
OR (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)
ORDER BY object_name(i.object_id) ASC


Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.