How to read a SQL Server execution plan

Estimated or actual plan

• Estimated plan: what the optimizer intends to do. The query does not run. Safe on production, but it shows guesses only.
• Actual plan: the same plan plus what really happened: rows per operator, executions, memory grants and warnings. The query runs.

Use the actual plan whenever you can run the query safely, because the useful clues are in the difference between estimated and actual.

1. Capture the actual plan

In SSMS, press Ctrl+M (Include Actual Execution Plan) before running the query. Add I/O and time statistics to see the cost in numbers:

SET STATISTICS IO, TIME ON;

SELECT o.OrderId, o.Status, c.Email
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.Id = o.CustomerId
WHERE o.Status = 'PENDING'
  AND o.CreatedAt > DATEADD(hour, -1, SYSUTCDATETIME());

SET STATISTICS IO, TIME OFF;

The Messages tab then shows logical reads per table. Logical reads are the best single number to compare before and after a change: they don't vary with cache state or server load the way elapsed time does.

2. Read it right to left

Data flows from the right-most operators, which read tables and indexes, to the left-most, the SELECT. Arrow thickness shows how many rows pass between operators. Start at the thickest arrows and the operators with the highest cost percentage.

Cost percentages are estimates, even in an actual plan. Use them to find where to look, then confirm with actual rows and logical reads.

3. Operators that matter

• Index Seek: jumps to matching rows. Usually what you want for selective filters.
• Index Scan / Clustered Index Scan: reads the whole index or table. Fine for small tables or when most rows are needed; a problem when the filter should be selective.
• Key Lookup: after a seek, fetches extra columns from the clustered index, once per row. Cheap for a few rows, expensive for thousands. A covering index with INCLUDE columns removes it.
• Nested Loops, Hash Match, Merge Join: the three join strategies. A Nested Loops join with a large outer input is often the expensive part of a slow plan.
• Sort and Hash Match need a memory grant. If the grant is too small, they spill to tempdb.

4. Estimated against actual rows

Hover an operator, or open its properties, and compare Estimated Number of Rows with Actual Number of Rows. When they differ by orders of magnitude, the optimizer chose the plan for a different workload than the one it got.

Common causes:

• Stale statistics: refresh with UPDATE STATISTICS dbo.Orders;.
• Parameter sensitivity: the plan was compiled for a parameter value with very different row counts. Compare with OPTION (RECOMPILE) to test whether that's the cause.
• Table variables and multi-statement functions with fixed, low estimates.

5. Warnings to act on

A yellow triangle on an operator means a warning. The ones worth acting on:

• Implicit conversion (CONVERT_IMPLICIT): a column is compared with a different data type, such as varchar against nvarchar, which can prevent an index seek. Match the parameter type to the column.
• Spill to tempdb on a Sort or Hash: the memory grant was too small, usually because of a low row estimate.
• Missing index suggestion in green above the plan: a hint, not an instruction. Check it against existing indexes and write cost before creating it.

6. Plans over time: Query Store

A query that was fast yesterday and slow today often changed plan. Query Store keeps plans and runtime statistics per query, and is on by default for new databases from SQL Server 2022.

SELECT q.query_id,
       COUNT(DISTINCT p.plan_id)  AS plans,
       LEFT(t.query_sql_text, 120) AS query
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
GROUP BY q.query_id, t.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY plans DESC;

In SSMS, the Regressed Queries report shows the same thing graphically. If an older plan was clearly better, you can force it while you fix the cause:

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

The same steps in Quorum

Quorum keeps plan history beside the workload: it compares equivalent windows, flags regressions, and explains estimated against actual rows, spills and warnings in plain English.

• Plan history and regressions: fingerprint, plan, calls, waits and input mix compared across equivalent windows.
• Query Store as a source, alongside wait-time analysis per statement.
• Index advice weighed honestly: ranked by measured impact, with storage, write overhead and overlapping indexes shown.