Index health

The Performance → Index Health and Missing Indexes areas help you find indexes that slow things down – fragmented, unused, duplicate and missing.

Scans run in the background

Index checks are expensive, so they run on a schedule in the background and the results are stored. The page reads from those stored runs instead of hitting SQL Server on every load. Three scan types: Fragmented, Unused, Duplicate.

Admins can trigger a run immediately with Scan now. If one is already running for that combination, the button is disabled.

If "Scan in progress" stays up for a very long time, the process was probably stopped mid-scan. The scheduler reaps such orphaned runs automatically after a timeout.

Missing indexes & impact analysis

Missing Indexes come from SQL Server's own suggestions. Analyze Impact opens a detailed analysis per suggestion:

• Storage Footprint – the table's current on-disk size plus the estimated size of the new index.
• Existing Indexes on This Table – existing indexes with a merge advisor: it detects whether a suggestion is already covered by an existing index (redundant), whether an existing index should be extended (extend), or whether a new index makes sense (create new) – including ready-to-use ALTER/CREATE SQL.

If the table isn't found, it was likely renamed or dropped after the suggestion was captured; the analysis says so clearly.

Merge instead of duplicating

The merge advisor emits CREATE INDEX … WITH (DROP_EXISTING = ON) so you consolidate indexes instead of creating expensive overlaps with duplicate maintenance cost.

Rule of thumb: every index speeds up reads but makes writes more expensive. Weigh suggestions against the read/write ratio and actual usage before creating them – ideally test in a non-production environment first.