/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 |
|---