Reading Actual Execution Plans: Operators, Estimates vs Actuals and Logical Reads

Learning outcome

You can read an actual execution plan from the data source outwards, name the common operators (Index Seek, Key Lookup, Clustered Index Scan, Nested Loops, Hash Match, Stream Aggregate, Sort, Top, Parallelism), compare estimated with actual rows to spot a bad estimate, and connect a plan to its STATISTICS IO cost. This is the skill every later lesson uses.

Intuition

A plan is a tree of operators. Rows flow from the leaves (the operators that read tables and indexes) up to the root (the result). Each operator was chosen because the optimizer estimated how many rows it would see. When the estimate is close to the actual count, the plan is usually sensible. When it is off by orders of magnitude, the plan is often wrong, and the cause is upstream: statistics, a non-sargable predicate, or a sniffed parameter.

flowchart BT
    S["Index Seek IX_Orders_CustomerId: est 40 / actual 40"] --> NL["Nested Loops"]
    K["Key Lookup PK_Orders: 40 executions"] --> NL
    NL --> R["Result: 40 rows, 133 logical reads"]

Deep dive

• How to get the actual plan. In SSMS or Azure Data Studio, "Include Actual Execution Plan". In T-SQL, SET STATISTICS XML ON returns the plan with runtime counters (the runner in this course uses it). The estimated plan (SET SHOWPLAN_XML ON) has no actual counts.
• Read right to left, bottom to top in graphical plans. In the indented text in this course, children are indented under their parent; rows flow upward, while parent operators request work from their children.
• Data access operators.
  • Index Seek / Clustered Index Seek: navigate the B+ tree to a range.
  • Index Scan / Clustered Index Scan / Table Scan: read every leaf page (or stop early under a Top).
  • Key Lookup: for each row from a nonclustered index, fetch the missing columns from the clustered index. Watch its executions count.
• Join operators.
  • Nested Loops: for each outer row, probe the inner input. Good when the outer input is small.
  • Hash Match: build a hash table on one input, probe with the other. Good for large unsorted inputs; needs a memory grant.
  • Merge Join: both inputs sorted on the join key; cheap if the order comes free from indexes.
• Other operators. Sort (needs memory, can spill to tempdb), Top, Stream Aggregate (needs sorted input or a single group), Hash Match (Aggregate), Compute Scalar, Parallelism (Gather Streams) for parallel plans.
• Estimated vs actual. For an operator that runs once, compare est with actual. For the inner side of a Nested Loops, the estimate is per execution, so compare est × executions with actual.
• Costs are estimates. The "cost %" in SSMS is the optimizer's model, not a measurement, even in an actual plan. Use logical reads, actual rows and elapsed time to judge.

Code example: five plans over 200,000 orders

-- Lesson 2: reading actual execution plans. Customers (5,000) and Orders (200,000).
SET NOCOUNT ON;
CREATE TABLE dbo.Customers (CustomerId int NOT NULL CONSTRAINT PK_Customers PRIMARY KEY, Name nvarchar(50) NOT NULL, Country char(2) NOT NULL);
CREATE TABLE dbo.Orders (OrderId int NOT NULL CONSTRAINT PK_Orders PRIMARY KEY, CustomerId int NOT NULL, OrderDate date NOT NULL, Total decimal(10,2) NOT NULL);
WITH n AS (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_objects a CROSS JOIN sys.all_objects b)
INSERT dbo.Orders SELECT i, i % 5000 + 1, DATEADD(day, i % 730, '2024-01-01'), (i % 997) + 0.99 FROM n;
INSERT dbo.Customers SELECT CustomerId, CONCAT(N'Customer ', CustomerId), CASE WHEN CustomerId % 10 = 0 THEN 'IN' ELSE 'US' END
FROM (SELECT DISTINCT CustomerId FROM dbo.Orders) d;
CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId);
GO
-- > 1. One customer's orders: Index Seek + Key Lookup joined by Nested Loops
-- @plan
-- @quiet
SET STATISTICS IO ON;
SELECT OrderId, OrderDate, Total FROM dbo.Orders WHERE CustomerId = 42;
SET STATISTICS IO OFF;
GO
-- > 2. Join all Indian customers to their orders: a Hash Match join on big inputs
-- @plan
SET STATISTICS IO ON;
SELECT c.Country, COUNT(*) AS Orders, SUM(o.Total) AS Revenue
FROM dbo.Customers c JOIN dbo.Orders o ON o.CustomerId = c.CustomerId
WHERE c.Country = 'IN'
GROUP BY c.Country;
SET STATISTICS IO OFF;
GO
-- > 3. ORDER BY a column with no supporting index: a Sort operator (and a memory grant)
-- @plan
SELECT TOP (5) OrderId, Total FROM dbo.Orders ORDER BY Total DESC, OrderId;
GO
-- > 4. The same TOP over the clustered key: no Sort, the index is already ordered
-- @plan
SELECT TOP (5) OrderId, Total FROM dbo.Orders ORDER BY OrderId DESC;
GO
-- > 5. Estimated vs actual: a predicate the optimizer cannot estimate well
-- @plan
DECLARE @from date = '2024-01-01';
SELECT COUNT(*) AS N FROM dbo.Orders WHERE DATEADD(day, 30, OrderDate) < @from;
GO

Output (SQL Server 2022 in Docker)

