Overview
Index Advisor is the index-focused review and maintenance planning module. It combines deterministic classification, Query Store-backed evidence, and optional AI interpretation to help you decide which indexes should be kept, maintained, validated, or considered for removal.
What You Can Do
- Collect index metadata and workload signals for the active database.
- Classify indexes with a deterministic scoring model.
- Identify effective, weak, maintenance-heavy, or unnecessary indexes.
- Estimate drop safety with Query Store evidence and guardrails.
- Generate scripts, export reports, and run AI analysis for a selected index.
Main Screen Areas
- Header area
- Deterministic analysis table
- Right-side detail tabs
- Footer summary cards
Analysis Model & Data Sources
Base Collection
- Collects index metadata, usage, physical stats, and storage signals.
- Uses index metadata, DMV usage counters, fragmentation, and statistics freshness inputs.
- Can fall back to a legacy path if the primary collection query fails.
Query Store Enrichment
- Adds 30-day usage trend evidence for the selected index.
- Finds dependent queries whose plans referenced the index.
- Estimates workload impact if the index becomes unavailable.
Deterministic Classes
- Effective and Effective Mandatory
- Weak and Weak But Necessary FK
- Needs Maintenance
- Unnecessary
Drop Safety
- Do Not Drop
- Validate Before Drop
- Safe Drop Candidate
- Safe Drop Candidate still means review first, not immediate removal.
Controls & Filters
Primary Buttons
- Refresh
- Generate Selected Action Script
- Export Visible / Selected Report
- Copy Script
- Analyze, Save LLM JSON, and Save HTML
Combobox Filters
- Table
- Class
- Drop Safety
These filters are applied against the current analyzed dataset to narrow the review surface.
Checkboxes
- Show Needs Attention Only
- Include Low-Usage Risk Pool on Refresh
- Select Visible Needs Action
Score Slider
- Filters by deterministic score threshold.
- Range is 0 to 100.
- Updates the client-side filtered table immediately.
Results Table & Batch Actions
Main Columns
- Table and Index
- Class and Drop Safety
- Score
- Read/Write ratio
- Fragmentation, seeks, and writes
Selection Behavior
- Selecting a row refreshes Script, Metrics, and deterministic AI preview.
- The module also starts loading Query Store usage trend and dependency data.
- Right-click actions provide Copy Script and Analyze with AI shortcuts.
Batch Script Generation
- Scripts rebuild, reorganize, or update statistics for maintenance-focused rows.
- Scripts drop indexes only for safe drop candidates.
- Unsafe or protected cases produce review comments instead of destructive SQL.
Report Export
- Exports selected rows if any are selected.
- Otherwise exports all currently visible filtered rows.
- Produces a Markdown report for sharing or audit review.
Detail Tabs
Script
- Shows a reconstructed best-effort CREATE INDEX style script.
- Can include uniqueness, index type, key columns, included columns, filters, and options.
- ONLINE option is decided according to edition or environment capability.
Metrics
- Explains score model, value, usage, storage, design, and diagnostics.
- Includes overlap analysis, column heatmap, dependency context, and drop safety reasoning.
- Best place to understand why an index got its class and score.
AI Analysis
- Shows a deterministic preview even before AI is run.
- Analyze starts AI interpretation for the selected index.
- Save LLM JSON and Save HTML become available after usable output exists.
History
- Shows Query Store trend data for the last 30 days when available.
- Includes reads sparkline, data points, executions, logical reads, and recent daily entries.
- Explains missing-data or Query Store error states when history is unavailable.
Summary, Workflow & Safety
Summary Cards
- Analyzed
- Needs Action
- Avg Score
These cards reflect the last full refresh dataset, not only the currently filtered rows.
Typical Workflow
- Refresh the active database analysis.
- Narrow the table with Table, Class, Drop Safety, and Score.
- Keep Needs Attention enabled when focusing on action items.
- Select an index and inspect Script, Metrics, AI Analysis, and History.
- Use batch selection, script generation, or export when working across multiple rows.
- Validate any drop candidate before making production changes.
Interpretation Notes
- The module is decision support only and does not execute generated scripts.
- Safe Drop Candidate is a recommendation category, not a production guarantee.
- Query Store-dependent history and impact sections are only as good as the available evidence.
- Include Low-Usage Risk Pool changes the next refresh candidate set and does nothing until refresh is run again.