Execution Plans

▶ DRILL

What a Plan Is

A SQL query is a declaration — it says what you want, not how to get it. The execution plan is the how: a tree of physical operators that SQL Server constructs after optimization and executes every time the query runs. Each operator takes row streams from its children, transforms them, and passes results up to its parent. The root operator delivers the final result set.

Plans come in two flavors. The estimated plan (Ctrl+L in SSMS, or SET SHOWPLAN_XML ON) is produced without executing the query — useful when the query takes too long to run during diagnosis. The actual plan (Ctrl+M, or SET STATISTICS XML ON) runs the query and records real row counts alongside the estimates. The actual plan is almost always more useful: the gap between estimated and actual rows is where most performance problems reveal themselves.

Reading the Tree

Plans are read right-to-left, bottom-up. Data originates at the leaves on the right — table scans, index seeks — and flows left through join, filter, and compute operators until it reaches the root. The arrows connecting operators carry row streams; thicker arrows mean more rows. The percentage on each operator is its estimated share of total query cost.

Each operator node in SSMS shows four numbers on hover: estimated rows, actual rows, estimated cost, and actual CPU and I/O cost. The most diagnostic comparison is estimated vs actual rows. When they agree, the optimizer had good information. When they diverge by 10× or more, the optimizer built the rest of the plan — join types, memory grants, parallelism — for the wrong data volume. That divergence, and the operator where it first appears, is the root cause.

Five Operators to Know First

SQL Server has dozens of physical operators. In practice, OLTP query tuning comes back to the same five again and again.

Clustered Index Scan
Reads every page of the table in key order. Appears when no usable seek predicate exists, or when selectivity is low enough that a full scan is cheaper than targeted random reads. Not always wrong — sequential I/O beats random beyond a certain threshold. It is a problem when you expected a seek.
Index Seek
Traverses the B+ tree from root to leaf using a predicate that can be resolved to a key range. One page read per tree level plus the matching leaf pages — O(log n + matches). The target of every tuning session. Whether the optimizer chooses a seek depends entirely on whether the predicate is SARGable.
Key Lookup
When a nonclustered index seek finds a matching row locator, SQL Server must seek the clustered index again to fetch columns not stored in the nonclustered index. One random I/O per matched row. Harmless for 50 rows; catastrophic for 50,000. Fixed with INCLUDE columns.
Nested Loops
For each row on the outer (upper) input, probe the inner (lower) input. Cost scales as outer × inner. The dominant join operator in OLTP: fast when the inner side is small and indexed, catastrophic when it isn't. Nested loops against a scan on the inner side is O(n²).
Hash Match
Builds a hash table on the smaller input, probes it with the larger. Appears when nested loops would be too expensive — large, unsorted inputs with no useful index. Requires a memory grant sized to the build input. An undersized grant spills to tempdb. A hash join in an OLTP plan often means a missing index or a badly wrong cardinality estimate.

Execution Plan WalkthroughStep through the physical plan for a two-table join — see which operator executes when, and compare estimated vs actual row counts at each step.

SELECT so.sales_order_id,
       so.sales_order_date,
       c.customer_name
FROM   dbo.sales_order so
  JOIN dbo.customer    c  ON c.customer_id = so.customer_id
WHERE  c.customer_name = 'Eric Cartman'

OVERVIEW

Overview

Read right-to-left: data sources are on the right; the root SELECT is on the left. Data flows left along arrows — thicker arrows carry more rows. Cost percentages show each operator's estimated share of total query cost.

SARGable Predicates

SARG stands for Search ARGument. A predicate is SARGable if the storage engine can translate it into a start key and an end key on an index — use it to drive an index seek rather than a scan. The rule is simple:the indexed column must appear alone on one side of the comparison, in its stored form, with no transformation applied.

The most common violation is wrapping the column in a function:YEAR(sales_order_date), CAST(col AS date),DATEDIFF(..., col, ...), ISNULL(col, 0). Each transforms the stored value before comparison. The optimizer sees an expression, not a column — it cannot determine which index keys satisfy the expression without evaluating it against every row.

The fix is always the same: move the computation to the other sideof the comparison, against the literal or parameter, leaving the column bare.YEAR(col) = 2022 becomes a range on col.CAST(col AS date) = '2022-03-15' becomes a half-open day range. The result is semantically identical; the execution plan is not.

Two other common non-SARGable patterns: a leading wildcardin LIKE ('%text%') gives the B+ tree no start key; and implicit type conversion, where a literal of the wrong type causes SQL Server to silently wrap the column in aCONVERT — same result, harder to spot without reading the operator properties in the plan.

SARGable Predicate ExplorerFour patterns — toggle between non-SARGable and SARGable versions to see the operator and page count change.

Wrapping an indexed column in a scalar function hides it from the optimizer. The index is keyed on the raw datetime value; YEAR() transforms that value before comparison, so the optimizer cannot determine which key range to seek to.

SELECT COUNT(*)
FROM   dbo.sales_order
WHERE  YEAR(sales_order_date) = 2022

INDEX SCANix_sales_order_date ON dbo.sales_order (sales_order_date)

PAGES READ

100of 100

YEAR() is evaluated against every stored value. The optimizer cannot map this to key boundaries — it reads all 100 pages and applies the function row by row.

Putting It Together

The plan tells you what happened. The estimated-vs-actual row count tells you where the optimizer's assumptions broke down. SARGable predicates determine whether the index is used at all. These three — reading the operator tree, spotting cardinality divergence, and writing predicates the index can consume — cover the majority of SQL Server query performance work.

What they do not cover: parallel plans, memory grants and tempdb spills, the full operator catalog, or plan forcing with query hints. Those come later. For now, the ability to open a plan, find where estimated and actual rows diverge, and rewrite a non-SARGable predicate will resolve a large fraction of the queries that land on your desk at 3 AM.