sqlplan-compare

name	sqlplan-compare
description	Diff two SQL Server execution plans (baseline vs regression) to identify what changed — join strategies, memory grants, operator topology, new warnings, and missing indexes. Applies 20 checks (C1–C20). Use when a query regressed after a deployment, statistics update, schema change, or SQL Server version upgrade.
triggers	["/sqlplan-compare","/plan-compare","/plan-diff"]

SQL Server Execution Plan Comparison Skill

Purpose

Identify what changed between two execution plans for the same query — one known-good (baseline) and one regressed (new). Produce a side-by-side diff that explains why the query is slower and what to fix. Applies 20 regression checks (C1–C20).

Input

Accept any of:

• Two .sqlplan file paths: baseline.sqlplan and new.sqlplan
• Two blocks of raw .sqlplan XML pasted inline, labeled Baseline and New
• A description of both plans if XML is not available

How to Run

1. Parse both plans independently
2. Extract the comparison metrics listed below for each plan
3. Produce a side-by-side diff table, then a findings section for every significant change
4. Conclude with a prioritized fix list

---

Metrics to Compare

Statement-Level

Metric	Where to Find	Signal
StatementSubTreeCost	StmtSimple/@StatementSubTreeCost	> 2× increase = regression
DegreeOfParallelism	QueryPlan/@DegreeOfParallelism	DOP drop = serial plan forced
GrantedMemory (KB)	MemoryGrantInfo/@GrantedMemory	> 2× increase = cardinality inflation
MaxUsedMemory (KB)	MemoryGrantInfo/@MaxUsedMemory	Used > Granted = spill
CardinalityEstimationModelVersion	QueryPlan/@CardinalityEstimationModelVersion	Version drop = compat level change
CompileCPU (ms)	StmtSimple/@CompileCPU	> 2× increase = optimizer struggling
MissingIndexGroup count	<MissingIndexes> children	New suggestions = indexes dropped

Operator Topology

Compare these for each plan:

• Join count by type — Hash Match, Merge Join, Nested Loops (report count of each)
• Join type changes — Identify any operator that changed type between plans (e.g., Hash → Nested Loops is a regression signal when the table is large)
• New operators — Operators present in the new plan but not the baseline (e.g., Sort, Spool, Key Lookup appearing)
• Removed operators — Operators in baseline but not new (e.g., Seek replaced by Scan)
• Scan vs Seek changes — Any table that changed from Seek to Scan is critical

Warning Changes

• New <Warnings> elements in the new plan not present in baseline
• New SpillToTempDb entries
• New PlanAffectingConvert entries
• New NoJoinPredicate flags

---

Comparison Checks (C1–C20)

C1 — Seek Degraded to Scan

• Trigger: A table that had a Seek operator in the baseline now has a Scan in the new plan
• Severity: Critical
• Report: Table name, old operator (Seek), new operator (Scan), estimated cost ratio
• Likely causes: Index dropped, statistics changed causing optimizer to choose full scan, implicit conversion added

C2 — Hash Join Degraded to Nested Loops on Large Table

• Trigger: A join changed from Hash Match to Nested Loops AND actualRows on the probe side > 10,000
• Severity: Critical
• Report: Join operator location, old type, new type, row counts
• Likely causes: Bad cardinality estimate making the inner side appear small; parameter sniffing

C3 — Memory Grant Inflated > 2×

• Trigger: New plan GrantedMemory > baseline GrantedMemory × 2
• Severity: Warning
• Report: Baseline grant, new grant, ratio
• Likely causes: Row estimate inflation (stale statistics, parameter sniffing)

C4 — Memory Grant Deflated > 2× (Spill Risk)

• Trigger: New plan GrantedMemory < baseline GrantedMemory / 2 AND MaxUsedMemory > GrantedMemory in new plan
• Severity: Warning
• Report: Baseline grant, new grant, used memory in new plan
• Likely causes: Row estimate collapse; optimizer now thinks fewer rows are involved