-- 1. One customer's orders: Index Seek + Key Lookup joined by Nested Loops
(40 rows, not shown)
IO: 'Orders'. Scan count 1, logical reads 133
PLAN (actual):
  Nested Loops (Inner Join)  est=40 actual=40
    Index Seek [Orders.IX_Orders_CustomerId]  est=40 actual=40
    Key Lookup (Clustered) [Orders.PK_Orders]  est=1 actual=40 executions=40
-- 2. Join all Indian customers to their orders: a Hash Match join on big inputs
Country | Orders | Revenue
--------+--------+-----------
IN      | 20000  | 9968160.00
(1 row)
IO: 'Workfile'. Scan count 0, logical reads 0
IO: 'Worktable'. Scan count 0, logical reads 0
IO: 'Orders'. Scan count 1, logical reads 721
IO: 'Customers'. Scan count 1, logical reads 30
PLAN (actual):
  Compute Scalar  est=1 actual=n/a
    Stream Aggregate (Aggregate)  est=1 actual=1
      Hash Match (Inner Join)  est=20000 actual=20000
        Clustered Index Scan [Customers.PK_Customers]  est=500 actual=500
        Clustered Index Scan [Orders.PK_Orders]  est=200000 actual=200000
-- 3. ORDER BY a column with no supporting index: a Sort operator (and a memory grant)
OrderId | Total
--------+-------
996     | 996.99
1993    | 996.99
2990    | 996.99
3987    | 996.99
4984    | 996.99
(5 rows)
PLAN (actual):
  Top  est=5 actual=5
    Parallelism (Gather Streams)  est=5 actual=5
      Sort (TopN Sort)  est=5 actual=40 executions=8
        Clustered Index Scan [Orders.PK_Orders]  est=200000 actual=200000 executions=8
-- 4. The same TOP over the clustered key: no Sort, the index is already ordered
OrderId | Total
--------+-------
200000  | 600.99
199999  | 599.99
199998  | 598.99
199997  | 597.99
199996  | 596.99
(5 rows)
PLAN (actual):
  Top  est=5 actual=5
    Clustered Index Scan [Orders.PK_Orders]  est=5 actual=5
-- 5. Estimated vs actual: a predicate the optimizer cannot estimate well
N
-
0
(1 row)
PLAN (actual):
  Compute Scalar  est=1 actual=n/a
    Stream Aggregate (Aggregate)  est=1 actual=1
      Clustered Index Scan [Orders.PK_Orders]  est=60000 actual=0

Code walkthrough

• 1. 40 rows for customer 42: Index Seek on IX_Orders_CustomerId (est 40, actual 40), then a Key Lookup executed 40 times to fetch OrderDate and Total, joined by Nested Loops. 133 logical reads.
• 2. The join of 500 Indian customers to their 20,000 orders uses a Hash Match: build on the 500 customers, probe with a scan of all 200,000 orders (721 reads). Seeking 500 times would not pay off at this size. The Workfile/Worktable lines with 0 reads are the hash join's tempdb structures, unused because the build fit in memory.
• 3. ORDER BY Total DESC with no index on Total: a Sort (TopN Sort) over a full scan, run on 8 threads (executions=8), then Gather Streams and Top. Each thread kept its own top 5, which is why the Sort shows actual 40 (8 × 5).
• 4. ORDER BY OrderId DESC: the clustered index already provides the order, so the plan is a Top over a backward scan that stops after 5 rows. No Sort.
• 5. DATEADD(day, 30, OrderDate) < @from hides the column inside an expression and compares it with a local variable: the optimizer guessed 60,000 rows (30% of 200,000) and the actual count was 0. A big estimate gap is the signal to investigate (Lessons 5 and 6).

Failure modes

• Reading only the estimated plan and missing that a Key Lookup ran 200,000 times.
• Trusting "cost %" as if it were measured time.
• Comparing the inner side of a Nested Loops by its per-execution estimate without multiplying by executions.
• Tuning the operator with the biggest arrow instead of finding where the estimate first goes wrong.
• Testing on a table with 100 rows: tiny tables produce plans that say nothing about production.

Interview angle

"How do you troubleshoot a slow query?" Get the actual plan and STATISTICS IO. Find the operators with the most reads and the first place where estimated and actual rows diverge. Explain the operator choice (Nested Loops for small outer inputs, Hash Match for big unsorted inputs, Sort only when no index provides the order), then fix the cause (index, sargability, statistics, parameter sniffing) and verify with the same measurement.

Practice questions

1. A Key Lookup shows est=1 actual=40 executions=40. Is the estimate wrong?

Answer: No. The inner-side estimate is per execution: 1 × 40 executions = 40 rows, exactly right.

2. Why is there no Sort in plan 4?

Answer: The clustered index on OrderId already returns rows in key order; the engine scans it backwards and stops after 5 rows.

3. What does Hash Match (Inner Join) need that Nested Loops does not?

Answer: A memory grant for the hash table, usually built on the estimated smaller input. A spill can require partitioning and extra I/O rather than one pass.

Revision checklist

• Actual plan + STATISTICS IO before any tuning.
• Seek vs scan vs lookup; watch lookup executions.
• Nested Loops (small outer), Hash Match (big, unsorted), Merge (sorted inputs).
• Sort and Hash need memory; can spill.
• Compare est × executions with actual; find the first big gap.
• Cost % is a model, not a measurement.

Related on TechInterviewNotes

• Free cheat sheet: SQL Server Indexes and Isolation Levels
• Course: EF Core Performance and Behavior (how to capture the SQL that EF Core sends, so you can get its plan)
• Mock interview: EF Core and SQL

References

• Display an actual execution plan
• Showplan logical and physical operators reference
• SET STATISTICS IO
• SET STATISTICS XML