Reading Execution Plans

October 18, 2026
| No Comments

Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

The execution plan is how SQL Server decided to run a query. Reading one well comes down to a handful of checks: where the estimates go wrong, where most rows flow, and what the warnings say.

Getting the plan

  • Estimated plan (Ctrl+L in SSMS): the plan without running the query. Fine for a first look; it has no actual row counts.
  • Actual plan (Ctrl+M, then run): adds actual rows, executions, memory used and warnings. This is the one to tune from.
  • Live Query Statistics: shows the actual plan filling in while a long query runs. Useful when the query takes too long to wait for.
  • From the cache or Query Store: the plan a query used in production, with estimates only.
-- The cached plan for a statement
SELECT TOP (10) qs.execution_count, qs.total_worker_time / 1000 AS total_cpu_ms, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE N'%Orders%' AND st.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY qs.total_worker_time DESC;

-- The actual plan as XML, without SSMS
SET STATISTICS XML ON;
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = 42;
SET STATISTICS XML OFF;

Click the query_plan column in SSMS to open the graphical plan. Save plans as .sqlplan files so you can compare before and after.

Read it in this order

\1. The root operator (top left): right-click, Properties. Check the warnings, the memory grant, Degree of Parallelism, the compile and optimisation level, and the parameter values it was compiled with (Parameter List).

\2. The fat arrows: arrow width shows the number of rows. Find where large numbers of rows appear and where they are thrown away.

\3. Estimated against actual rows on each operator. A difference of 10 times or more is where the optimiser went wrong, and the cause is usually statistics, a function or conversion on a column, a table variable, or a parameter compiled for a different value.

\4. Yellow warning triangles: implicit conversions, spills to tempdb, missing join predicates, excessive memory grants.

The cost percentages are estimates, even in an actual plan. An operator showing 2% can be where all the time goes if its row estimate is wrong.

Common data access operators

  • Index Seek: goes straight to the matching rows using the index key. Usually what you want, but a seek that returns most of the table is a scan in disguise.
  • Index Scan / Clustered Index Scan: reads the whole index. Fine for small tables or when most rows are needed.
  • Key Lookup (or RID Lookup on a heap): for each row found in a non-clustered index, a separate trip to the clustered index for columns the index does not have. Cheap for a few rows, expensive for thousands. Adding the missing columns as INCLUDE columns removes it.
  • Table Scan: a scan of a heap (a table with no clustered index).

Seek predicates and residual predicates

Hover over a seek and look at two properties. The Seek Predicate is the part used to navigate the index. A Predicate (residual) is a filter applied to every row the seek returns. A seek on the first column that then filters millions of rows on a residual predicate is doing far more work than the word “seek” suggests; an index with the columns in a better order usually fixes it.

-- Seek on CustomerID, residual filter on Amount
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);
SELECT OrderID FROM dbo.Orders WHERE CustomerID = 42 AND Amount > 100;

Join operators

  • Nested Loops: for each row from the top input, look up matches in the bottom input. Best when the top input is small and the bottom is indexed.
  • Hash Match: builds a hash table from the top input, then probes it with the bottom. Good for large unsorted inputs; needs a memory grant.
  • Merge Join: walks two inputs already sorted on the join key. Very efficient when both are sorted.

None is good or bad in itself. A Nested Loops join that runs its bottom input 2 million times because the estimate said 10 rows is the classic problem.

Memory grants and spills

Sorts and hash operations need memory, reserved before the query starts from the estimated row counts. Too small and they spill to tempdb (a warning on the operator); too big and other queries wait for memory (RESOURCE_SEMAPHORE waits). Both come back to bad estimates.

-- Queries waiting for or holding memory grants right now
SELECT session_id, requested_memory_kb, granted_memory_kb, used_memory_kb,
       wait_time_ms, dop
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

Parallelism

Plans with the yellow double-arrow icons run on several cores. Parallelism is chosen when the estimated cost is above the cost threshold for parallelism (default 5, far too low for modern servers; 50 is a common starting point) and is capped by MAXDOP. Uneven work across threads shows as CXPACKET/CXCONSUMER waits; in the actual plan, compare the actual rows per thread in the operator properties.

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (N'cost threshold for parallelism', N'max degree of parallelism');

From my SQL Server DBA handbook.

Performance