Two Problems. Same Table.
The query is slow because there is no index on that column. The nightly job is slow because it has to maintain 14 indexes on a table that only 3 of them ever get used.
Indexes are not free. Every INSERT, UPDATE, and DELETE has to maintain every index on the table. The ones nobody reads are pure write overhead.
This query finds both problems at once.
What This Query Does
Part 1 — Missing Indexes
SQL Server tracks every time the query optimiser had to do a scan and thinks an index would have helped.
That data lives in sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats.
The Index Benefit Score multiplies estimated cost improvement by seek/scan frequency — higher is more impactful.
The Suggested CREATE INDEX column gives you a ready-to-run script, though you should always review before executing.
These suggestions reset on SQL Server restart. A high score means the optimiser has wanted this index many times since last restart — not necessarily since the database was created.
Part 2 — Unused Indexes
sys.dm_db_index_usage_stats tracks every seek, scan, lookup, and update on every index since the last restart.
An index with zero reads but hundreds of thousands of writes is costing you with nothing in return.
The Drop Script column gives you the DROP statement — but verify the index is not used by a unique constraint or required for replication before running it.
Missing Indexes Script
Loading…
Gareth Winterman