Reading execution plans — operators and join algorithms — SQL Server Internals — How the Database Engine Really Works

6. Reading execution plans — operators and join algorithms

Advanced★ Must knowLesson 6 of 11

Plans are read right to left (data flow) and top to bottom; each arrow's thickness shows the row count. Compare estimated vs actual rows — big differences point to statistics/estimation problems.

Operator	Meaning
Clustered Index Seek / Index Seek	Navigated the B-tree to specific keys — good for selective predicates
Clustered Index Scan / Table Scan	Read the whole table/index — fine for small tables or when most rows are needed
Key Lookup / RID Lookup	Fetch extra columns per row — fix with a covering index if many rows
Nested Loops join	For each outer row, look up matching inner rows — best when outer input is small and inner side has an index
Merge Join	Both inputs sorted on the join key — stream and match; efficient for large sorted inputs
Hash Match join/aggregate	Build a hash table on the smaller input, probe with the other — good for large unsorted inputs; needs a memory grant
Sort	Orders rows — needs memory; spills to tempdb if under-estimated
Stream Aggregate / Hash Aggregate	GROUP BY on sorted / unsorted input
Compute Scalar, Filter	Expressions; filtering after reading (a late filter is a hint of non-SARGable predicates)
Spool	Stores intermediate results for reuse
Parallelism (Gather/Repartition Streams)	Parallel plan exchanges

Warnings to look for: implicit conversion warnings, spills to tempdb (Sort/Hash warnings), missing index suggestions (evaluate, don't blindly create), huge estimated vs actual gaps, key lookups executed thousands of times.

sql

SET STATISTICS IO, TIME ON;          -- logical reads per table + CPU/elapsed time
SELECT o.OrderId, o.Total FROM dbo.Orders o WHERE o.CustomerId = 42 ORDER BY o.OrderDate DESC;
-- Messages tab: Table 'Orders'. Scan count 1, logical reads 4 ...  → compare before/after an index