Below is a query that we can use to quickly identify unused indexes in SQL Server. More specifically, it returns indexes that are updated, but not used.

The Query

SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    i.name AS IndexName,
    user_updates AS UserUpdates,
    i.is_primary_key AS IsPrimaryKey
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.object_id = i.object_id
AND 
    s.index_id = i.index_id
WHERE 
    OBJECTPROPERTY(s.object_id, 'IsMsShipped') = 0
    AND user_seeks = 0
    AND user_scans = 0
    AND user_lookups = 0
    AND i.name IS NOT NULL -- Ignore HEAP indexes
ORDER BY 
    UserUpdates DESC

As mentioned, this returns indexes that are being updated but not used. We specifically filter the query to just those rows where the user_seeks, user_scans, and user_lookups columns are zero. In other words, nobody’s using them.

This query could be useful for identifying indexes that can/should be dropped. Indexes that are being updated but not used are adding overhead to the system. They’re taking up resources (by having to be updated), but they’re not providing any value (due to nobody using them).

That said, be careful of anything that has 1 in the IsPrimaryKey column, as that’s a primary key and you might not want to drop those.

Also, this query is based on the sys.dm_db_index_usage_stats view, which is limited to activity done since the last time SQL Server was started or when the database was detached or shutdown. Here’s what Microsoft says about it:

The counters are initialized to empty whenever the database engine is started. Use the sqlserver_start_time column in sys.dm_os_sys_info to find the last database engine startup time. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

So only use this query when the database engine has been running for long enough to log meaningful results.