How to Read a SQL Server Execution Plan: Seeks, Scans, and Key Lookups

The last two lessons referenced execution plans repeatedly to prove their claims. Here’s how to actually read one systematically — the skill that turns “this query feels slow” into “this specific operator is the problem, for this specific reason.”

In SSMS, press Ctrl+M (Include Actual Execution Plan) before running a query.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT customer_id, order_status, order_total
FROM dbo.OrderLog
WHERE customer_id = 42;

The Key Plan Elements

Read a plan right to left, top to bottom — the rightmost, deepest operators run first (typically the actual table/index access), feeding data up and left into operators that filter, join, and aggregate it, until the leftmost operator produces the final result.

The Single Most Useful Diagnostic Signal

The optimizer chooses its plan based on estimated row counts — when those estimates are badly wrong, it often picks a suboptimal plan (the wrong join type, an index skipped in favor of a scan, an inappropriate memory grant). STATISTICS IO reports logical reads per table, often a more stable, comparable metric across runs than wall-clock time, since wall-clock time is affected by whatever else the machine happens to be doing at that moment.

The Cost Percentage Trap

-- A parameter-sniffing-prone query worth trying: run once with a common value,
-- once with a rare one, and compare estimated vs actual rows on the same plan shape
SELECT * FROM dbo.OrderLog WHERE order_status = 'completed'; -- common
SELECT * FROM dbo.OrderLog WHERE order_status = 'cancelled'; -- rare
-- Different row counts naturally produce different (correct) estimates per query --
-- this becomes a real problem specifically inside a cached stored procedure plan,
-- covered in the Performance Tuning course