Identifying Unused Indexes in SQL Server

Our nightly index maintenance job kept getting slower, and when I looked, half the indexes on the biggest tables were ones nobody could explain. Every nonclustered index costs on each insert, update and delete, takes space in backups and the buffer pool, and needs maintenance. They pile up: a report gets rewritten, a tuning advisor run from years ago leaves twenty behind, and nobody dares drop anything.

SQL Server keeps usage counters for every index since the last restart. Here's how I use them on SQL Server 2012 through 2017, and the checks I do before dropping anything.

How long have the counters been running?

sys.dm_db_index_usage_stats is reset when the instance restarts (and, in older builds, when a database goes offline). A list of "unused" indexes taken an hour after a reboot is worthless. Check uptime first:

SELECT sqlserver_start_time, DATEDIFF(day, sqlserver_start_time, GETDATE()) AS days_up
FROM sys.dm_os_sys_info;

I want at least one full business cycle, ideally a month that includes month-end processing. Also be aware of a bug in SQL Server 2012 and 2014 where ALTER INDEX ... REBUILD reset the counters for that index; it was fixed in 2012 SP3 CU3 and 2014 SP2, so patch or account for the last rebuild date.

Indexes with no reads

SELECT OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS table_name,
       i.name AS index_name,
       i.type_desc,
       i.is_unique,
       i.is_primary_key,
       ISNULL(us.user_seeks, 0) AS seeks,
       ISNULL(us.user_scans, 0) AS scans,
       ISNULL(us.user_lookups, 0) AS lookups,
       ISNULL(us.user_updates, 0) AS updates,
       us.last_user_seek, us.last_user_scan,
       ps.used_page_count * 8 / 1024 AS size_mb
FROM sys.indexes i
JOIN sys.objects o ON o.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats us
       ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
JOIN sys.dm_db_partition_stats ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE o.type = 'U'
  AND i.type_desc = 'NONCLUSTERED'
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) = 0
ORDER BY ISNULL(us.user_updates, 0) DESC, size_mb DESC;

An index with zero seeks, scans and lookups but thousands of updates is pure overhead. An index with no usage rows at all (us is NULL) has not been touched since the restart either; the LEFT JOIN catches those.

Note that the filter deliberately leaves out primary keys and unique constraints. Even if never read, they enforce data integrity and stay.

What the counters don't show

Don't trust the list yet. Some indexes legitimately show no reads:

• Foreign key support. An index on a foreign key column speeds up deletes and updates on the parent table; that work does not always show as a seek on this index. Check sys.foreign_keys for matching columns.
• Quarterly or yearly jobs. Ask the application owner, and look at last_user_seek on the same index on other instances (a reporting copy, the previous production server) if you have them.
• Availability Group secondaries. Reads happening on a readable secondary are counted on the secondary, not on the primary. Run the query there too.
• Query hints. WITH (INDEX(...)) in application code will fail hard if the index disappears. Search the code and sys.sql_modules:

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE definition LIKE '%INDEX(IX_Orders_LegacyLookup)%';

Disable first, drop later

I never drop straight away. Disabling keeps the metadata so you can bring the index back with a rebuild instead of recreating it from a script you may not have. A disabled nonclustered index consumes no space and costs nothing on writes.

ALTER INDEX IX_Orders_LegacyLookup ON dbo.Orders DISABLE;

Script the definition first anyway (right-click in SSMS → Script Index as → CREATE To) and keep it with your change records.

Leave the index disabled for a few weeks. If a query starts failing or slowing down, rebuild it:

ALTER INDEX IX_Orders_LegacyLookup ON dbo.Orders REBUILD;

If nothing happens, drop it:

DROP INDEX IX_Orders_LegacyLookup ON dbo.Orders;

Duplicates are cheaper to find

A used index can still be redundant if another index on the same table has the same leading columns. This query groups indexes by their key column list:

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name,
       STUFF((SELECT ', ' + c.name
              FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
              WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
              ORDER BY ic.key_ordinal FOR XML PATH('')), 1, 2, '') AS key_columns
FROM sys.indexes i
WHERE i.type_desc = 'NONCLUSTERED' AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY table_name, key_columns;

Two rows with the same table and the same key_columns prefix are candidates for merging into one index with the union of their included columns.

Afterwards

After a round of disabling, keep an eye on sys.dm_exec_query_stats for regressions and on the missing index DMVs. If sys.dm_db_missing_index_details suddenly suggests an index that matches the one you just disabled, you have your answer, and a rebuild restores it in one statement.

Gotchas

• "Msg 8622: Query processor could not produce a query plan because of the hints defined in this query." appears when a query uses an index hint pointing at a disabled or dropped index. Rebuild the index or remove the hint.
• Disabling a clustered index makes the whole table inaccessible. The query above filters on NONCLUSTERED; do not extend it to clustered indexes.
• Disabling an index on a table that is part of a publication or an indexed view can fail with an error about the object being referenced; drop the dependency first or leave those indexes alone.
• The usage DMV counts plan executions that touched the index, not rows. One scan of a huge index once a year counts as one, which looks the same as a lookup index used once. Combine with last_user_* timestamps before deciding.

I do this once a quarter now. The only thing anyone notices is that the nightly index job finishes earlier.