SQL Server Execution Plan Batch Analysis Skill

Purpose

Analyze multiple .sqlplan files in bulk — applying the full 108-check ruleset (S1–S36, N1–N72) from sqlplan-review to each plan — and produce a single aggregated dashboard that identifies the most expensive queries, most common violations, and consolidated missing index recommendations.

Input

Accept any of:

• A directory path containing .sqlplan files: /path/to/plans/
• A list of .sqlplan file paths
• A description of the available plans if files cannot be provided

How to Run

1. Enumerate all .sqlplan files in the input
2. Apply the full check ruleset to each plan (same logic as sqlplan-review)
3. Aggregate findings into the summary structures below
4. Generate a consolidated missing index script via the same merge rules as sqlindex-advisor
5. Write output to batch-analysis.md in the same directory

Per-Plan Data to Collect

For each plan, collect:

Aggregation Structures

1. Top 10 Most Expensive Plans

Rank by StatementSubTreeCost descending. Report:

2. Top 10 Plans by Critical Issue Count

Rank by Critical issue count descending, break ties by cost.

3. Check Violation Frequency

Count how many plans triggered each check ID. Report top 15 most common violations:

Example output:

N21 (Bad Row Estimate)          — 31 / 50 plans (62%)
S1  (Serial Plan)               — 28 / 50 plans (56%)
N4  (Expensive Scan)            — 25 / 50 plans (50%)

4. Spill Summary

List all plans with confirmed spills:

5. Plans With Memory Grant > 1 GB

6. Consolidated Missing Index Report

Apply the same merge rules as sqlindex-advisor:

• Group by table
• Merge overlapping suggestions
• Rank by Impact × occurrence count
• Generate CREATE INDEX statements for top 10 (or all, if ≤ 20 total)

Version-Aware Check Suppression

If the SQL Server version is known — from the ServerVersion attribute in the plan XML or stated by the user — read VERSION_COMPATIBILITY.md (~/.claude/skills/VERSION_COMPATIBILITY.md if installed, or skills/VERSION_COMPATIBILITY.md from the repo). If unavailable, skip silently. For checks whose minimum version exceeds the instance version: verbose mode → log as SKIP (version: requires SQL 20XX+, instance is SQL 20YY); standard report → omit entirely. Do not suppress NOT ASSESSED rows from missing input — only suppress version-inapplicable checks.

Output Format

Write batch-analysis.md with the following structure:

# Batch Execution Plan Analysis
**Plans analyzed:** N  
**Generated:** [timestamp]  
**Checks applied:** 108 (S1–S36, N1–N72)

---

## Executive Summary

- Total Critical issues: X across Y plans
- Total Warnings: A across B plans
- Plans with confirmed spills: C
- Plans with memory grant > 1 GB: D
- Unique tables with missing index suggestions: E

**Systemic issues (> 30% of plans):**
- [Check ID]: [name] — N plans (X%)
- ...

---

## Top 10 Most Expensive Plans

| Rank | File | Cost | DOP | Memory (MB) | Criticals | Warnings |
|------|------|------|-----|-------------|-----------|---------|
| 1 | ... | | | | | |

---

## Top 10 Plans by Critical Issues

[table]

---

## Check Violation Frequency (Top 15)

[table]

---

## Spill Report

| File | Operator | Spill Level | Threads Spilled | Est. Rows | Actual Rows | Note |
|------|---------|------------|----------------|-----------|-------------|------|
| plan.sqlplan | Sort (Node N) | 2 | 8 | 1 | 9,999,999 | [root cause in one phrase] |

[Or: "No spills detected across all plans."]

---

## Memory Grant Summary

| File | Granted MB | Max Used MB | Efficiency | Wait ms |
|------|-----------|-------------|------------|---------|
| plan.sqlplan | 1,024 | 2,048 | 200% overused (grant too small) | 5,000 |

[Efficiency = MaxUsed / Granted × 100. Flags both over-grants (< 10% used) and under-grants (> 100% used). Omit if no plan has a memory grant.]

---

## Cardinality Accuracy Report

| File | NodeId | Operator | Estimated | Actual | Error Factor |
|------|--------|---------|-----------|--------|-------------|
| plan.sqlplan | 5 | Sort | 1 | 9,999,999 | **9,999,999×** |

[Include only operators where actual vs estimated diverges > 100×. Sort by Error Factor descending. This table reveals which plans need statistics work before anything else.]

---

## Consolidated Missing Index Script

### Summary
- Raw suggestions across all plans: N
- After merging: M
- Tables affected: K

### Recommended Indexes

[CREATE INDEX statements in ranked order]

---

## Per-Plan Summary

| File | Cost | DOP | Memory (MB) | Criticals | Warnings | Spill | Check IDs |
|------|------|-----|-------------|-----------|---------|-------|-----------|
| ... | | | | | | | |

