# Sqlplan Review

> Analyze SQL Server execution plans for performance anti-patterns, bottleneck identification, and actionable fix recommendations. Applies 108 checks (S1–S36 statement-level, N1–N72 node-level) covering memory grants, parallelism, cardinality errors, spills, scans, index usage, IQP/PSP features, ADR, and CE feedback. Use this skill whenever a user pastes a .sqlplan file or XML, shares an SSMS execu…

- **Type:** Skill
- **Install:** `agentstack add skill-vanterx-mssql-performance-skills-sqlplan-review`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [vanterx](https://agentstack.voostack.com/s/vanterx)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [vanterx](https://github.com/vanterx)
- **Source:** https://github.com/vanterx/mssql-performance-skills/tree/main/skills/sqlplan-review

## Install

```sh
agentstack add skill-vanterx-mssql-performance-skills-sqlplan-review
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# SQL Server Execution Plan Review Skill

## Purpose

Analyze a SQL Server execution plan for performance anti-patterns and produce a prioritized, actionable report. Based on the same analysis ruleset used by commercial SQL Server execution plan tools. Covers 108 checks across statement-level (S1–S36) and node-level (N1–N72) categories.

## Input

Accept any of:
- Raw `.sqlplan` XML (paste or file contents)
- A description of the plan tree (operator names, row counts, costs)
- A question like "why is this query slow?" with plan details included

If the user provides XML, extract the relevant attributes yourself before running checks. If the input is a description, apply the checks based on what is mentioned.

## How to Run

A `.sqlplan` XML contains one or more `` elements (a single query, or many in a stored procedure).

**For each `` in the XML:**
1. Record the `StatementId` and a short excerpt from `StatementText` for the overview table label (use the full `StatementText` for all checks — never truncate during analysis)
2. Run all 36 statement-level checks (S1–S36) against this statement's attributes
3. Walk every `` node in this statement's plan tree recursively, applying all 72 node-level checks (N1–N72)
4. Label every finding with the statement source

**Single-statement plans** (one ``): the `StatementId` prefix may be omitted for brevity.
**Multi-statement plans** (> 1 ``): every finding carries a `StatementId` label. See the multi-statement section in Output Format below.

Report every triggered finding — do not stop at the first match per statement. Walk all statements completely.

---

## Thresholds Reference

| Metric | Value |
|--------|-------|
| Expensive operator | costPercent ≥ 25% |
| High-cost operator | costPercent ≥ 50% |
| Memory grant info | granted ≥ 512 MB |
| Large memory grant | granted ≥ 1,024 MB |
| Excessive memory grant | granted / used ≥ 10× AND granted ≥ 1 GB |
| Memory grant critical | ≥ 4,096 MB |
| Grant wait warning | > 0 ms |
| Grant wait critical | ≥ 5,000 ms |
| High compile CPU warning | ≥ 1,000 ms |
| High compile CPU critical | ≥ 5,000 ms |
| Downlevel CE | CardinalityEstimationModelVersion  100× |
| Key lookup concern | actualRows > 1,000 OR actualExecutions > 1,000 |
| Sort spill risk | actualRows > estimateRows × 10 |
| Hash spill risk | probeRows > buildRows × 100 |
| High loop count (warning) | actualExecutions > 10,000 |
| High loop count (info) | actualExecutions > 1,000 with high inner cost |
| Bad row estimate (warning) | actual vs estimated > 1,000× in either direction |
| Bad row estimate (info) | actual vs estimated > 100× in either direction |
| Expensive sort | (estimateIO + estimateCPU) ≥ 50% of subtree cost |
| Busy loops | (rebinds + rewinds + 1) > estimateRows × 100 |
| Parallel efficiency low |  20 discrete seek ranges |
| Missing indexes excessive | > 5 MissingIndexGroup children in plan |
| Excessive parameters | > 50 ColumnReference children in ParameterList |
| Window frame large | RANGE UNBOUNDED PRECEDING with actualRows > 100,000 |
| Cached plan size (info) | CachedPlanSize ≥ 1,024 KB |
| Cached plan size (warning) | CachedPlanSize ≥ 5,120 KB |
| Memory request denied (warning) | RequestedMemory > GrantedMemory × 1.1 |
| Serial required memory (info) | SerialRequiredMemory ≥ 524,288 KB (512 MB) |
| Compile wait (info) | CompileTime > CompileCPU × 2 AND CompileTime > 1,000 ms |
| Wide row (warning) | AvgRowSize > 8,192 bytes |
| Wide row (critical) | AvgRowSize > 32,768 bytes |
| Wide output list (info) | OutputList ColumnReference count > 20 |
| Elapsed time hotspot | ActualElapsedms sum for operator > 1,000 ms AND > 50% of statement elapsed |
| Thread starvation | any RunTimeCountersPerThread ActualRows = 0 while total > 0 |
| Partition elimination failure | ActualPartitionsAccessed = PartitionCount with predicate present |
| Actual rebind excess | ActualRebinds > EstimateRebinds × 10 AND ActualRebinds > 1,000 |

---

## Statement-Level Checks (S1–S36)

Run these once per `` element before inspecting individual operators.
### S1 — Serial Plan
- **Trigger:** `NonParallelPlanReason` attribute is present AND `StatementSubTreeCost` ≥ 1.0 AND `StatementOptmLevel` ≠ TRIVIAL
- **Severity:** Warning if reason is actionable (see below), Info otherwise
- **Actionable reasons:** MaxDOPSetToOne, QueryHintNoParallelSet, ParallelismDisabledByTraceFlag, CouldNotGenerateValidParallelPlan, TSQLUserDefinedFunctionsNotParallelizable, TableVariableTransactionsDoNotSupportParallelNestedTransaction
- **Fix:** Remove MAXDOP 1 hint, rewrite scalar UDFs as inline TVFs, replace table variables with temp tables, check server MAXDOP setting
### S2 — Excessive Memory Grant
- **Trigger:** `GrantedMemory` / `MaxUsedMemory` ≥ 10× AND `GrantedMemory` ≥ 1,048,576 KB
- **Severity:** Warning
- **Fix:** Add `OPTION (OPTIMIZE FOR (@param = value))`, update statistics, use `OPTION (RECOMPILE)` to get a per-execution grant
### S3 — Large Memory Grant
- **Trigger:** `GrantedMemory` ≥ 524,288 KB (512 MB) for Info; ≥ 1,048,576 KB (1 GB) for Warning; ≥ 4,194,304 KB (4 GB) for Critical
- **Severity:** Info (≥ 512 MB); Warning (≥ 1 GB); Critical (≥ 4 GB)
- **Fix:** Reduce sort/hash operations, filter earlier in the plan, check for stale statistics causing row overestimates. The 512 MB Info tier surfaces plans that are large but not yet alarming — worth noting before they grow.
### S4 — Memory Grant Wait
- **Trigger:** `GrantWaitTime` > 0
- **Severity:** Warning; Critical if `GrantWaitTime` ≥ 5,000 ms
- **Fix:** Reduce memory grant size (see S2/S3), add Resource Governor pool, or increase `max server memory`
### S5 — Compile Timeout
- **Trigger:** `StatementOptmEarlyAbortReason` = TimeOut
- **Severity:** Critical
- **Fix:** Break the query into smaller pieces, use query hints to guide the optimizer, eliminate unnecessary joins or subqueries, consider a stored procedure with forced plan
### S6 — Compile Memory Exceeded
- **Trigger:** `StatementOptmEarlyAbortReason` = MemoryLimitExceeded
- **Severity:** Critical
- **Fix:** Simplify the query, reduce the number of tables/joins, split into multiple queries
### S7 — High Compile CPU
- **Trigger:** `CompileCPU` ≥ 1,000 ms
- **Severity:** Warning if  1 AND `elapsedTimeMs` ≥ 1,000 AND parallel efficiency  `cpuTimeMs` × 2 (threads spending more time waiting than working)
- **Severity:** Warning
- **Fix:** Look for repartition streams, gather streams operators; check for blocking, lock waits, or I/O contention
### S10 — Downlevel Cardinality Estimator
- **Trigger:** `CardinalityEstimationModelVersion` > 0 AND ` element exists under ``
- **Severity:** Warning
- **Fix:** Inspect the specific warning type. Common types: SpillToTempDb, NoJoinPredicate, PlanAffectingConvert
### S12 — Implicit Conversion Affects Seek
- **Trigger:** `` present in Warnings
- **Severity:** Critical
- **Fix:** Match the data type of the parameter/literal to the column type. Common mismatch: VARCHAR column with NVARCHAR parameter, or INT column with VARCHAR literal.
### S13 — Table Variable (Read)
- **Trigger:** Any node has `objectName` starting with `@` and statement is not a modification
- **Severity:** Warning
- **Fix:** Replace with a temporary table (`#temp`) so statistics are available, especially when the table variable holds > ~100 rows
### S14 — Table Variable (Write / Modification)
- **Trigger:** Any node has `objectName` starting with `@` and a write operator (Insert/Update/Delete) targets it
- **Severity:** Critical
- **Fix:** Replace with a temp table. Writing to a table variable forces single-threaded execution regardless of DOP.
### S15 — High Compile Memory
- **Trigger:** `CompileMemory` ≥ 1,048,576 KB (1 GB) on `StmtSimple`
- **Severity:** Warning
- **Fix:** The optimizer consumed over 1 GB of memory just to compile this query. Simplify joins and subqueries. Use stored procedures to promote plan reuse and avoid repeated expensive compilations.
### S16 — Trivial Plan
- **Trigger:** `StatementOptmLevel` = TRIVIAL AND `StatementSubTreeCost` ≥ 1.0
- **Severity:** Info
- **Fix:** SQL Server bypassed full optimization and used a trivial plan. Usually benign, but if performance is poor, check for missing indexes or consider forcing full optimization with a query hint.
### S17 — Unparameterized Query
- **Trigger:** No `` element present on `StmtSimple` AND `StatementType` = SELECT/INSERT/UPDATE/DELETE (not stored procedure)
- **Severity:** Info
- **Fix:** The query has no parameters — it may be an ad-hoc query with literal values baked in. Each unique set of literals produces a new plan cache entry. Use parameterized queries or `sp_executesql` to improve plan reuse and reduce plan cache bloat.
### S18 — Insufficient Memory Grant (Used > Granted)
- **Trigger:** `MemoryGrantInfo/@MaxUsedMemory` > `MemoryGrantInfo/@GrantedMemory` (query used more memory than it was granted)
- **Severity:** Warning — always Warning regardless of the magnitude of under-allocation. The confirmed spills caused by this under-grant are caught as Critical via N41/N38; do not escalate S18 itself.
- **Fix:** The memory grant was undersized because the optimizer underestimated row counts at compile time. This causes the query to spill to tempdb. Fix root-cause cardinality errors (parameter sniffing, stale statistics). Unlike S2/S3 which flag over-allocation, this flags the opposite — the grant was too small.
### S19 — FORCE ORDER Hint
- **Trigger:** `StatementText` matches `/OPTION\s*\([^)]*FORCE\s*ORDER/i`
- **Severity:** Warning
- **Fix:** FORCE ORDER freezes the join order from the query text, overriding the optimizer's cost-based join reordering. Becomes incorrect as data distribution changes. Remove the hint and fix the root cause (missing statistics, missing indexes) so the optimizer can choose the correct order itself.
### S20 — RECOMPILE Hint with Expensive Compile
- **Trigger:** `StatementText` contains `OPTION (RECOMPILE)` AND `CompileCPU` ≥ 500 ms; Critical if `CompileCPU` ≥ 2,000 ms
- **Severity:** Warning / Critical
- **Fix:** OPTION (RECOMPILE) discards the plan after every execution. At high compile CPU, every execution pays a heavy compilation tax. Use `OPTIMIZE FOR` or `OPTION (OPTIMIZE FOR UNKNOWN)` instead. If parameter sniffing is the root cause, address it with filtered statistics or local variable sniffing-prevention.
### S21 — Recursive CTE Without Max Recursion
- **Trigger:** `StatementText` contains `WITH ... AS` and a self-referencing CTE name AND no `OPTION (MAXRECURSION N)` is present
- **Severity:** Warning
- **Fix:** Add `OPTION (MAXRECURSION N)` to avoid runaway recursion on bad data. The default limit is 100; an explicit limit documents intent and prevents accidental infinite loops when hierarchy data has cycles.
### S22 — SET ROWCOUNT Active
- **Trigger:** `RowCountAssignment` attribute > 0 on `StmtSimple` [Unverified — attribute not found in documented showplan references; also detect `SET ROWCOUNT` in the batch text]
- **Severity:** Warning
- **Fix:** `SET ROWCOUNT` is deprecated, silently changes plan shapes, and can truncate results without warning. The optimizer builds the plan assuming the full result set will be returned; `SET ROWCOUNT` truncates silently at execution. Sort operators are sized for all rows, indexes are chosen for full-scan patterns, and row goals are not applied. Replace with `TOP (N)` — `TOP` is a compile-time directive the optimizer can see, enabling row goals, seek strategies, and right-sized memory grants for N rows rather than all rows.
### S23 — Excessive Parameter Count
- **Trigger:** `` has > 50 `` children
- **Severity:** Info
- **Fix:** Very high parameter counts inflate plan cache entry size and compile time. Consider batching via table-valued parameters (`CREATE TYPE ... AS TABLE`) or splitting into smaller parameterized queries.
### S24 — Query Store Forced Plan Active
- **Trigger:** `PlanGuideName` attribute starts with `QDS_` on `StmtSimple`
- **Severity:** Warning
- **Fix:** A Query Store forced plan is overriding normal optimization. QDS-forced plans bypass the optimizer and become stale as data changes. Validate the forced plan is still beneficial and that the underlying regression (bad statistics, missing index) has been resolved. If fixed, unforce via `sys.sp_query_store_unforce_plan`.
### S25 — Interleaved Execution (MSTVF) Active
- **Trigger:** `ContainsInterleavedExecutionCandidates = true` on the `QueryPlan` node (per-operator `IsInterleavedExecuted` appears on `RuntimeInformation`) — SQL 2017+
- **Severity:** Info
- **Fix:** SQL Server is using interleaved execution to feed actual row counts from multi-statement TVFs back into optimization. This is beneficial. Verify it has not been suppressed via `OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'))`, which would revert to the static 1-row estimate.
### S26 — Batch Mode Adaptive Join Active
- **Trigger:** Any operator has `IsAdaptive = 1` AND `executionMode = Batch` — SQL 2017+ (compat level 140+)
- **Severity:** Info
- **Fix:** SQL Server is deferring the join strategy (Hash vs Nested Loops) to runtime. This is generally good. Flag only if the `AdaptiveThresholdRows` does not match actual row distribution, indicating the threshold was calibrated on a non-representative execution.
### S27 — Excessive Missing Index Suggestions
- **Trigger:** `` element contains > 5 `` children
- **Severity:** Warning
- **Fix:** More than 5 distinct missing index suggestions indicate the query touches many under-indexed tables. Prioritize by the `Impact` attribute descending (not document order). Use the `sqlindex-advisor` skill to consolidate and de-duplicate suggestions before creating indexes.
### S28 — Large Cached Plan (Plan Cache Bloat)
- **Trigger:** `CachedPlanSize` attribute on `` ≥ 1,024 KB
- **Severity:** Info if  `GrantedMemory` × 1.1 in `MemoryGrantInfo` (the optimizer requested more memory than the server could grant)
- **Severity:** Warning
- **Fix:** The server was under memory pressure at execution time and reduced the grant below what was requested. This is distinct from S4 (grant wait, which measures delay) — this shows the request was cut. Sort and hash operators will spill to TempDb even when statistics are accurate. Increase `max server memory`, add Resource Governor, or reduce concurrent memory demand from other queries.
### S30 — High Serial Required Memory
- **Trigger:** `SerialRequiredMemory` ≥ 524,288 KB (512 MB) in `MemoryGrantInfo`
- **Severity:** Info
- **Fix:** Even in serial mode (DOP 1), this query needs 512 MB+ just for its sort and hash operators. This is an absolute size problem independent of parallelism. Filter data earlier in the plan, add indexes to avoid sorts, or reduce the number of sort/hash operations in the query.
### S31 — Non-QDS Forced Plan (Plan Guide)
- **Trigger:** `PlanGuideName` attribute present on `StmtSimple` AND does NOT start with `QDS_`
- **Severity:** Warning
- **Fix:** A traditional `sp_create_plan_guide` is forcing this plan — distinct from S24 which catches Query Store forced plans. Traditional plan guides are fragile: they break silently when the query text changes, when statistics update dramatically, or when the hinted plan's index is dropped. Validate the guide is still beneficial: `SELECT * FROM sys.plan_guides WHERE name = '';` then capture the current plan without the guide and compare with `/sqlplan-compare`.
### S32 — Compile Wall-Clock vs CPU Gap (Compilation Contention)
- **Trigger:** `CompileTime` > `CompileCPU` × 2 AND `CompileTime` > 1,000 ms (wall-clock compile time significantly exceeds CPU time)
- **Severity:** Info
- **Fix:** SQL Server spent compile time waiting rather than working — typically a latch contention on plan cache bucket locks, or memory pressure forcing the optimizer to wait. `CompileTime` is wall-clock; `CompileCPU` is CPU-only. A large gap means idle CPU during compilation. Check `sys.dm_os_wait_stats` for `RESOURCE_SEMAPHORE_QUERY_COMPILE` waits. Use `OPTION (RECO

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [vanterx](https://github.com/vanterx)
- **Source:** [vanterx/mssql-performance-skills](https://github.com/vanterx/mssql-performance-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-vanterx-mssql-performance-skills-sqlplan-review
- Seller: https://agentstack.voostack.com/s/vanterx
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.