Unused Index Detection

Identifies nonclustered indexes with zero seeks, scans, and lookups but active write overhead. Uses DMV usage stats as the primary signal and Query Store plan references as optional confirmation. All objects in the indexing schema in DBA_Utilities.

Why Unused Indexes Matter

Every nonclustered index on a table is maintained on every INSERT, UPDATE, and DELETE — whether or not any query ever reads from it. An unused index is pure cost:

• Write amplification: Every DML operation against the base table also updates every nonclustered index. A table with 10 indexes means each INSERT does 11 writes (1 clustered + 10 nonclustered). Removing one unused index is a permanent write throughput improvement.
• Storage waste: A 500 MB unused index wastes disk, backup time, restore time, and buffer pool memory that could be caching something useful.
• Maintenance overhead: Every index defragmentation run (REORG/REBUILD) spends time on unused indexes. On large tables, rebuilding a useless 2 GB index can take 10+ minutes that could have been spent on an index that matters.
• Optimizer confusion: More indexes means more choices for the query optimizer to evaluate. While rarely a bottleneck, a cleaner index set makes plans more predictable.

Why Detection Is Tricky

Detection Strategy

Phase 1: DMV Collection (Primary Signal)

Collects all nonclustered indexes where:

user_seeks = 0 AND user_scans = 0 AND user_lookups = 0 AND user_updates > 0

This means the index is being written to (maintained on every DML) but never read from by any query. The full index definition is captured including key column order (ASC/DESC), included columns, filter definitions, and fill factor — everything needed to recreate it later if the disable turns out to be wrong.

Phase 2: Query Store Confirmation (Optional)

When @WithQueryStore = 1 and the database has Query Store enabled with ≥ 30 days retention, the proc searches all stored execution plans for references to each flagged index. Uses CHARINDEX('[IndexName]', plan_text) with brackets for precise matching, avoiding false positives from substring matches.

Query Store is confirmation only — it runs against indexes already flagged by Phase 1. It never adds new indexes to the list.

Phase 3: Confidence Assignment

Each unused index is assigned a confidence level based on the strength of evidence:

Confidence Levels

The @Confidence parameter controls which levels generate active (uncommented) scripts. Indexes below the threshold still appear in the output but their action statements are wrapped in /* */ comments.

Actions: DISABLE vs DROP

Observation Window

The @MinObservDays parameter prevents premature recommendations. If the server restarted 3 days ago, even a heavily-used index will show zero reads in DMVs.

Safety Exclusions

The following are never flagged as unused, regardless of DMV stats:

• Primary keys — enforce entity integrity
• Unique indexes / unique constraints — enforce data integrity even if never explicitly seeked
• Clustered indexes — the table's physical data order
• Filtered indexes — serve specific query patterns that may be rare but important
• CDC schema objects — Change Data Capture internal indexes
• Hypothetical indexes — created by the Database Tuning Advisor, not real
• System tables

Write Overhead Filter

The @MinUpdateCount parameter filters out indexes with trivial write overhead. An unused index on a table that gets 5 updates per day isn't worth the operational effort of disabling it. The default of 100 filters out the noise; increase to 1000+ for large fleets where you want to focus on the highest-impact removals first.

Parameters

Note: No @RunnableUnder parameter — unlike tools that create indexes (which lock tables proportional to size), disabling or dropping an index is an instant metadata operation regardless of size.

Results Table

indexing.UnusedIndexResults — one row per unused nonclustered index:

Data Flow

sp_UnusedIndexRecommendations(@RunAnalysis = 1)
  |
  +--> sp_UnusedIndexAnalysis(@DatabaseName)
  |      Per-database loop:
  |        Phase 1: Collect unused NC indexes from DMVs
  |          - sys.indexes + sys.dm_db_index_usage_stats
  |          - Filter: seeks=0, scans=0, lookups=0, updates>0
  |          - Skip: PKs, unique, clustered, CDC, hypothetical
  |          - Capture full definition + recreate script
  |        Phase 2: Query Store confirmation (optional)
  |          - Search plan XML for index name references
  |          - Count distinct plans per index
  |        Phase 3: Assign confidence + insert results
  |      -> writes indexing.UnusedIndexResults
  |
  +--> Generate ScriptText output ranked by size (largest savings first)
         - Recreate script as comment (rollback safety)
         - Observation details + confidence per index
         - UPDATE ResolvedDate after each action

Usage Examples

-- Analyze all databases, view results
EXEC indexing.sp_UnusedIndexRecommendations @RunAnalysis = 1;

-- Single database
EXEC indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1, @DatabaseName = 'MyDatabase';

-- Wildcard pattern
EXEC indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1, @DatabaseName = 'MyApp_%';

-- Only flag indexes with significant write overhead
EXEC indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1, @MinUpdateCount = 10000;

-- Require at least 30 days of observation
EXEC indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1, @MinObservDays = 30;

-- High-confidence with Query Store confirmation (slower)
EXEC indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1, @WithQueryStore = 1;

-- Include MEDIUM confidence (commented out below threshold)
EXEC indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1, @Confidence = 'MEDIUM';

-- Drop instead of disable (HIGH confidence only)
EXEC indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1, @Action = 'DROP', @Confidence = 'HIGH';

-- Production recommendation: conservative settings
EXEC indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1,
    @DatabaseName = 'MyApp_%',
    @Action = 'DISABLE',
    @Confidence = 'HIGH',
    @MinUpdateCount = 1000,
    @MinObservDays = 14;