Indexing Overview
Overview
The Indexing Overview page is the front door to a database’s indexing. It gives a score with the reasons behind it, and a card per indexing report showing what that report found – so you can see where the problems are before opening anything.
What this fixes
The page used to be three eighty-pixel strips:
- A two-bar chart of unused and duplicate index counts.
- A strip titled “Worthless Indexes” that was never given anything to show – it was empty by construction.
- A single link to the Enterprise report.
Eleven indexing reports hang off the tree underneath this page, and the overview knew about two of them.
Where to find it
A database-level page. Select a database in the tree; Indexing Overview is one of its sections.
Reading the page
The score is a summary of the database’s indexing health, with the reasons listed rather than left implicit. A score without reasons is a number nobody can act on.
The cards are one per indexing report. Each shows what that report found – a count, a size, a verdict – and clicking it opens the report.
Cards are filled in as their background passes complete, so the page becomes useful progressively rather than waiting for every report to finish. A card that has not reported yet is still working, not empty.
The reports behind the cards
| Report | What it finds |
|---|---|
| Missing Indexes | Indexes the optimizer wanted, with benefit against write cost. |
| Unused Indexes | Zero reads, ranked by reclaimable space. |
| Duplicate Indexes | Identical key/include/filter signatures. |
| Inefficient Indexes | Written far more than read. |
| Most Used Indexes | What must not be touched. |
| Problem Indexes | Disabled, low fill factor, hypothetical. |
| Unclustered Tables | Heaps, and whether they are a problem. |
| Big Clustered Indexes | Wide clustering keys, by what the width costs. |
| Index Fragmentation | Fragmentation and page density. |
| Statistics | Whether the optimizer’s estimates are current. |
How to read the page
- Read the score and its reasons. The reasons say which cards to look at.
- Scan the cards for the largest counts.
- Start with the cheapest wins – hypothetical indexes on Problem Indexes and safe groups on Duplicate Indexes cost nothing to clean up.
- Then the trade-offs – Missing against Unused, since dropping one may pay for adding another.
- Finish with the structural items – heaps and wide clustering keys, which are bigger projects.
Related reports
Every report in the table above, plus Table Use for whether the table is read at all.
Frequently asked questions
Why is a card empty? It may still be loading. Cards fill in as their background passes complete.
Why is the score not a single number I can track? It is a summary with reasons attached. The reasons are the part that tells you what to do; the number on its own would not.
Which report should I start with? Problem Indexes and Duplicate Indexes usually contain the cheapest wins. Missing and Unused should be read together.