Unused Indexes in SQL Server: Find them, vet them, and drop them safely

Indexes are fantastic when you’re reading data—and a tax when you’re writing it. Every INSERT/UPDATE/DELETE has to maintain each affected index; more indexes mean longer transactions, heavier logging, bigger backups, and sometimes blocking. Cleaning up unused indexes is one of the quickest wins in a health check—if you do it safely.

Don’t let unused indexes drive your SQL Server write I/O and CPU through the roof.

Clean them up to keep I/O, CPU, and SQL Server under control.

How SQL Server tracks “usage”

sys.dm_db_index_usage_stats increments counters when the optimizer seeks, scans, or looks up rows through an index since the last SQL Server restart. If the DMV shows no seeks, no scans, no lookups, that index hasn’t been touched for reads (since the last restart). Two big implications:

• DMV data resets on service restart. Don’t trust usage stats right after a reboot.

• Writes still cost you. user_updates shows the index is maintained even if it’s never read.

Recommendation: Track usage over 30–90 days (14 is an absolute minimum).

Add an uptime gate (don’t analyze right after a restart)

Option A – stop early with an info row

-- Stop early if SQL Server has been up for less than 14 days
DECLARE @MinDaysSinceRestart int = 14;
DECLARE @StartTime datetime2 = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info);

IF DATEDIFF(DAY, @StartTime, SYSDATETIME()) < @MinDaysSinceRestart
BEGIN
    SELECT 
        sqlserver_start_time = @StartTime,
        days_since_restart   = DATEDIFF(DAY, @StartTime, SYSDATETIME()),
        info                 = CONCAT('SQL Server was restarted less than ', @MinDaysSinceRestart,
                                      ' days ago. No index drop suggestions are shown.');
    RETURN;
END;

Option B – silent filter inside your query

AND DATEDIFF(DAY, (SELECT sqlserver_start_time FROM sys.dm_os_sys_info), SYSDATETIME()) >= 14

Safer candidate query (rowstore only, current DB, sized, with drop script)

This version excludes PK/unique/disabled/hypothetical indexes, focuses on rowstore (clustered/nonclustered), requires non-zero writes and zero reads, scopes to current DB, and surfaces size to help you prioritize by impact.

-- Unused index candidates in the current database (rowstore only), prioritized by write cost and size
SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id)        AS TableName,
    i.name                          AS IndexName,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS IndexSizeMB,
    s.user_seeks, 
    s.user_scans, 
    s.user_lookups, 
    s.user_updates,
    'DROP INDEX ' + QUOTENAME(i.name) + ' ON '
        + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) AS Drop_Index
FROM sys.indexes AS i
JOIN sys.dm_db_index_usage_stats AS s
  ON s.object_id = i.object_id
 AND s.index_id  = i.index_id
 AND s.database_id = DB_ID()          -- scope to this database
JOIN sys.dm_db_partition_stats AS ps
  ON ps.object_id = i.object_id
 AND ps.index_id  = i.index_id
WHERE
    i.index_id > 0                     -- exclude heaps
    AND i.type IN (1,2)                -- 1=clustered, 2=nonclustered (rowstore)
    AND i.is_hypothetical = 0
    AND i.is_disabled = 0
    AND i.is_primary_key = 0           -- exclude PK
    AND i.is_unique = 0                -- exclude unique indexes
    AND s.user_updates <> 0            -- writes happened (maintenance cost)
    AND s.user_lookups = 0             -- no reads since last restart
    AND s.user_seeks   = 0
    AND s.user_scans   = 0
GROUP BY i.object_id, i.index_id, i.name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
ORDER BY s.user_updates DESC, IndexSizeMB DESC;

Want to quantify write pressure further? Cross apply operational stats:

CROSS APPLY sys.dm_db_index_operational_stats(DB_ID(), i.object_id, i.index_id, NULL) AS os
-- Then select: (os.leaf_insert_count + os.leaf_update_count + os.leaf_delete_count) AS LeafWrites

What you generally shouldn’t drop

• Primary key and unique indexes (already excluded).

• Indexes that support indexed views, filtered scenarios that matter, or specialized types (XML, spatial, columnstore) — treat with a different policy.

