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.
In a typical SQL Server tuning workflow, this page is most valuable after Query Statistics surfaces a query pattern worth investigating or after Dashboard points to workload pressure that might be index-related.
- 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 review scripts, export reports, and run AI analysis for a selected index.
- Header area
- Deterministic analysis table
- Right-side detail tabs
- Footer summary cards
Deterministic Index Analysis Workspace
The main screen combines the status banner, filter row, analysis table, detail tabs, and action controls in one working surface. This is where the user ranks indexes, narrows the review set, and decides whether the next step is maintenance, validation, or deeper analysis.
- Collects index metadata, usage, physical stats, and storage signals.
- Uses DMVs, fragmentation, statistics freshness, and operational signals.
- Can fall back to a legacy collection path if the primary query fails.
- 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.
- Effective
- Effective Mandatory
- Weak
- Weak But Necessary FK
- Needs Maintenance
- Unnecessary
- Do Not Drop
- Validate Before Drop
- Safe Drop Candidate
- Safe Drop Candidate still means review first, not immediate removal.
Index Advisor is a decision-support surface, not an automatic tuner. The module generates evidence-backed recommendations and scripts, but it does not execute maintenance or drop actions by itself.
- Refresh
- Generate Selected Action Script
- Export Visible / Selected Report
- Copy Script
- Analyze, Save LLM JSON, and Save HTML
- Table filter narrows the analysis to a single table.
- Class filter isolates one deterministic classification family.
- Drop Safety filter narrows the review by removal posture.
- Score slider keeps only rows at or above the selected threshold.
- Show Needs Attention Only is enabled by default for faster action-focused review.
- Include Low-Usage Risk Pool changes the next refresh candidate set.
- Select Visible Needs Action is a convenience selector for batch workflows.
- Selecting a row refreshes Script, Metrics, and deterministic AI preview.
- The module also starts loading Query Store history and dependency evidence.
- Right-click actions mirror Copy Script and Analyze with AI shortcuts.
Script Tab for a Selected Index
The Script tab reconstructs a best-effort CREATE INDEX style script for the selected row. This is useful for comparing the current design, copying definition details, or preparing a review before maintenance or consolidation work.
Metrics Tab with Deterministic Evidence
The Metrics tab explains why the selected index received its score and class. It consolidates current stats, score model output, usage, storage, diagnostics, overlap evidence, dependency context, and drop-safety reasoning into one deterministic snapshot.
History Tab with Query Store Trend Data
The History tab summarizes recent Query Store evidence for the selected index. It helps the user validate whether the index is stable, hot, and historically active before deciding on maintenance or removal.
Batch Script Generation for Selected Action Items
Batch script generation turns a selected group of indexes into a reviewable action script. The module can prepare maintenance and statistics commands, while protected or unsafe rows are emitted as comments instead of destructive SQL.
- Shows a reconstructed best-effort CREATE INDEX style script.
- Can include uniqueness, key columns, included columns, filters, and WITH options.
- ONLINE behavior is scripted according to edition or environment capability.
- Explains score model, value, usage, storage, design, and diagnostics.
- Includes overlap analysis, dependency context, and drop-safety evidence.
- Best place to understand why an index got its class and score.
- Selecting an index already fills the tab with a deterministic preview.
- Analyze runs LLM-assisted interpretation for the current index.
- Save LLM JSON and Save HTML become available after usable output exists.
- Shows Query Store trend data for the last 30 days when available.
- Includes reads activity, data points, executions, and recent day-level entries.
- Explains missing-data or Query Store error states when history is unavailable.
In practice, the best companion modules are Query Statistics for query-level evidence and Wait Statistics when you need broader workload correlation before taking index action.
- The selected index is wrapped with deterministic context before LLM interpretation begins.
- The AI output is meant to explain posture, risk, maintenance order, and validation guidance.
- Rendered output can be saved as HTML or audited through the saved LLM request JSON.
- Use Select Visible Needs Action to stage a focused group of candidate indexes.
- Generate Selected Action Script to build a review package for maintenance or validation work.
- Use Export Visible / Selected Report when you need a compact audit or DBA handoff document.
The examples below show how Index Advisor AI output can be shared as standalone HTML reports for review, approval, or change planning.
Example AI review for a foreign-key-supporting index with maintenance-first guidance and evidence framing.
Example AI report for archive-oriented indexing decisions and validation workflow commentary.
Example report focused on dependency-sensitive indexes and cautious change sequencing.
- Analyzed
- Needs Action
- Avg Score
These cards reflect the last full refresh dataset, not only the currently filtered rows.
- Refresh the active database analysis.
- Narrow the table with Table, Class, Drop Safety, and Score.
- Keep Show Needs Attention Only 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.
- 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 sections are only as good as the available workload evidence.
- Include Low-Usage Risk Pool changes the next refresh candidate set and does nothing until refresh is run again.