# Sqlplan Compare

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

- Skill: `vanterx/sqlplan-compare` (Agent Skill, multi-file: 9 files)
- Install (CLI): `npx skillmds@latest add vanterx/sqlplan-compare`
- Raw SKILL.md: https://api.skillmd.com/api/skills/vanterx/sqlplan-compare/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: vanterx (https://skillmd.com/u/vanterx)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/vanterx/sqlplan-compare

---


# 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.