sqlplan-index-advisorlisted

# SQL Server Index Advisor Skill ## Purpose Produce a prioritized, ready-to-run `CREATE INDEX` script from two 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) 2. **Optimizer suggestions** — the explicit `<MissingIndexGroup>` elements SQL Server emits, consolidated and de-duplicated Both 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 ## How to Run 1. **Source A — Operator scan:** Walk every `<RelOp>` node and apply the derived rules (D1–D8) below 2. **Source B — Explicit extraction:** Extract all `<MissingIndexGroup>` elements 3. **Unified merge:** Combine A and B by table, apply merge rules, deduplicate 4. **Rank** the merged set by score 5. **Generate DDL** with width checks applied --- ## 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