Part of the DBA-Tools Project.
Index problems rarely announce themselves. A missing index shows up as a slow query someone eventually complains about; a duplicate or unused one just sits there costing write overhead and storage with nothing to show for it; fragmentation quietly degrades range scans until someone happens to check. Seven scripts on this site cover the full index health picture, from finding indexes that don’t exist yet to finding the ones that shouldn’t.
This post is the map. It groups them by what they find, and lays out a sensible order for a routine index health pass rather than checking each in isolation.
Why Index Maintenance Matters
- A missing index forces a scan where a seek would do, the cost compounds with table size and query frequency, small tables rarely notice, large ones notice constantly
- Every index costs something on every write, an unused or duplicate index is pure overhead with no offsetting benefit, and it’s invisible until someone specifically goes looking
- Fragmentation affects range scans and read-ahead specifically, a heavily updated table can fragment fast even with otherwise reasonable index design
- A heap (a table with no clustered index at all) inherits problems none of the others do, forwarded records, no natural sort order, full scans on every non-covering lookup
The Scripts, Grouped by What They Find
Indexes that don’t exist yet
- Missing Indexes, SQL Server’s own missing-index DMVs, surfaced with the impact estimate that tells you whether it’s worth creating
Indexes that shouldn’t exist
- Unused Indexes, indexes with real maintenance cost and no read activity to justify it
- Duplicate Indexes, exact-duplicate and prefix-overlapping indexes, paying the write cost of two indexes that do the same job as one
Indexes with a structural problem
- Index Design Issues, wide keys, too many included columns, and other design-level problems that show up as index bloat and slow writes rather than missing coverage
- Heaps, tables with no clustered index at all, the specific problems that come from having no natural row order
Indexes that need maintenance, not redesign
- Index Fragmentation Across Databases, a fast, wide sweep across every database on the instance
- Index Fragmentation, the single-database, single-table drill-down once the wide sweep points somewhere specific
How They Fit Together
A sensible order for a routine index health pass:
- Start wide with Index Fragmentation Across Databases, to see the whole instance at a glance before drilling into any one database
- Drill into a specific finding with Index Fragmentation once the wide sweep points at a particular table or database worth a closer look
- Check for indexes with no reason to exist using Unused Indexes and Duplicate Indexes, both cost real write overhead for zero benefit, and both are easy to miss without specifically checking
- Check for structural problems with Index Design Issues and Heaps, these aren’t fixed by a rebuild, they need an actual design change
- Check for coverage gaps with Missing Indexes last, since adding an index without first clearing out unused and duplicate ones just adds more write overhead on top of what’s already there
Best Practices Across the Series
- Don’t add a missing index without first checking whether an existing index is already close, a slightly different key order sometimes covers the same query without adding a new one
- Review unused and duplicate indexes before adding new ones, removing dead weight first keeps overall write overhead in check
- Rebuilding or reorganizing a heavily fragmented index doesn’t fix a design problem, wide keys and heaps need an actual schema change, not routine maintenance
- Re-run the wide fragmentation sweep on a schedule, not just when a specific query is already reported slow, fragmentation builds quietly on any actively updated table
See Also
This pillar is one of ten in DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
Index health isn’t one check, it’s several different questions: what’s missing, what shouldn’t exist, what’s structurally wrong, and what just needs routine maintenance. These seven scripts answer each of those separately, because the fix for a missing index, an unused one, and a fragmented one are three completely different actions.
Start wide with Index Fragmentation Across Databases if you haven’t run an index health pass recently, then follow the chain from there into whichever category turns up a real finding.
Leave a Reply