A SQL Server execution plan is the set of physical operations the query optimizer chooses to run a query — the scans, seeks, joins, and sorts, along with the row counts it estimated and the relative cost of each step.
You read a graphical plan right-to-left and top-to-bottom, following the arrows in the direction of data flow. The most expensive operators and the widest arrows show where the time and the rows are going.
The execution plan is the single most useful artifact for diagnosing a slow query — it shows what SQL Server actually did, not what you hoped it would do. This guide covers how to capture one, how to read it, and the handful of signals that point straight at the problem.
What an execution plan is
When you submit a query, SQL Server's query optimizer evaluates many possible ways to satisfy it and picks the one it estimates to be cheapest. That chosen strategy — expressed as a tree of operators — is the execution plan. Per Microsoft's documentation, the plan reveals which tables and indexes were used, the join types, the order of operations, and the estimated number of rows flowing between each step.
There are two forms:
- Estimated plan — produced at compile time without running the query. It shows only estimates.
- Actual plan — produced after the query runs. It adds runtime data such as actual row counts and warnings, which makes it far more useful for diagnosis.
How to capture a plan
You can get a plan in seconds without any extra tooling. In SQL Server Management Studio:
- Estimated plan: press
Ctrl+L(Display Estimated Execution Plan) — the query is not executed. - Actual plan: press
Ctrl+M(Include Actual Execution Plan), then run the query. - As XML:
SET STATISTICS XML ON;returns the plan alongside the results.
To pull the plan for something already running or recently cached, use the dynamic management functions:
Retrieve cached plans for stored proceduresSELECT TOP (25)
OBJECT_NAME(qp.objectid, qp.dbid) AS [procedure],
qs.execution_count,
qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_ms,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.objectid IS NOT NULL
ORDER BY avg_elapsed_ms DESC;
How to read it
Graphical plans flow right-to-left and top-to-bottom. The right-most operators run first (they touch the data), and rows flow leftward toward the final result. A few conventions carry most of the meaning:
- Operator cost %. Under each operator is its estimated share of total query cost. Start with the highest-cost operators.
- Arrow thickness. The width of the arrows is proportional to the number of rows moving between operators — a thick arrow deep in the plan means a lot of rows are being handled early.
- Tooltips. Hover any operator for detail: estimated vs. actual rows, I/O and CPU cost estimates, the object and index used, and predicates.
The operators that matter most
| Operator | What it usually tells you |
|---|---|
| Index Seek | Efficient — SQL Server used an index to jump to the rows it needed. |
| Index / Table Scan | Reads the whole structure. Fine on small tables; a red flag on large ones where a seek was possible. |
| Key / RID Lookup | The index didn't cover the query, so it looked up extra columns row by row. Often fixed with a covering index. |
| Nested Loops / Hash / Merge Join | The join strategy. A hash join on a query you expected to be small can signal a bad row estimate. |
| Sort | Expensive and memory-hungry. Sometimes avoidable with an index in the right order. |
The warning signs
Certain patterns reliably point to a fixable problem:
- Estimated rows far from actual rows. The optimizer was misled — usually parameter sniffing or stale statistics.
- A scan where a seek was possible. A missing or unusable index, or a non-SARGable predicate.
- Implicit conversion warnings. A data-type mismatch forcing a scan.
- Operators spilling to tempdb (sort/hash spill warnings). The estimate was too low to grant enough memory.
The plan tells you where; the optimization guide covers what to do about each cause — and finding the slow procedures is where to start.
How AI analysis uses execution plans
Execution plans are exactly the kind of structured evidence an AI analysis step reasons over well. Given the plan alongside the DDL, indexes, and statistics, a model can connect an estimate/actual gap to its likely cause and propose a targeted fix — a covering index, a SARGable rewrite, a sniffing remedy — far faster than reading the XML by hand.
SprocOptimizer collects execution plans as part of the evidence package it sends to Claude AI for analysis. As always, any resulting rewrite is verified for logical equivalence and validated against your real parameters before it can be promoted.
Frequently asked questions
In SQL Server Management Studio, press Ctrl+L to display the estimated execution plan without running the query, or Ctrl+M (Include Actual Execution Plan) before executing to capture the actual plan with runtime statistics. You can also retrieve a cached plan programmatically with sys.dm_exec_query_plan.
Read the graphical plan from right to left and top to bottom, following the arrows in the direction of data flow. Each operator shows its estimated percentage of total query cost; hover any operator to see detailed statistics. The most expensive operators and the widest data-flow arrows show where the time and rows are going.
An estimated plan is produced from the optimizer's compilation without running the query, so it shows only estimated row counts. An actual plan is generated after the query executes and includes runtime information such as actual row counts and warnings. Comparing estimated versus actual rows is one of the most useful diagnostics — a large divergence usually means stale statistics or parameter sniffing.
Look for operators with a high relative cost, large gaps between estimated and actual rows, table or index scans where a seek was expected, expensive sorts and key lookups, implicit conversion warnings, and operators that spill to tempdb. These are the signals that point to a missing index, stale statistics, a non-SARGable predicate, or a parameter-sniffing problem.
Primary sources & further reading
- Microsoft Learn — Execution plans.
- Microsoft Learn — Analyze an actual execution plan.
- SQLShack — How to read an execution plan with all details.
Let the analysis read the plan for you
SprocOptimizer collects execution plans as evidence, has Claude AI diagnose them, and validates every proposed fix — on-premises, with no row-level data leaving your network.
Request a Demo Read the Optimization Guide