Finding Unused Indexes

A zero in an index usage report can mislead anyone finding unused indexes. SQL Server can show index usage since a reset, but it cannot tell you that a year-end process will never need an index again. Treat the counters as evidence with a date attached.

What the Usage DMV Records

sys.dm_db_index_usage_stats tracks user seeks, scans, lookups, and updates for indexes in the current database. A seek or scan shows that an access path was used during the observed period. A lookup can show a clustered index serving data after another index found rows. Updates indicate maintenance activity from data changes.

The DMV does not keep a permanent history. A row can be absent if there has been no recorded activity since reset. It does not explain whether a query was important, how much time the index saved, or whether a specialized report will run next week. I treat it as a candidate generator, then investigate candidates. Which scheduled job would use this index after your current observation window?

Establish the Observation Window Before Finding Unused Indexes

A SQL Server restart clears usage counters. Database operations and index recreation can also affect what you see. Capture sqlserver_start_time and the date when you collected the data. If the server restarted yesterday, a zero says little about a monthly or quarterly job. A long observation window that spans the business cycle is more useful.

I ask about billing runs, holiday peaks, data exports, and maintenance before tagging an index as unused. The quietest index can support the most stressful day of the month. A calendar beside the DMV output makes the numbers more honest. Without that context, a neat zero is mostly a scheduling accident.

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT DB_NAME(s.database_id) AS database_name,
       OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS table_name,
       i.name AS index_name, i.index_id,
       COALESCE(s.user_seeks, 0) AS seeks,
       COALESCE(s.user_scans, 0) AS scans,
       COALESCE(s.user_lookups, 0) AS lookups,
       COALESCE(s.user_updates, 0) AS updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.database_id = DB_ID()
 AND s.object_id = i.object_id AND s.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
  AND i.index_id > 0
ORDER BY i.index_id;

Read Updates Correctly

user_updates is the number of update operations against an index, not the number of rows changed. A single statement that modifies many rows is counted as an operation. It still points to write activity, but dividing user_updates by user_seeks does not produce a universal value score. One seek can save a costly table scan while many updates are cheap.

Think about index width and the columns being modified. A wide index on a frequently changed status field has a different maintenance burden from a narrow index on stable data. Use write duration, log generation, latch waits, and plans where those costs matter. The counter provides direction, not a price tag.

Exclude Protected Indexes When Finding Unused Indexes

Primary keys and unique constraints protect data rules. A zero read count does not mean those rules can be discarded. Unique indexes can also prevent duplicate values before an application sees them. Keep them out of an automated unused-index drop list. Verify whether an index supports foreign key checks, even though SQL Server does not create every foreign key index automatically.

Filtered indexes and indexes used by infrequent critical procedures need extra care. Check stored procedure plans and Query Store history where available. I would rather document an apparently idle enforcement index than remove it to improve a maintenance score. A smaller catalog is not a substitute for correct data.

Compare Definitions and Query Plans

An index can be unused because another index covers the same access path. Compare key order, included columns, filters, uniqueness, and partitioning before choosing which one to keep. Similar names mean very little. Two access paths with the same first column can diverge sharply once a range predicate or ORDER BY appears.

Look at plans for expensive and frequent queries. An index can appear in a cached plan without having been executed during the current window, while a critical query could have been evicted from cache. Query Store can offer a longer view when it is enabled and retaining the relevant history. Cross-checking independent evidence reduces the chance of deleting a useful path.

Build a Candidate List for Finding Unused Indexes

This query narrows the inventory to nonconstraint, nonclustered indexes with no recorded reads and at least one recorded update in the current counter window. It is deliberately a review list, not a script that drops anything. Change the table filter or remove it to inspect the database, but keep the workload calendar beside the output.

No-read indexes are not automatically safe to delete. This query does not account for server uptime, a disabled feature, a seasonal job, or a newly deployed query. Its value is to focus human review on a manageable set.

SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
       t.name AS table_name, i.name AS index_name,
       s.user_updates
FROM sys.indexes AS i
JOIN sys.tables AS t ON t.object_id = i.object_id
JOIN sys.dm_db_index_usage_stats AS s
  ON s.database_id = DB_ID()
 AND s.object_id = i.object_id AND s.index_id = i.index_id
WHERE i.type = 2
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND COALESCE(s.user_seeks, 0) = 0
  AND COALESCE(s.user_scans, 0) = 0
  AND COALESCE(s.user_lookups, 0) = 0
  AND s.user_updates > 0
ORDER BY s.user_updates DESC;

Test Before Removing

Save the complete CREATE INDEX definition, including filter, included columns, compression, and options. On a representative test system, remove one candidate and exercise the queries that could use it. Compare duration, logical reads, CPU, and plan changes. The right test includes parameter values that produce both small and large result sets.

For production, schedule one change at a time and prepare the recreation command. Large index builds can require significant time, transaction log, and extra space. Monitor after deployment through a full relevant business cycle. If the index was used only at month end, a week of quiet monitoring does not close the case.

Avoid Automated Cleanup Rules

An unattended job that drops indexes with zero seeks is dangerous. It can erase enforcement structures, specialized paths, or indexes whose use fell outside the counter window. It can also miss an expensive index with one seek that provides negligible benefit. Automated reporting is useful. Automated deletion needs evidence the DMV does not possess.

I keep a review record: counter start, collection date, index definition, queries served, proposed change, test result, and rollback script. That record makes a removal explainable months later. It also makes a wrong decision reversible before a user has to discover it through a slow screen.

Repeat the Review After Change

After a removal, recheck the affected query group and write workload. The optimizer can switch to another index, sometimes improving reads and sometimes creating extra key lookups. Query Store and application metrics help reveal that movement. Restore the index if a critical path regresses and investigate a narrower replacement if the original was too expensive.

Finding unused indexes is a continuing process because workloads evolve. New features make old indexes valuable, and retired features leave structures behind. The useful outcome is a measured index portfolio with known purposes. The punchline is that an unused index can be expensive, but an unmeasured deletion can be more expensive.

Related reading on this blog: Your Index Rebuild Maintenance Plan Is Rebuilding Indexes Nobody Uses and Unused Index Script: Download.

Finding unused indexes is not reading zeroes, it is proving an index no longer earns its cost.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.