git:20260528.619534c to git:20260529.c32faaf

---

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 87-check ruleset (S1–S27, N1–N60) 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 `sqlplan-index-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 `sqlplan-index-advisor`:

- Group by table

- Merge overlapping suggestions

- Rank by Impact × occurrence count

- Generate `CREATE INDEX` statements for top 10 (or all, if ≤ 20 total)

---

## Output Format

Write `batch-analysis.md` with the following structure:

```markdown

# Batch Execution Plan Analysis

**Plans analyzed:** N

**Generated:** [timestamp]

**Checks applied:** 80 (S1–S27, N1–N60)

---

## 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 `/sqlplan-index-advisor` on the consolidated missing indexes for a deployment-ready script, or `/sqlplan-review` on any specific high-cost plan for detailed analysis.

---

### 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/<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 99-check ruleset to the highest-cost or most-critical plan.

- **sqlplan-index-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.

- **sqlplan-deadlock** — 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.

- **query-store-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.