SQL Server Unused Indexes: sys.dm_db_index_usage_stats

calendar Apr 1, 2026 / · 7 min read · sql server performance sql scripts sys.dm_db_index_usage_stats index maintenance database administration nonclustered indexes query tuning maintenance scripts indexes  ·
Share on: linkedin copy

Find Unused Nonclustered Indexes in SQL Server

This script queries sys.dm_db_index_usage_stats to identify nonclustered indexes that have never been used by any read operation since the last SQL Server restart, yet still carry write overhead with every INSERT, UPDATE, and DELETE on the table.

Purpose and Overview

Every nonclustered index in SQL Server has a cost: each time a row is inserted, updated, or deleted in the base table, SQL Server must also update every nonclustered index on that table. If an index is never used by the query optimizer — no seeks, scans, or lookups — it is pure overhead with no benefit. Over time, databases accumulate these unused indexes through abandoned development, changed application query patterns, or indexes added by automated tools without follow-up review. This script, from the DBA-Scripts collection by Bulent Gucuk, reads sys.dm_db_index_usage_stats to expose every nonclustered index with zero read activity since the last server restart, and generates the DROP INDEX statement for each one.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

 1SELECT
 2    DB_NAME()                                              AS database_name,
 3    s.name                                                 AS schema_name,
 4    o.name                                                 AS table_name,
 5    i.name                                                 AS index_name,
 6    i.index_id,
 7    dm_ius.user_seeks                                      AS user_seeks,
 8    dm_ius.user_scans                                      AS user_scans,
 9    dm_ius.user_lookups                                    AS user_lookups,
10    dm_ius.user_updates                                    AS user_updates,
11    p.table_rows                                           AS row_count,
12    'DROP INDEX ' + QUOTENAME(i.name)
13        + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
14                                                           AS drop_statement
15FROM sys.dm_db_index_usage_stats AS dm_ius
16INNER JOIN sys.indexes AS i
17    ON i.index_id = dm_ius.index_id
18   AND dm_ius.object_id = i.object_id
19INNER JOIN sys.objects AS o
20    ON dm_ius.object_id = o.object_id
21INNER JOIN sys.schemas AS s
22    ON o.schema_id = s.schema_id
23INNER JOIN (
24    SELECT SUM(p.rows) AS table_rows, p.index_id, p.object_id
25    FROM sys.partitions AS p
26    GROUP BY p.index_id, p.object_id
27) AS p
28    ON p.index_id = dm_ius.index_id
29   AND dm_ius.object_id = p.object_id
30WHERE OBJECTPROPERTY(dm_ius.object_id, 'IsUserTable') = 1
31    AND dm_ius.database_id = DB_ID()
32    AND i.type_desc = 'NONCLUSTERED'
33    AND i.is_primary_key = 0
34    AND i.is_unique_constraint = 0
35    AND o.is_ms_shipped = 0
36ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
37OPTION (RECOMPILE);

sys.dm_db_index_usage_stats

sys.dm_db_index_usage_stats accumulates index usage counts since the last SQL Server service restart. It holds one row per index per database that has been accessed at least once. The key columns this script uses:

  • user_seeks — index seek operations driven by user queries; seeks are the most efficient read access pattern
  • user_scans — index scan operations driven by user queries; a scan reads all or part of the index
  • user_lookups — key lookup operations where SQL Server used this index for a seek but then had to go back to the clustered index to retrieve additional columns
  • user_updates — the number of times this index was updated due to INSERT, UPDATE, or DELETE operations on the base table

An index with user_seeks + user_scans + user_lookups = 0 but a high user_updates count is paying the write cost with no read benefit. The ORDER BY sorts ascending on the sum of read operations, putting the least-used indexes at the top.

Filters That Focus on Safe Drop Candidates

Several WHERE conditions narrow the output to indexes that are genuinely candidates for removal:

  • i.type_desc = 'NONCLUSTERED' — clustered indexes define the physical row order and cannot simply be dropped
  • i.is_primary_key = 0 — primary key indexes enforce uniqueness and referential integrity; they must never be dropped
  • i.is_unique_constraint = 0 — unique constraint indexes enforce data integrity and may be referenced by foreign keys
  • o.is_ms_shipped = 0 — excludes system objects
  • OBJECTPROPERTY(dm_ius.object_id, 'IsUserTable') = 1 — restricts to user tables, excluding views and other object types
  • dm_ius.database_id = DB_ID() — scopes results to the current database, since the DMV contains rows from all databases on the instance

