How SQL Server Executes a Query: Read the Plan as Data Flow

The query optimizer chooses an execution plan. The execution engine carries it out using physical operators that read, join, sort, group, exchange and modify rows.

Understanding operators helps you move beyond simplistic rules such as seeks are good and scans are bad. The better question is why an operator was appropriate for the estimated amount, order and shape of the data.

1. Read an execution plan as a flow of rows

Locate qualifying orders
        ↓
Locate or read customers
        ↓
Join matching rows
        ↓
Return selected columns

In a traditional graphical plan, data generally flows from right to left. For each operator, ask what it receives, how many rows were estimated and actually arrived, whether it needs order or memory, how many times it executed, and what it passes onward. An inexpensive operation can become costly when repeated a million times.

2. A scan reads a structure

A Table Scan reads a heap, a Clustered Index Scan reads the clustered index, and an Index Scan normally reads a nonclustered index. A scan can be appropriate when most rows are required, the structure is small, no selective predicate exists, or a narrow covering index is cheaper than repeated seeks and lookups.

SELECT
    AddressId,
    City,
    StateProvinceId
FROM Person.Address;

Code-review question

How much of this structure must SQL Server read, and is that reasonable for the requested result?

A clustered-index scan also does not guarantee presentation order. Request required ordering explicitly with ORDER BY and inspect the operator's Ordered property when order matters to later plan operations.

3. A seek navigates to a useful key or range

SELECT
    AddressId,
    City
FROM Person.Address
WHERE AddressId BETWEEN 10000 AND 20000;

A seek navigates an index to the beginning of a key or range and can continue reading until the range ends. It may therefore touch many rows and pages. Inspect seek predicates, residual predicates, actual rows, logical reads and execution count rather than treating the seek icon as proof of efficiency.

A seek predicate helps navigate the index. A residual predicate is checked against rows after navigation. Residual filtering may mean the operator reads considerably more rows than it returns.

4. A lookup retrieves columns missing from an index

Nonclustered Index Seek
Find qualifying row locators
        ↓
Key Lookup or RID Lookup
Retrieve missing columns

A lookup can be excellent for a few qualifying rows. It becomes risky when the driving operator returns many rows and the lookup executes once per row. At some point, one scan may be cheaper than hundreds of thousands of navigations.

Review lookup execution count, driving rows, retrieved columns and logical reads before deciding whether a covering index is justified. Adding every output column to an index increases storage and write cost.

5. Some operators stream while others must prepare

Streaming operators can often pass rows progressively. Blocking operators must consume some or all input first. Sort prepares ordered rows, while hash operations build an in-memory hash table before or during later processing.

Sorting and hashing are essential, but they delay initial output, request memory and may spill work to tempdb when the memory grant is insufficient. Ask how many rows are being prepared, whether estimates were accurate and whether the operator spilled.

6. Stream Aggregate benefits from ordered input

SELECT
    CustomerId,
    COUNT(*) AS OrderCount
FROM Sales.Orders
GROUP BY CustomerId;

Stream Aggregate works efficiently when equal grouping keys arrive together. Useful order may come from an index, Merge Join, Sort or another order-preserving operator. The aggregate is not automatically cheap if SQL Server first performs an expensive sort.

7. Hash Aggregate groups unordered input

Hash Match (Aggregate) does not require ordered input. It calculates a hash from each grouping key and updates an in-memory entry for that group. This can avoid sorting, but underestimated rows or groups can produce an inadequate memory grant and tempdb spills.

DISTINCT also needs a physical algorithm, such as Stream Aggregate, Hash Aggregate or Distinct Sort. Do not use DISTINCT to hide a join that accidentally creates duplicates.

8. Nested Loops Join repeats an inner operation

For each row from the outer input
        ↓
Run the inner operation
        ↓
Return matching rows

Nested Loops often suits a small outer input and an efficiently indexed inner input. It can become expensive when the outer input is far larger than estimated because the inner seek or lookup repeats for every outer row. Review outer rows, inner execution count and cumulative reads.

9. Merge Join walks through ordered inputs

Merge Join can efficiently combine larger inputs ordered by their join keys. If indexes already provide the order, it may be attractive. If both inputs need expensive sorts first, the benefit can disappear. Many-to-many matches may also require additional handling.

10. Hash Join builds and probes

Smaller build input
        ↓
Create hash table
        ↓
Larger probe input
        ↓
Hash join key and find matches

Hash Join often suits large unsorted equality inputs where no efficient repeated lookup exists. It needs memory, and inaccurate estimates can cause spills. Inspect build and probe rows, memory grant, spill warnings and whether earlier filtering could reduce either input.

Nested Loops → small outer input and efficient inner lookup
Merge Join → useful ordered inputs
Hash Join → large unsorted inputs and sufficient memory

No join algorithm is universally best. If a chosen join behaves badly, investigate estimates, parameter values, statistics, join-key types, indexes and excessive upstream rows before blaming the icon.

11. Parallelism divides work between workers

Large input
    ↓
Distribute or repartition rows
    ↓
Worker 1   Worker 2   Worker 3   Worker 4
    ↓          ↓          ↓          ↓
Gather results

Exchange operators distribute, repartition or gather rows between workers. Parallelism can reduce elapsed time for expensive work but adds coordination, memory and CPU costs. Data skew can leave one worker doing most of the work. Review both elapsed and total CPU time rather than treating parallelism as automatically faster.

12. Updates contain a read phase and a write phase

UPDATE Sales.Orders
SET Status = 'Archived'
WHERE OrderDate < '2020-01-01';

SQL Server first identifies qualifying rows and then modifies table and index structures. This is why every additional index has a write cost even if it helps selected reads.

13. Halloween protection prevents repeated updates

UPDATE dbo.Employee
SET Salary = Salary * 1.10
WHERE Salary < 25000;

If rows are read through an index on the value being changed, an updated row could move and appear again later in the scan. SQL Server can introduce a spool or another blocking boundary to finish identifying rows before modifying them. The extra work protects correctness.

A practical execution-operator checklist

• ✓Access: Is SQL Server scanning or navigating to a key or range?
• ✓Coverage: Does the selected index contain the required columns?
• ✓Repetition: How many times does a seek, lookup or inner join operation execute?
• ✓Row flow: Where do estimated and actual rows first diverge?
• ✓Order: Was useful order already available, or did SQL Server sort?
• ✓Blocking: Which operators must consume input before producing rows?
• ✓Memory: Were sort and hash memory grants appropriate?
• ✓Spills: Did work move to tempdb?
• ✓Join shape: Why did the selected join suit the estimates?
• ✓Parallelism: Was work distributed evenly and what coordination was added?
• ✓Updates: Which table and index structures must be maintained?
• ✓Correctness: Is a spool protecting update semantics?
• ✓Evidence: What do reads, CPU, elapsed time and execution counts show?

The operator model to remember

Scan → read a structure
Seek → navigate to a key or range
Lookup → retrieve missing columns
Stream Aggregate → group ordered input
Hash Aggregate → group unordered input using memory
Nested Loops → repeat inner work for outer rows
Merge Join → walk ordered inputs
Hash Join → build and probe
Exchange → move rows between workers
Spool → store rows for reuse or correctness

A seek can be expensive, a scan can be correct, a hash can be efficient, a sort can be necessary and a spool can protect correctness. Read the plan as a flow of data and SQL Server's choices become easier to understand.