C5 — Parallelism Lost

• Trigger: Baseline DegreeOfParallelism > 1 AND new plan DegreeOfParallelism = 1
• Severity: Warning
• Report: Old DOP, new DOP, NonParallelPlanReason if present
• Likely causes: MAXDOP hint added, scalar UDF introduced, table variable used in new code path

C6 — New Spill to TempDb

• Trigger: SpillToTempDb present in new plan but not in baseline
• Severity: Critical
• Report: Operator that spills, spill level, estimated vs actual rows at that operator

C7 — New Key Lookup Introduced

• Trigger: Key Lookup or RID Lookup operator present in new plan but not in baseline
• Severity: Warning
• Report: Table name, estimated rows, costPercent

C8 — New Missing Index (High Impact)

• Trigger: A MissingIndexGroup in the new plan is not present in the baseline AND Impact > 50
• Severity: Warning
• Report: Missing index details, impact score, columns

C9 — Sort Operator Added

• Trigger: Sort operator present in new plan but not in baseline AND costPercent ≥ 10%
• Severity: Warning
• Report: Sort columns, cost percent, estimated rows

C10 — Cardinality Model Downgraded

• Trigger: CardinalityEstimationModelVersion in new plan < baseline
• Severity: Warning
• Report: Old version, new version
• Likely causes: Database compatibility level was lowered, or plan was compiled under a different database context

C11 — Adaptive Join Threshold Changed

• Trigger: AdaptiveThresholdRows attribute on an Adaptive Join operator differs between plans — SQL 2017+
• Severity: Warning
• Report: Node ID, baseline threshold rows, new threshold rows, join type chosen in each plan
• Likely causes: Cardinality estimate for the build side changed (statistics update, parameter sniffing); the threshold is set at compile time from the optimizer's row count estimate

C12 — Batch Mode Lost

• Trigger: Baseline has operators with executionMode="Batch"; new plan has only executionMode="Row" — SQL 2017+ (Columnstore), SQL 2019+ (Rowstore)
• Severity: Warning
• Report: Count of batch-mode operators in baseline vs new plan; first operator that lost batch mode
• Likely causes: Columnstore index dropped; DISABLE_BATCH_MODE_ON_ROWSTORE hint added; compat level dropped below 150; scalar UDF or incompatible operator introduced

C13 — New Implicit Conversion Warning

• Trigger: PlanAffectingConvert element present in new plan but absent from baseline
• Severity: Warning
• Report: Column and expression affected; from/to data types; whether seeks are impacted
• Likely causes: Parameter or variable type changed; column altered to a different type; a new function call wraps a column making the predicate non-sargable

C14 — Estimated vs Actual Row Divergence Worsened

• Trigger: Maximum actualRows / estimateRows ratio across all operators (with actualRows > 100) increased by > 10× between plans — requires actual execution plans
• Severity: Warning
• Report: Operator with highest ratio in each plan; node ID; estimated vs actual rows; ratio
• Likely causes: Statistics quality degraded; parameter sniffing changed compiled estimates; a predicate was added or removed that shifted cardinality

C15 — Compile CPU Regression

• Trigger: CompileCPU in new plan > baseline CompileCPU × 3
• Severity: Info
• Report: Baseline compile CPU (ms), new compile CPU (ms), ratio
• Likely causes: Schema became more complex (more joins, views resolved); optimizer timeout extended; query gained additional joins or subqueries

C16 — Plan Guide or Forced Plan Introduced

• Trigger: PlanGuideName attribute present in new plan but absent from baseline
• Severity: Warning
• Report: Plan guide name; type (SQL, OBJECT, TEMPLATE); operator shape it forced
• Likely causes: A DBA applied a plan guide or Query Store forcing after the regression — the forced plan may itself be suboptimal

C17 — New Eager Index Spool

