The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

The SQL Booklet · Execution plans

How to read a SQL Server execution plan.

Thirty articles that move from the first graphical tree to a measured, reproducible analysis limited by the evidence.

Start the pathView the seven stages

A complete reading path

The execution plan strategy.

A query describes the result it needs. SQL Server evaluates alternatives and chooses a strategy for obtaining it. The plan represents that strategy through operators, connections, estimates, and properties. Reading it helps reconstruct what the engine did and formulate a check. Metrics complete the diagnosis and support any change.

The path organizes orientation, operators, tools, and measurement. Each icon is studied within the flow, and every Scan, warning, or suggestion is interpreted in context.

Before changing the query

Four questions for analyzing the plan.

  1. What behavior did we observe?The symptom, expected result, and execution conditions come before the plan.
  2. What strategy did SQL Server choose?The operators show how it accessed, transformed, and combined rows.
  3. What evidence is missing?Reads, CPU, duration, actual rows, and repeated runs complete the observation.
  4. How will we test a hypothesis?Change one variable, repeat the measurement, and review side effects.

Reproducible evidence

Screenshots and reproducible tests.

When an article depends on SSMS, it shows the query or prerequisite, names the option to enable, and points to the tab, property, or operator that should appear. Values that depend on the machine, data, or version are identified as observations from that test.

Real screenshots are cropped to retain useful context and remove paths, connections, and internal data. Conceptual diagrams represent relationships absent from the interface.

Read the editorial methodology

The path

Seven stages and thirty articles.

The first units build the vocabulary. The final units combine the same pieces in comparisons and complete cases.

Stage 1

Understand what you are looking at

Build the mental model, distinguish plan modes, and learn to traverse the tree before judging an operator as good or bad.

  1. 01What an execution plan is for
  2. 02Estimated plans, actual plans, and Live Query Statistics
  3. 03How an execution plan tree is organized
  4. 04Arrows, properties, and execution counts
  5. 05Operator map: what role each node plays
Stage 2

Interpret plan figures

Relate estimates, observed rows, access methods, repeated work, estimated cost, and measured time.

  1. 06Estimated rows and actual rows
  2. 07Index Seek, Index Scan, and Table Scan
  3. 08Key Lookup and repeated work
Stage 3

Recognize frequent operators

Explain what transformation, join, ordering, aggregation, materialization, and parallelism operators receive and produce.

  1. 09Filter, Compute Scalar, and Top
  2. 10Nested Loops, Hash Match, and Merge Join
  3. 11Sort, Segment, and Sequence Project
  4. 12Stream Aggregate and Hash Match Aggregate
  5. 13Spool: materialization and reuse
  6. 14Parallelism: distributing, repartitioning, and gathering rows
  7. 15Execution plan warnings that require investigation
Stage 4

Use plan tools

Navigate properties, Showplan XML, and SSMS analysis to locate evidence and evaluate its suggestions.

  1. 16Actions available in the SSMS graphical plan
  2. 17How Showplan XML is organized
  3. 18Walk through Analyze Actual Execution Plan
Stage 5

Measure and compare

Add reads, CPU, duration, baselines, plan comparison, and Query Store to determine whether a difference is stable.

  1. 19Reads with SET STATISTICS IO
  2. 20CPU and elapsed time with SET STATISTICS TIME
  3. 21Build a repeatable baseline
  4. 22Save and compare two plans
  5. 23History and regressions with Query Store
Stage 6

Evaluate automated help and indexes

Turn scenarios and suggestions into hypotheses, then review existing structures, writes, and results before keeping an index.

  1. 24Turn an Analyze Actual Execution Plan scenario into a hypothesis
  2. 25Read a Missing Index suggestion in an execution plan
  3. 26Understand the CREATE INDEX generated by SSMS
  4. 27Decide, create, and verify an index
Stage 7

Apply the method

Combine symptom, query, plan, metrics, hypothesis, change, and verification in a reproducible analysis.

  1. 28From a slow query to a testable hypothesis
  2. 29Evaluate a change without optimizing for one execution
  3. 30Guided analysis of a reproducible business query