Home / Databases / SQL Server

DatabasesHow-toIntermediate

Optimization: SQL Server index review - missing, unused and duplicate indexes from DMVs and Query Store

Too few indexes slow reads; too many slow every write and waste memory. Review missing-index suggestions critically, find indexes that are written but never read, merge duplicates and overlapping indexes, and validate each change against Query Store before and after.

Deepak Bhardwaj2 min read

Applies toSQL Server 2016 and later, Azure SQL Database and Managed Instance

Missing index suggestions (treat as hints)

SELECT TOP 20
  DB_NAME(d.database_id) AS db, d.statement AS tbl,
  d.equality_columns, d.inequality_columns, d.included_columns,
  s.user_seeks, s.avg_user_impact,
  s.user_seeks * s.avg_total_user_cost * s.avg_user_impact AS score
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
WHERE d.database_id = DB_ID()
ORDER BY score DESC;

Before creating one:

  • Check if an existing index can be extended instead (add a column or INCLUDE).
  • Don't create every suggestion; they overlap and ignore write cost.
  • Key column order: equality columns first, then the most selective inequality.

Unused indexes

SELECT OBJECT_NAME(i.object_id) AS tbl, i.name AS idx,
       us.user_seeks, us.user_scans, us.user_lookups, us.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
  ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.index_id > 1 AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
  AND ISNULL(us.user_seeks + us.user_scans + us.user_lookups, 0) = 0
ORDER BY us.user_updates DESC;

Usage stats reset on restart (and on some index operations). Check sqlserver_start_time in sys.dm_os_sys_info and make sure the period covers month-end and other cycles before dropping anything.

Duplicate and overlapping indexes

  • Exact duplicates: same key columns in the same order. Drop one.
  • Left-prefix overlap: (A) and (A, B). The narrower one is often redundant unless it's unique or much smaller for a hot query.
  • Indexes differing only in INCLUDE columns can often be merged.

Safe change process

  1. Script the index definition before dropping (keep it in source control).
  2. Disable rather than drop first where practical: ALTER INDEX ix ON t DISABLE; (not for clustered indexes).
  3. Watch Query Store for regressed queries for a full business cycle.
  4. Drop when confident.

Measure the result

Metric Where
Top queries' duration and reads Query Store
Write latency on hot tables Query Store, wait stats
Database and backup size Size trends
Buffer pool pressure Page life expectancy trend

Warning

Some indexes support rare but critical work (quarter-end reports, unique constraints, foreign key lookups during deletes). Ask application owners before removing indexes that look unused.

Checklist

  • Missing index suggestions reviewed and consolidated
  • Unused indexes identified over a full business cycle
  • Duplicates and overlaps merged
  • Definitions scripted; disable before drop
  • Query Store checked after each change

Related fixes