Generated DROP INDEX Statement

The drop_statement column builds a ready-to-execute DROP INDEX statement using QUOTENAME to safely bracket the index and table names. QUOTENAME wraps names in square brackets, handling any reserved words or special characters in object names. The format DROP INDEX [index_name] ON [schema].[table] is the correct two-part syntax for DROP INDEX in SQL Server 2005 and later.

Row Count Subquery

The subquery against sys.partitions sums rows across all partitions for each index and object combination. This provides the row_count column, which is valuable context when reviewing candidates. An unused index on a 500-million-row table has a much higher write cost than the same index on a 1,000-row table, and should be prioritized for removal.

Key Benefits and Use Cases

  • Identifies indexes that are costing write performance with zero read benefit
  • Generates DROP INDEX statements ready for review and execution
  • Row count column helps prioritize high-impact indexes on large tables
  • user_updates column quantifies the actual write cost of each unused index
  • Filters exclude primary keys, unique constraints, and system objects — only safe drop candidates appear
  • Works on all SQL Server editions; reads only built-in DMVs

Performance Considerations

  • Reset on restart: sys.dm_db_index_usage_stats is cleared every time SQL Server restarts. If the server restarted recently, the data may not reflect true long-term usage patterns. The longer the server has been running since its last restart, the more reliable the unused index list.
  • Seasonal workloads: A query that runs only at month-end or once a year may use certain indexes that appear unused after a recent restart. Do not drop indexes based on a single observation — compare across multiple weeks or, ideally, a full business cycle.
  • Query Store: SQL Server 2016 and later includes Query Store, which persists query statistics across restarts. Check Query Store for any queries that reference the index before dropping it.
  • Replication and ETL: Some indexes exist to support replication, CDC (Change Data Capture), or ETL processes that do not show in normal user_seeks counts. Verify with the application and DBA team before dropping.
  • Missing indexes: After removing unused indexes, re-run the missing index DMV queries (sys.dm_db_missing_index_details) to confirm no missing index recommendations appeared for the same columns. Dropping an index the optimizer has also stopped recommending validates the decision.

Practical Tips

To also include indexes that have very low usage — not just zero — adjust the ORDER BY filter to show indexes below a threshold:

1WHERE (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) < 100
2    AND dm_ius.user_updates > 1000

This surfaces indexes used rarely (fewer than 100 reads ever) but updated frequently (more than 1,000 writes), which may still be worth dropping if the read count is very low relative to the write cost.

Before dropping any index in production, script it first so it can be recreated quickly if needed:

 1-- Script the index definition before dropping
 2SELECT
 3    'CREATE NONCLUSTERED INDEX ' + QUOTENAME(i.name)
 4    + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
 5    + ' (' +
 6        STUFF((
 7            SELECT ', ' + QUOTENAME(c.name) + CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ASC' END
 8            FROM sys.index_columns ic
 9            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
10            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
11            ORDER BY ic.key_ordinal
12            FOR XML PATH('')
13        ), 1, 2, '')
14    + ');'
15FROM sys.indexes i
16JOIN sys.objects o ON i.object_id = o.object_id
17JOIN sys.schemas s ON o.schema_id = s.schema_id
18WHERE i.name = 'YourIndexName';

Drop unused indexes one at a time in a non-production environment first, monitor query performance, then promote to production with confidence.

Conclusion

Unused indexes are a hidden tax on write performance in SQL Server. Every INSERT, UPDATE, and DELETE pays the cost of maintaining every nonclustered index on the table, even if the query optimizer never uses those indexes for reads. This script surfaces those indexes quickly, quantifies their write overhead through user_updates, and generates the DROP INDEX statements needed to reclaim that performance. Use it as the starting point for an index review — not as an automatic drop list. Validate uptime since last restart, check for seasonal queries, and script the index definition before dropping. Done carefully, removing unused indexes is one of the most reliable ways to improve write throughput on a SQL Server database.

References

Posts in this series