• Indexes that support foreign keys (even if “unused,” they can be critical for parent DELETE/UPDATE performance). At minimum, flag them for manual review.

Quick flag for likely FK-supporting indexes (for review, not auto-exclusion):

-- Adds a simple warning column for indexes whose key columns overlap FK columns (heuristic)
WITH fk_cols AS (
  SELECT f.parent_object_id AS object_id, fc.parent_column_id AS column_id
  FROM sys.foreign_keys AS f
  JOIN sys.foreign_key_columns AS fc ON fc.constraint_object_id = f.object_id
)
SELECT DISTINCT
  i.object_id, i.index_id,
  'Candidate supports FK columns' AS FKWarning
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN fk_cols AS fk ON fk.object_id = ic.object_id AND fk.column_id = ic.column_id
WHERE i.index_id > 0 AND i.type IN (1,2);

The subtle edge case: “unused” index statistics that are used

An index can influence plans without being physically accessed. Creating an index creates a statistics object. The optimizer may use that statistic for cardinality estimates even when the final plan scans a clustered index. The DMV still shows zero reads for the nonclustered index, but its stats are helping the plan.

• If AUTO_CREATE_STATISTICS is ON (default), SQL Server will often auto-create a similar stat if you drop the index.

• If it’s OFF, dropping the index also drops the stat and can hurt estimates/plan quality.

Safer play before a drop (especially for filtered indexes or AUTO_CREATE_STATISTICS = OFF):

-- Preserve a key histogram/statistic before dropping an "unused" index
CREATE STATISTICS [STAT__YourTable__YourColumnList]
ON dbo.YourTable (YourColumnList)
WITH FULLSCAN;  -- if the index was filtered, add the same WHERE filter here

Generate recreate scripts before you generate drop scripts

Always have a rollback. Here’s a lightweight generator for the CREATE script of each candidate index:

-- Generate CREATE scripts for candidate indexes (use alongside your candidate list)
SELECT 
  '/* Recreate */ ' +
  'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
  CASE i.type WHEN 1 THEN 'CLUSTERED' WHEN 2 THEN 'NONCLUSTERED' END + ' INDEX ' + QUOTENAME(i.name) +
  ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + '(' +
  STUFF((
      SELECT ', ' + QUOTENAME(c.name) + CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ASC' END
      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(''), TYPE).value('.', 'nvarchar(max)')
  ,1,2,'') + ')' +
  CASE 
    WHEN EXISTS (
      SELECT 1 FROM sys.index_columns ic
      WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
    )
    THEN ' INCLUDE (' +
      STUFF((
        SELECT ', ' + QUOTENAME(c2.name)
        FROM sys.index_columns ic2
        JOIN sys.columns c2 ON c2.object_id = ic2.object_id AND c2.column_id = ic2.column_id
        WHERE ic2.object_id = i.object_id AND ic2.index_id = i.index_id AND ic2.is_included_column = 1
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') ,1,2,''
      ) + ')'
    ELSE ''
  END +
  CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END AS CreateIndexScript
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.YourTableName'); -- or join to your candidate set

Then, generate the drop statements from the candidate query (already shown in Drop_Index).

A one-minute drop checklist

1. Uptime window covered? ≥ 30–90 days (14-day minimum gate in scripts).

2. Candidate is rowstore (not XML/spatial/columnstore), not PK/unique, not disabled/hypothetical.

3. Zero seeks/scans/lookups; non-zero updates.

4. Not obviously supporting FKs, indexed views, or critical filtered predicates.

5. Stats plan risk addressed (either AUTO_CREATE_STATISTICS is ON, or you created equivalent stats).

6. Recreate script saved; tested in lower env.

7. Monitoring in place (Query Store/Perf counters) to catch regressions post-drop.

Summary

Cleaning up unused indexes reduces write amplification, speeds maintenance, and shrinks databases. Use sys.dm_db_index_usage_stats to find candidates, gate on uptime, exclude key/unique/disabled/hypothetical, focus on rowstore, and prioritize by write cost and size. Watch out for the statistics edge case, and always have a rollback. Prune with confidence—and keep I/O and CPU from going through the roof.