Reading SQL Server execution plans: a practical guide

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.

What causes slow queries in SQL Server, and how do I fix them?

A diagnostic catalogue of slow query causes in SQL Server: how to tell running from waiting, the plan or wait signature of each cause, and the fix.

#execution-plans#performance#troubleshooting