How to Find Unused Indexes in SQL Server and Why You Shouldn’t Drop Them Immediately

Before dropping an index because sys.dm_db_index_usage_stats shows little or no usage, check how long the counters have been collecting data and whether the index is important for writes, constraints, or occasional business workloads.

A simple query can help identify candidate indexes:

SELECT
    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,
    ISNULL(us.user_seeks, 0) AS user_seeks,
    ISNULL(us.user_scans, 0) AS user_scans,
    ISNULL(us.user_lookups, 0) AS user_lookups,
    ISNULL(us.user_updates, 0) AS user_updates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
    AND us.object_id = i.object_id
    AND us.index_id = i.index_id
WHERE i.object_id > 100
  AND i.index_id > 0
ORDER BY
    ISNULL(us.user_seeks, 0)
  + ISNULL(us.user_scans, 0)
  + ISNULL(us.user_lookups, 0);

sys.dm_db_index_usage_stats tracks user seeks, scans, lookups, and updates for indexes. However, its counters are not a permanent historical record; they can reset when the database comes online, so an index that currently shows zero usage is not automatically an unused index.

Real-World Example

Imagine you find:

Index                  Seeks   Scans   Lookups   Updates
---------------------------------------------------------
IX_Order_CustomerID       0       0         0    125000

It is tempting to conclude:

“This index is useless. Drop it.”

But that’s not enough evidence.

The index may be:

Used by a monthly report.
Used during an end-of-month process.
Required by a particular query that hasn’t executed since the last restart.
Supporting a constraint or uniqueness requirement.
Important during a workload that happens only occasionally.

Also notice the 125,000 updates.

Even if the index provides little read benefit, it is receiving maintenance work from data modifications. The DMV’s user_updates counter represents update operations, not the exact number of rows affected.

Better Investigation

Before dropping it, check:

1. How long have the counters been collecting data?

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

If SQL Server restarted yesterday and your business cycle is weekly or monthly, today’s usage numbers may be misleading.

2. Check the index definition

SELECT
    i.name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.Orders');

3. Check the workload

Look at Query Store, application workload, scheduled jobs, and reporting processes before making a decision.

4. Consider write overhead

An index that is never useful for reads but receives frequent updates may be a candidate for removal but still requires testing and validation.

Best Practices

Treat DMV-based unused-index queries as candidate identification, not automatic drop lists.
Check the SQL Server/database uptime before interpreting usage counters.
Review the complete index definition before dropping anything.
Check primary-key and unique-constraint status.
Look at Query Store and known reporting/ETL workloads.
Consider index size and write-maintenance cost.
Check whether another index overlaps with the candidate.
Test removal in a non-production environment.
Monitor performance after any index change.

SQL Server’s Query Optimizer also relies on statistics when generating plans, so don’t confuse an index problem with a statistics problem.

Common Mistake

“user_seeks = 0, therefore drop the index.”

That’s one of the most dangerous shortcuts in index maintenance.

Another common mistake is to use a fragmentation percentage as the only reason to rebuild an index. Microsoft recommends measuring whether index maintenance actually improves the workload rather than assuming that rebuilding an index automatically improves performance.

Pro Tip

Don’t maintain indexes based on a single snapshot.

For a production database, collect index usage information across a representative business cycle. For example, enough time to include normal OLTP activity, reporting, month-end processing, ETL, and other periodic workloads.

Then combine:

Index usage + Query Store + index size + write activity + workload knowledge

before deciding whether an index should be removed.