Reading SQL Server execution plans: a practical guide

When a query is slow, the execution plan is the first thing to look at. It is SQL Server's own description of how it retrieved your data: which indexes it touched, in what order, how it joined tables and how many rows it expected at each step.

Estimated vs. actual plans

SQL Server can show you two kinds of plans:

• Estimated plan — produced by the optimizer without running the query. Useful for expensive queries you can't afford to run.
• Actual plan — the same plan, plus runtime statistics: actual row counts, number of executions, memory grants and warnings.

In SSMS, press Ctrl+M (include actual plan) before running the query, or Ctrl+L for an estimated plan. Pair it with I/O and timing statistics:

SET STATISTICS IO, TIME ON;

SELECT o.OrderID, o.OrderDate, c.CompanyName
FROM Sales.Orders AS o
JOIN Sales.Customers AS c ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2026-01-01';

The Messages tab now shows logical reads per table — the single most useful number for comparing two versions of a query.

Read plans right to left

Graphical plans flow from right to left: data starts at the operators on the right (index seeks and scans) and moves left toward the SELECT operator. Arrow thickness is proportional to the number of rows.

The operators that matter most

Operator	What it means	Watch for
Index Seek	Navigates the B-tree directly to matching rows	Usually good
Index / Table Scan	Reads the whole index or heap	Fine for small tables or when most rows are needed
Key Lookup	Fetches extra columns from the clustered index for each row	Expensive when executed thousands of times
Nested Loops	For each outer row, probes the inner input	Great for small outer inputs
Hash Match	Builds a hash table from one input	Needs a memory grant; can spill to tempdb
Merge Join	Joins two inputs already sorted on the join key	Very efficient when sorts come for free
Sort	Sorts rows in memory	Memory grant, possible spills

Estimates vs. actuals: where most problems hide

Hover over any operator to compare Estimated Number of Rows with Actual Number of Rows. When they differ by orders of magnitude, the optimizer chose the plan based on a wrong picture of your data. Common causes:

• Stale statistics — run UPDATE STATISTICS dbo.YourTable; and compare.
• Non-SARGable predicates — wrapping a column in a function (WHERE YEAR(OrderDate) = 2026) hides it from the index. Rewrite as a range: WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'.
• Table variables and multi-statement functions — historically estimated at very low row counts.
• Parameter sniffing — a plan compiled for one parameter value reused for a very different one.

Warnings you should never ignore

A yellow triangle on an operator is SQL Server telling you something went wrong:

• Implicit conversion — e.g. comparing an NVARCHAR parameter to a VARCHAR column. It can prevent seeks.
• Spill to tempdb — a sort or hash ran out of its memory grant.
• Missing index — a hint, not an order. Evaluate it before creating it; the suggestion ignores existing indexes and write overhead.

Fixing a key lookup

A classic pattern: a seek on a nonclustered index followed by a key lookup for every row.

-- The index only contains CustomerID, so OrderDate and TotalDue require lookups
CREATE INDEX IX_Orders_CustomerID ON Sales.Orders (CustomerID);

-- Cover the query instead
CREATE INDEX IX_Orders_CustomerID_Covering
ON Sales.Orders (CustomerID)
INCLUDE (OrderDate, TotalDue);

With the covering index, the lookup disappears and logical reads typically drop dramatically.

Finding expensive plans in production

You don't have to reproduce problems by hand. Query Store records plans and runtime statistics over time:

SELECT TOP (10)
    qt.query_sql_text,
    rs.avg_duration / 1000.0 AS avg_ms,
    rs.count_executions
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
ORDER BY rs.avg_duration * rs.count_executions DESC;

Key takeaways

1. Always measure with STATISTICS IO and actual plans.
2. Compare estimated and actual rows — big gaps explain most bad plans.
3. Make predicates SARGable and cover hot queries.
4. Use Query Store to find regressions before users do.