• Trigger: Eager Index Spool operator present in new plan but absent from baseline
• Severity: Critical
• Report: Node ID, estimated rows, cost percent
• Likely causes: A permanent index was dropped; the optimizer is now building a temporary runtime index to compensate — this is expensive and signals a missing permanent index

C18 — Partition Elimination Lost

• Trigger: New plan accesses more partitions than baseline for the same partitioned table with an identical filter predicate — SQL 2005+ (partitioning)
• Severity: Warning
• Report: Table name, partition count baseline vs new plan
• Likely causes: Data type or collation change on the partition key column broke elimination; parameter type changed making the predicate non-sargable against the partition function

C19 — Parameter Sensitive Plan Dispatcher Added

• Trigger: ParameterSensitivePredicate dispatcher node present in new plan but absent from baseline — SQL 2022+ only
• Severity: Info
• Report: PSP predicate column, threshold rows, number of variants
• Likely causes: SQL 2022 PSP optimization activated after a data-skew threshold was met; generally beneficial but variant boundaries should be verified against actual data distribution

C20 — New Cross-Database or Linked Server Access

• Trigger: New plan references a four-part name (server.db.schema.table) or a linked server operator absent from baseline
• Severity: Warning
• Report: Remote server or database name; operator type; estimated rows
• Likely causes: A view was modified to reference a linked server; a stored procedure was updated to query a different database; query was rewritten to join across database boundaries

---

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

## Execution Plan Comparison

### Summary Table

| Metric | Baseline | New Plan | Change |
|--------|----------|----------|--------|
| Statement Cost | X | Y | +Z% |
| DOP | X | Y | ↓ or ↑ |
| Memory Grant (MB) | X | Y | +Z% |
| Join Types (Hash/NL/Merge) | X/Y/Z | A/B/C | — |
| Spills | None | 2 (Sort, Hash) | ⚠ New |
| Missing Indexes | N | M | +K |

### Regression Findings

**[R1 — C2, NodeId 8→12] Finding Name**
- **Was:** [baseline operator/value]
- **Now:** [new plan operator/value]
- **Why:** [root cause — what changed and why it caused this shift]
- **Fix:** [concrete action with code if applicable]

The bracket suffix (`— C2`, `— C5`) is the check ID from the C1–C10 checks above that fired. Include the NodeId from both plans for each changed operator (e.g., `NodeId 8→12`). If NodeIds are absent, use operator name + table name instead.
Findings reference each other where one is the root cause of another (e.g., "consequence of R1").
Do not use Critical/Warning severity tiers — regression findings are ranked by fix priority, not severity.

### Root Cause Summary

[One paragraph synthesising all findings into a single root cause statement.
Example: "A single change caused the entire regression: the @status parameter type changed from
VARCHAR to NVARCHAR, introducing an implicit conversion on the Status column. This made the
index non-sargable → cardinality collapsed → Hash Match replaced Nested Loops → 2 GB memory
grant → 3.2-second wait before execution begins."]

```sql
-- Recommended fix (step-by-step code block)
-- Step 1: ...
-- Step 2: ...

Confirmed Stable (Unchanged)

[List key operators, DOP, CE version, compile time that are the same in both plans. This gives confidence the comparison is valid.]

---

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

- If plans are from different queries, note this and refuse to compare — the diff is meaningless across different query shapes.
- If the baseline is estimated-only and the new plan is actual, note the comparison limitation for runtime-dependent metrics.
- When the root cause is parameter sniffing, recommend capturing the plan at the specific parameter value that causes the regression.

---

### 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/<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** — Run the full 108-check analysis on each plan individually before comparing. Findings from sqlplan-review provide context for why the regression occurred.
- **sqlindex-advisor** — If the regression introduced a new Key Lookup or expensive scan, use this skill to generate the covering index that would resolve it.
- **sqltrace-review** — If a workload trace showed the query regressing in production, cross-reference trace duration variance (X14) with the plan diff.
- **tsql-review** — If the regression was triggered by a schema or code change, review the T-SQL source for the anti-pattern that caused the plan change.
- **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.