sqlplan-batch

name	sqlplan-batch
description	Batch-analyze a folder of SQL Server .sqlplan files and produce a summary dashboard of the top issues, most common check violations, and deduplicated missing indexes across all plans. Use this skill whenever a user has a folder or collection of .sqlplan files; asks for a workload-level summary across multiple plans; wants to find systemic patterns across a captured workload; or doesn't know which plan to look at first. Trigger after any workload capture that produced multiple .sqlplan files — offer this before individual sqlplan-review calls.
triggers	["/sqlplan-batch","/plan-batch","/batch-review"]

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:

Field	Source
File name	file system
Query text (first 200 chars, for display only; use full StatementText for analysis)	StmtSimple/@StatementText
Statement cost	StmtSimple/@StatementSubTreeCost
DOP	QueryPlan/@DegreeOfParallelism
Memory grant (MB)	MemoryGrantInfo/@GrantedMemory ÷ 1024
Critical issue count	checks fired at Critical severity
Warning count	checks fired at Warning severity
Spill present	SpillToTempDb/@SpillLevel > 0
Missing index count	<MissingIndexGroup> children count
Missing index max impact	max @Impact across all MissingIndexGroups
Check IDs fired	list of S/N codes

---

Aggregation Structures

1. Top 10 Most Expensive Plans

Rank by StatementSubTreeCost descending. Report:

Rank	File	Cost	DOP	Memory (MB)	Criticals	Warnings

2. Top 10 Plans by Critical Issue Count

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

Rank	File	Criticals	Warnings	Primary Issue

3. Check Violation Frequency

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

Check	Name	Plans Affected	% of Total

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:

File	Operator	Spill Level	Memory Grant (MB)	Memory Used (MB)

5. Plans With Memory Grant > 1 GB

File	Memory Grant (MB)	Grant Used (MB)	Ratio

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]

---



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



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



| 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  attribute from the  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: "]

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

---

---

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:

Check	Evidence	Threshold	Result
[ID — Name]	[key attribute(s) and value found, or "absent"]	[threshold or condition]	PASS / FIRE → [severity] / NOT ASSESSED

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//-/analysis.md ← full report output//-/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.