Reading execution plans: what to check first?

-- performance tuning · Ronald de Groot

An execution plan for a sizeable query can contain dozens of operators. Where do you start? After thirty years of performance tuning, my experience is: in nine out of ten plans you'll find the culprit with the five checks below — usually within a few minutes.

Prefer the actual execution plan (or Query Store / the cached plan with runtime statistics). The estimated plan only shows what the optimizer thought would happen, not what actually did.

1. Follow the thick arrows

The thickness of an arrow between two operators represents the number of rows flowing through it. Start at the top right (that's where SQL Server starts reading) and follow the thickest arrows. Often you'll see millions of rows being read while the query only returns a few hundred: all those rows were filtered out too late. That's almost always a sign that an index on the filter column is missing, or that a predicate isn't sargable.

-- Not sargable: function wrapped around the column, index unusable
WHERE YEAR(OrderDate) = 2026

-- Sargable: the index on OrderDate can do a seek
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'

2. A scan where you expect a seek

A Clustered Index Scan or Index Scan on a large table, while your query only needs a handful of rows, is check number two. Common causes:

No index exists that supports the WHERE predicate.
Implicit conversion: the column is varchar, the parameter is nvarchar — look for CONVERT_IMPLICIT in the plan.
A function or calculation applied to the column (see point 1).

Note: a scan isn't wrong by definition. For a small table, or when you genuinely need almost all rows, a scan is actually the cheapest choice.

3. Key Lookups with high row counts

A Key Lookup means: the nonclustered index produced the row, but some columns were missing, so SQL Server has to go back to the clustered index for every row. With ten rows, no problem; with a hundred thousand rows, that's a hundred thousand extra I/O operations. The fix is usually a covering index:

CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount);   -- carry the lookup columns in the index

I go deeper into this in my article on index tuning.

4. Estimated vs. actual rows: is the estimate right?

For each operator, compare the estimated row count with the actual one. A deviation by a factor of 10, 100 or more means the optimizer based its choices (join type, memory, order) on wrong information. The most common causes:

Stale statistics — run UPDATE STATISTICS or use Ola Hallengren's maintenance solution.
Parameter sniffing — the plan was compiled for a parameter value with very different row counts.
Table variables — historically estimated at 1 row (until SQL 2019's deferred compilation).

5. Warnings: the exclamation marks

Operators with a yellow exclamation mark always deserve a look. The most important warnings:

Warning	Meaning
Spill to tempdb	Too little memory granted; a sort or hash join falls back to tempdb — often the result of bad estimates (point 4).
Implicit conversion	Data type mismatch; can make index seeks impossible (point 2).
No join predicate	Cartesian product — almost always a forgotten JOIN condition.
Excessive memory grant	The query requested far more memory than it used; squeezes out other queries.

Finally: measure beyond the plan

The plan doesn't tell you everything. When tuning, also switch this on:

SET STATISTICS IO, TIME ON;

The logical reads per table are the most honest metric for comparing a before-and-after situation: they're independent of cache and server load. If the number of logical reads drops dramatically, your fix is a real improvement — whatever the cost percentages in the plan may claim.

Need help with a stubbornly slow plan? Feel free to get in touch — a second opinion on an execution plan is quickly given.