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