## Per-Plan Findings Summary

For each plan with at least one Critical or Warning finding, add a sub-section:

### `plan-name.sqlplan`

| ID | Severity | NodeId | Finding |
|----|----------|--------|---------|
| S3 | Critical | — | Memory grant 1,024 MB — over-budget |
| N21 | Warning | 7 | Row estimate 1 vs actual 9,999,999 |

NodeId column: populate for operator-level findings (N-prefix check IDs) using the `NodeId` attribute from the `<RelOp>` element. Use `—` for statement-level findings (S-prefix) that have no associated operator.

[One sentence at the bottom pointing to the full analysis: "Full analysis: `/sqlplan-review plan-name.sqlplan`"]

[Plans with no findings beyond Info: one line — "Clean plan — no Critical or Warning findings."]

---
*Analyzed by: [state the AI model and version you are running as, e.g. "Claude Sonnet 4.6", "DeepSeek R1", "GPT-4o"] · [current date and time in the user's local timezone, or UTC if timezone is unknown, e.g. "2026-05-16 20:15 NZST"]*

Notes

• Apply the checks from sqlplan-review (the parent skill) — do not re-define them here. This skill is an aggregation layer, not a separate ruleset.
• If a plan file is malformed or cannot be parsed, log it in a "Skipped Plans" section and continue.
• For very large directories (> 100 plans), report only the top findings to keep the output actionable. Note the total plan count and that full per-plan data is in the Per-Plan Summary table.
• The batch-analysis.md output file should be placed in the same directory as the input plans (or a specified output path) so it stays with the workload capture.
• After generating the batch report, offer to run /sqlindex-advisor on the consolidated missing indexes for a deployment-ready script, or /sqlplan-review on any specific high-cost plan for detailed analysis.

Section: Output Filters (--brief / --critical-only)

`--brief` — Omit the Passed Checks table and attribution footer. Output the Summary, Findings, and Prioritized Fix Sequence sections only. Use when a quick scan of what fired is all that's needed.

`--critical-only` — Suppress Warning and Info findings. Show only Critical findings. The Passed Checks table is also omitted. Use when triaging an incident and only actionable blockers matter.

Both flags can be combined: --brief --critical-only produces the Summary section plus Critical findings only.

When neither flag is present, produce the full report as documented above.

Section: Verbose Output (--verbose)

When the user's request includes --verbose, --trace, or the word verbose:

1. Append a `## Check Evaluation Log` section after the Passed Checks table.

Include one row for every check in this skill's ruleset, in check-ID order:

Result conventions:

• PASS — attribute present, threshold not met
• **FIRE → Critical/Warning/Info** — threshold met; bold to distinguish from passes
• NOT ASSESSED — required attribute absent from input

2. Save both files to the current working directory using the Write tool:

output/<skill-name>/<YYYY-MM-DD-HHmmss>-<input-prefix>/analysis.md ← full report output/<skill-name>/<YYYY-MM-DD-HHmmss>-<input-prefix>/trace.md ← Check Evaluation Log

Derive <input-prefix>:

1. Filename stem if a file path was provided (e.g. horrible.sqlplan → horrible)
2. First meaningful identifier from the artifact (top wait type, first table name, procedure name, etc.)
3. Fallback: run

Sanitize: alphanumeric + hyphens/underscores only, max 32 chars.

File headers: analysis.md → # Analysis — <skill-name> / # Input: <first 80 chars> / # Generated: <UTC timestamp> trace.md → # Check Evaluation Log — <skill-name> / # Input: <first 80 chars> / # Generated: <UTC timestamp>

Create directories as needed. When --verbose is not present, write nothing to disk.

Companion Skills

• sqlplan-review — Deep-dive analysis on any individual plan from the batch. Apply the full 108-check ruleset (S1–S36, N1–N72) to the highest-cost or most-critical plan.
• sqlindex-advisor — Generate a deployment-ready CREATE INDEX script from the consolidated missing index recommendations in the batch report.
• sqlplan-compare — Diff the worst-performing plan against a known-good baseline to explain why a specific query regressed.
• sqldeadlock-review — If deadlock graphs were captured alongside the .sqlplan files, analyze them with this companion skill.
• sqltrace-review — If a Profiler or Extended Events trace was captured from the same workload, cross-reference trace findings with batch plan findings.
• sqlquerystore-review — Analyze Query Store data to find regressed queries, plan instability, and the top resource consumers across the whole workload. Use after running a workload capture to prioritize which queries to tune with /sqlplan-review.

• mssql-performance-review — Orchestrator that routes mixed artifacts to multiple specialised skills (this one included), runs an adversarial root-cause check, and produces a single consolidated report with evidence chain, risk-rated fixes, and rollback. Use when you have several artifact types together or describe a symptom without knowing which skill to run.