/sqlindex-advisor

name: sqlindex-advisor
description: Analyze SQL Server execution plans to produce a ranked CREATE INDEX script. Applies 13 checks (D1–D13). Derives index recommendations from operator patterns (Key Lookups, scans, sorts, spools, nested loops, filtered index opportunities, hash match probe-side scans — D1–D10) and the optimizer's explicit MissingIndexGroup suggestions, then validates the generated DDL against engine limits and filtered-index prerequisites (D11–D13). Also accepts sys.dm_db_missing_index_details + sys.dm_db_missing_index_group_stats DMV output directly, without a plan file. Use this skill whenever a user wants index recommendations from an execution plan; asks what indexes would help a query; mentions Key Lookup, index scan, missing index, filtered index, or covering index; or asks to generate CREATE INDEX statements. Trigger after sqlplan-review findings or directly on any .sqlplan file or missing index DMV output.
triggers:
  - /sqlindex-advisor
  - /index-advisor
  - /missing-indexes

SQL Server Index Advisor Skill

Purpose

Produce a prioritized, ready-to-run `CREATE INDEX` script from three independent sources:

1. **Operator-derived recommendations** — index opportunities inferred directly from plan operator patterns (Key Lookups, expensive scans, Sort operators, Eager Index Spools, high-count Nested Loops, residual predicates, heap scans, backward scans, filtered index candidates, hash match probe-side scans) 2. **Optimizer suggestions** — the explicit `<MissingIndexGroup>` elements SQL Server emits, consolidated and de-duplicated 3. **DMV data** — `sys.dm_db_missing_index_details` + `sys.dm_db_missing_index_group_stats` output, which provides server-wide frequency data (`UserSeeks × AvgQueryCost`) unavailable in plan files

All sources feed into a single unified merge and ranking pipeline. The final output contains one CREATE INDEX statement per table group — not one per source.

Input

Accept any of:

• One or more `.sqlplan` file paths
• Raw `.sqlplan` XML pasted inline
• A description of plan operators if XML is not available
• Output from `sys.dm_db_missing_index_details` + `sys.dm_db_missing_index_group_stats` (Source C — no plan file required):

-- Capture server-wide missing index data (run on the target instance)
SELECT
    mig.index_group_handle,
    mig.index_handle,
    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 2) AS weighted_impact,
    mid.statement            AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups  mig
JOIN sys.dm_db_missing_index_details mid
  ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
  ON mig.index_group_handle = migs.group_handle
ORDER BY weighted_impact DESC;

`sys.dm_db_missing_index_groups` exposes only `index_group_handle` and `index_handle` — `unique_compiles` lives on `sys.dm_db_missing_index_group_stats` (`migs`), not on `mig`.

**Permissions:** `VIEW SERVER STATE` on SQL Server 2019 and earlier; `VIEW SERVER PERFORMANCE STATE` on SQL Server 2022 and later.

**600-row cap:** `sys.dm_db_missing_index_details` and `sys.dm_db_missing_index_groups` each return at most 600 rows. On an instance with more missing indexes than that, the capture is silently truncated — address the visible suggestions first, then re-capture to see the rest. Always state this caveat in the report when Source C is used.

When DMV output is provided, treat each row as a Source C candidate and use `weighted_impact` as the ranking score rather than the optimizer's static Impact percentage.

How to Run

1. **Source A — Operator scan:** Walk every `<RelOp>` node and apply the derived rules (D1–D10) below 2. **Source B — Explicit extraction:** Extract all `<MissingIndexGroup>` elements 3. **Source C — DMV parsing:** If DMV output is present, parse each row into a candidate (table, equality cols, inequality cols, include cols, weighted_impact) 4. **Unified merge:** Combine A, B, and C by table, apply merge rules, deduplicate 5. **Rank** the merged set by score — apply D11 per-query attribution when Source C is present on SQL 2019+ 6. **Generate DDL** with width checks applied, gated by D12 (hard engine limits) and D13 (filtered-index SET options)

---

Source A: Operator-Derived Recommendations

Apply these rules to every operator node. Each fired rule produces a candidate recommendation with an estimated impact derived from the operator's `costPercent` or `actualExecutions`.

D1 — Key Lookup / RID Lookup: Extend NC Index

**When:** `physicalOp` = Key Lookup or RID Lookup

**What to extract from the plan:**

• The nonclustered index being seeked (from the parent Nested Loops' seek predicate)
• The seek predicate columns → these become the key columns of the existing NC index
• The output list columns being fetched via the lookup → these become new INCLUDE candidates

**Recommendation:** Extend the seeked NC index with the lookup's output columns as INCLUDE columns. This eliminates the lookup entirely.

<!-- Key Lookup fetches Status and TotalAmount after seeking IX_Orders_CustomerId -->
CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId]
ON [dbo].[Orders] ([CustomerId])
INCLUDE ([Status], [TotalAmount])   -- add these to kill the lookup
WITH (ONLINE = ON, DROP_EXISTING = ON, SORT_IN_TEMPDB = ON);

**Estimated impact:** `min(90, costPercent)` — the lookup's plan share is the impact; no multiplier needed since Key Lookup cost already reflects the round-trip penalty.

**Cross-reference:** sqlplan-review N5

---

D2 — Expensive Scan: Add Seek Index

**When:** `physicalOp` = Index Scan or Table Scan AND `costPercent` ≥ 25% AND a predicate is present on the operator

**What to extract:**

• Table and schema name
• Predicate columns: eq