Execution Plan Analysis
Use this skill when the user wants to understand or analyze a SQL Server execution plan. This provides plan summaries, operator cost rankings, and bottleneck identification — all without executing any queries.
When to Use
- User says "analyze this plan", "explain this execution plan", "find bottlenecks"
- User asks to explain why the supplied plan is slow, limited to plan analysis
- User says "what are the most expensive operators?", "top costly operators"
- User says "summarize this plan", "give me an overview"
- User provides a query and wants to see its estimated plan
When NOT to Use
- Generic query performance requests, including "why is this query slow?" with a supplied plan, belong to
improve-query-performance; use this skill when plan analysis itself is requested or delegated. - User wants server-wide diagnostics → use the
resource-usage-*orhealth-audit-*skills - User asks only about plan metadata (warnings, indexes used, row counts, tables accessed) → read the plan XML directly without running the ranking or bottleneck procedures
Required tools
- File read access, to read a
.sqlplanor.xmlshowplan file the user provides. - Only when the user provides a query instead of a plan: a SQL query tool connected to the target server and database. Prefer, in order: the SQL Tools MCP server that ships with the
microsoft-sql-adminplugin (mssql_list_database_connections,mssql_connect_database, thenmssql_execute_query); any other SQL tool the agent host provides for running T-SQL and reading result sets;sqlcmdfrom a terminal. If using the SET-based scripts below instead of a dedicated plan tool, the host must interpretGObatch separators in one session (or explicitly preserve that session between calls), becauseSET SHOWPLAN_XMLandSET STATISTICS XMLonly apply to the session that sets them. If the SQL Tools MCP server is not connected or lists no connections, follow thesql-tools-mcp-setupskill, which asks before installing or configuring anything. If no SQL tool can be made available, give the user the T-SQL to run and ask for the resulting plan XML.
SQL Tools MCP session boundary
With SQL Tools MCP, do not send GO separators to mssql_execute_query, and do not rely on session SET options surviving a second tool call. That tool accepts raw T-SQL, not a sqlcmd script. Prefer getEstimatedShowplanForQuery for estimated-plan retrieval; it is in the server's default tool set, so when it is missing the host has filtered the tools or runs an older build. Use getShowplanWithExecutionStatsForQuery only with explicit authorization to execute the workload. Call a plan tool once per plan and reuse its result; do not loop re-running the statement through mssql_execute_query to compare variants. Otherwise use supplied plan XML or a verified same-session sqlcmd capture. Keep estimated-plan capture separate from execution.
Workflow
Reuse a supplied matching capture and any compact analysis already produced for that capture. Do not replace actual statistics with a new estimated plan. When another workflow delegates a specific question, return that answer in its format instead of restarting scope selection or producing an unrelated full report.
Optional specialized tools
With the SQL Tools MCP server, SummarizePlan and GetPlanWarnings provide
compact metadata; PlanBottleneckAnalysis provides candidate bottlenecks;
PlanOperatorAnalysis provides the requested cost, elapsed-time or CPU ranking.
They read a plan from a file path, planXml, a Query Store queryStorePlanId or
a plan-cache planHandle, so a plan found by query needs no re-execution. Use
only the calls needed for the question (one call per tool per plan), and reuse
their results for the same capture. A tool's file path must be reachable in that
tool's environment; a local host path is not automatically a server path.
When these tools are absent, read the supplied XML and use the bundled calculator
for estimated own-cost ranking.
For CPU ranking, use actual CPU counters and a CPU denominator; elapsed time is not a substitute. Do not infer a specific wait type from elapsed-minus-CPU alone, or sum inclusive/overlapping operator counters into a query total. Report the observed metric and use separate wait/runtime evidence for causal attribution. If actual counters or the relevant query total are missing or zero, report the metric unavailable rather than rank placeholder zeros. Do not execute a query solely to fill that gap unless execution is already authorized and eligible in the requested scope.
Step 1: Obtain the Plan
If the user provides a .sqlplan or .xml file path, read that file directly (it is showplan XML).
If the user provides a query instead:
By default, get the estimated plan. This compiles without executing the workload; compilation can still take locks or trigger statistics work. With
sqlcmdor a verified batch-aware host, send all of the following in one session, with eachSET SHOWPLAN_XMLin its own batch:SET SHOWPLAN_XML ON; GO <the user's query> GO SET SHOWPLAN_XML OFF; GOThe query is compiled but not executed; the result is one row per statement with a single XML column containing the showplan. Never send the query in a separate request on the assumption that
SHOWPLAN_XMLis still on: if the tool does not keep the session, the query would execute. If the tool cannot sendGO-separated batches in one request or keep a session, usesqlcmdor ask the user to capture the estimated plan.Only get the actual plan (with runtime statistics) if the user explicitly agrees to execute the query. Require explicit authorization; reuse authorization already given for this scope — the query runs against the database and may be long-running or resource-intensive:
SET STATISTICS XML ON; <the user's query> SET STATISTICS XML OFF;The query's own result sets are returned first, followed by a result set with the actual showplan XML.
If you have file write access, save the XML as a
.sqlplanfile so the user can open it graphically (for example in SSMS), and tell the user the path.
Step 2: Plan Summary
Read the showplan XML (namespace http://schemas.microsoft.com/sqlserver/2004/07/showplan) and build a high-level overview for each statement (StmtSimple element):
- Statement text —
StatementTextattribute - Total estimated cost —
StatementSubTreeCostattribute - Degree of parallelism (DOP) —
DegreeOfParallelismattribute onQueryPlan(absent or 1 for serial plans) - Memory grant info (requested/granted/max used) —
RequestedMemory,GrantedMemory,MaxUsedMemoryonQueryPlan/MemoryGrantInfo(granted and max used are only meaningful in actual plans) - Plan hash —
QueryPlanHashattribute (useful when comparing plans)
Step 3: Choose Analysis Based on User Intent
The ranking and bottleneck procedures below work on the RelOp elements of the plan. For each RelOp, note NodeId, PhysicalOp, LogicalOp, EstimateRows, and EstimatedTotalSubtreeCost. Its direct child operators are the RelOp elements one operator element down (RelOp/*/RelOp). Compute:
- Own cost = the operator's
EstimatedTotalSubtreeCostminus the sum of its direct childRelOps'EstimatedTotalSubtreeCost(floor at 0). - Cost % = own cost ÷ the statement's
StatementSubTreeCost× 100.
For reproducible ranking, prefer the bundled local Python 3 helper: python scripts/plan_costs.py <plan.sqlplan> --top 5 (resolve the script relative to this skill). It rejects DTD/entity declarations, duplicate IDs and invalid statement costs, keeps statements separate, uses decimal arithmetic, floors own cost at zero, and breaks ties by NodeId. Run it on the complete supplied XML; do not manually retype a large plan. Record parser failures instead of inventing a ranking. This reads a local file and does not execute SQL. Estimated cost is not elapsed time or proof of a runtime bottleneck. If Python is unavailable, explicitly use the same calculation rules below.
You can also let SQL Server do the arithmetic (read-only; the agent substitutes the plan XML as a Unicode string literal, doubling any single quotes):
DECLARE @plan xml = N'<ShowPlanXML ...>'; -- substitute XML, escape single quotes
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT s.value('@StatementId', 'int') AS StatementId,
r.value('@NodeId', 'int') AS NodeId,
r.value('@PhysicalOp', 'nvarchar(128)') AS PhysicalOp,
CASE WHEN c.RawOwnCost < 0 THEN 0 ELSE c.RawOwnCost END AS OwnCost
FROM @plan.nodes('//StmtSimple') AS statements(s)
CROSS APPLY s.nodes('QueryPlan//RelOp') AS operators(r)
CROSS APPLY (SELECT r.value('@EstimatedTotalSubtreeCost', 'decimal(38,15)')
- ISNULL(r.value('sum(*/RelOp/@EstimatedTotalSubtreeCost)', 'decimal(38,15)'), 0)) AS c(RawOwnCost)
ORDER BY StatementId, OwnCost DESC, NodeId;
For a plan with several statements, analyze each StmtSimple separately.
For bottleneck investigation ("find bottlenecks", "why is this query slow?", "identify performance issues", "where to focus optimization"):
- Analyze the plan XML yourself — identify warnings, missing index hints, cardinality estimate issues, key lookups, spills.
- Find the cost-dominant subtrees: a minimal set of subtree roots whose combined cost covers ≥80% of total estimated cost.
- Start with the set
{root RelOp}. - Take the node in the set with the largest
EstimatedTotalSubtreeCostthat has childRelOps and is not marked final. Tentatively replace it with its direct children. Keep the replacement if the set's summedEstimatedTotalSubtreeCostis still ≥80% ofStatementSubTreeCost; otherwise undo it and mark the node final. - Repeat step 2 until no node in the set can be replaced.
- Drop the cheapest subtrees from the set while the remaining total stays ≥80%.
- The resulting subtrees are signals, not authoritative answers.
- Start with the set
- Merge your own analysis with these signals — confirm, supplement, or skip signals based on your judgment.
For operator cost ranking ("top expensive operators", "rank operators by cost", "most costly operators", "which operator takes the most time"):
Rank the RelOp operators by own cost (cost %), highest first.
- Estimated cost ranking works for all plans.
- Elapsed time ranking is only available when the plan includes actual execution statistics (i.e., an actual plan captured with
SET STATISTICS XML ON). Do not use elapsed time on estimated-only plans. UseActualElapsedmsfromRunTimeInformation/RunTimeCountersPerThread(take the maximum across threads). For row-mode operators this value includes the time of their child operators, so subtract the children's elapsed time to get the operator's own time; batch-mode operators (ActualExecutionMode="Batch") report only their own time. - For CPU time questions, read the plan XML directly for
ActualCPUmsattributes.
For plan metadata questions (warnings, indexes used, row counts, tables accessed, missing indexes, UDFs/TVFs, special optimizations):
Do NOT run the ranking or bottleneck procedures — read the plan XML directly. Examples:
- Warnings →
<Warnings>elements - Indexes used →
IndexScan/IndexSeeknodes - Tables accessed → table references from operator nodes
- Estimated vs actual rows →
EstimateRowsvsActualRowsattributes - Missing index hints →
<MissingIndexes>elements - UDFs/TVFs →
ScalarFunction/TableValuedFunctionoperators - Top operators by logical/physical reads →
ActualLogicalReads/ActualPhysicalReadsfrom<RunTimeCountersPerThread>
CPU, elapsed-time, and read rankings require an actual execution plan containing the relevant runtime counters. If they are absent, report that the supplied plan cannot support the requested ranking. Never execute the query to obtain missing counters without explicit user consent.
Step 4: Report Findings
Present the analysis in a clear format:
## Execution Plan Analysis
### Plan Overview
- Statement: (first 200 chars)
- Total Estimated Cost: {{cost}}
- DOP: {{degree of parallelism}}
- Memory Grant: {{requested}} / {{granted}} / {{max used}}
### Bottleneck Analysis
{{For each bottleneck identified:}}
- **{{Operator}} at Node {{id}}**: {{description of the issue and why it's costly}}
### Top Operators by Cost
| Node | Operator | Cost % | Description |
|------|----------|--------|-------------|
| {{id}} | {{operator}} | {{pct}} | {{brief note}} |
### Warnings & Issues
{{List any plan warnings, cardinality misestimates, missing indexes, spills, key lookups}}
### Recommendations
{{Brief suggestions: consider adding indexes, update statistics, review join strategy, etc.}}
Important Rules
- Operator cost ranking is ONLY for cost/time ranking questions — do not use it for metadata questions (warnings, indexes, row counts, etc.).
- The bottleneck procedure is for bottleneck investigation — do not answer "why is this slow?" or "find bottlenecks" with a plain operator ranking.
- You CAN read plan files — when the user provides a file path, read the file directly. Do NOT say you cannot access files.
- Do not execute queries without user permission — default to the estimated plan (
SET SHOWPLAN_XML ON) for plan generation.
Related Skills
improve-query-performance— Full optimization workflow that includes plan analysis as a stepquery-hint-tuning— Test query hints after identifying bottlenecksquery-rewrite— Rewrite the query for better performance