What is a query plan?

What is a query plan?

SQL is a declarative language. You describe the result you want - these columns, from these tables, matching these conditions - and you say nothing at all about how to produce it. Something has to turn that description into actual work: which table to read first, which index to use, how to match rows across tables, whether to sort. That translation is the job of the query optimizer, and its output is the query plan.

The plan is a tree of physical operators. Each operator does one narrow thing - seek an index, join two streams, sort rows, compute an aggregate - and pulls the rows it needs from its children, one at a time, on demand. Execution starts at the root asking for a row, and that request cascades down the tree until a leaf operator actually touches data.

You do not write the query the database runs. You write a description of the answer, and the optimizer writes the query.- what the plan actually is

This has a consequence people find uncomfortable: a query has no single performance characteristic. The same text can be fast this morning and slow this afternoon, on identical hardware and identical data, because the plan changed. Parameters, statistics, row counts, available memory, and server settings all feed the choice. Tuning a query without looking at its plan is tuning something you cannot see.

The vocabulary here is SQL Server's, and so are the tools. PostgreSQL calls the same thing an execution plan and exposes it through EXPLAIN ANALYZE; the operator names differ, the reading technique does not.

The optimizer's job

SQL Server's optimizer is cost-based. It generates candidate plans by applying transformation rules to your query tree, estimates what each one would cost, and returns the cheapest one it managed to find. Three words in that sentence do most of the damage when people misunderstand them.

Estimates. The optimizer never looks at your data while optimizing. It looks at statistics about your data - a summary described in section 06 - and reasons from those. Every decision in the plan follows from a predicted row count, and a prediction can be wrong.

Cheapest. Cost is a unitless number produced by a fixed model of how expensive I/O and CPU are. Those constants were calibrated on a developer's machine at Microsoft in the 1990s and have essentially never changed, precisely so that plan choices stay predictable across versions. A cost of 47 is meaningfully more than a cost of 0.03. Neither one is a number of seconds, and no plan cost predicts a duration on your hardware.

Managed to find. The search space is enormous - join order alone grows factorially - so the optimizer does not explore it exhaustively. It works in stages against a time budget and stops as soon as it has something good enough, a design goal usually summarised as "a good plan, fast". Simple queries skip most of this through a trivial plan. This is why a hand-written rewrite occasionally beats the optimizer: not because it reasoned badly, but because it stopped searching.

One practical corollary that catches almost everybody: the cost percentages shown on each operator are estimates too, even in an actual execution plan. They are derived from the estimated row counts, not from measured time. When an estimate is wrong, the percentage attached to it is wrong in the same direction. The operator labelled "cost 94%" is a hypothesis about where the time went, not a measurement of it.

Estimated vs actual

There are two plans for any query, and knowing which one you are looking at matters.

The estimated plan is the compiler's output. Nothing runs. You get the operator tree and every prediction the optimizer made, which is enough to spot a missing index or an implicit conversion, and it is safe to pull for a query you cannot afford to execute on production.

The actual plan is the same tree with runtime counters attached: rows really produced per operator, how many times each operator executed, memory granted versus used, whether anything spilled to tempdb, and which waits the query hit. This is what you tune with.

That last one is worth remembering. A query that has been running for forty minutes will never hand you an actual plan, because actual plans arrive when the query finishes. sys.dm_exec_query_statistics_xml reads the live plan out of a running session with its counters so far, which is often the only way to see what a runaway query is actually doing.

Once you have an actual plan, the highest-value thing in it is not the cost percentages. It is the comparison, per operator, between estimated rows and actual rows. Every structural decision - which join algorithm, how much memory to request, whether to parallelize, whether to seek and look up or just scan - was chosen from the estimate. When the estimate is off by three orders of magnitude, the plan is not a bad plan; it is a good plan for a query you did not run.

Read those gaps from the bottom of the tree upward and find the first operator where the divergence appears. That one is the origin. Everything above it inherited the bad number and is only a symptom.

How to read one

A graphical plan in SSMS is drawn right to left: the leaf operators that touch data sit on the right, and rows flow leftward and upward toward the SELECT at the far left. Logically the tree runs the other way, with each parent asking its children for rows, which is why the same plan reads sensibly in both directions once you are used to it.

Things worth looking at, roughly in the order they pay off:

• Arrow thickness. The engine draws each arrow in proportion to the rows flowing through it. A thin pipe that suddenly becomes a fire hose is where your query stopped being cheap. In an actual plan those widths reflect real row counts, so a fat arrow next to a small estimate is the diagnosis and the evidence in one glance.
• Warnings. The yellow triangle is never decorative. It flags implicit conversions, a join with no predicate (an accidental cross join), a sort or hash that spilled to tempdb because its memory grant was too small, an excessive grant that starved everything else, and columns with no statistics.
• Number of Executions. Hover the inner side of a nested loop. An operator costing 3 milliseconds per execution is irrelevant at one execution and is your entire query at 184,000. Always multiply it out; this single number explains most "but the plan looks fine" conversations.
• Actual vs estimated rows, per operator, bottom up. Section 03.
• Scans where a seek was possible, and lookups with a high execution count. Both are covered in What is an index?, and both are usually an indexing problem rather than a query problem.

What not to do: sort the operators by cost percentage and start with the biggest. That number is an estimate (section 02), so on exactly the queries you most need to fix - the ones with broken estimates - it points at the wrong operator. Use it to break ties after the row counts have told you where to look.

The operators that matter

There are dozens. About ten account for nearly everything you will meet in an OLTP system.

Getting at the data

A Clustered Index Scan or Table Scan reads everything. This is not automatically a defect: for a small lookup table, or a query that genuinely needs most of the rows, reading straight through is the cheapest thing available. It is a defect when it appears under a selective WHERE clause.

An Index Seek navigates the B-tree to a starting point. Also not automatically good - check the Seek Predicate versus the Predicate in the operator's properties. A seek that lands at the start of the index and then range-scans four million rows while filtering them with a residual predicate is a scan wearing a better name.

A Key Lookup or RID Lookup is the trip back to the table for columns the non-clustered index did not carry. One is nothing; a hundred thousand is the query.

Joining

Nested Loops: for every row from the outer input, probe the inner input. Correct and extremely fast when the outer side is small and the inner side is indexed on the join column. It is also the operator behind most catastrophic plans, because its cost scales with the outer row count - which is exactly the number the optimizer got wrong in the cover diagram above.

Merge Join: both inputs arrive sorted on the join key and the engine walks them together in a single pass. Beautifully cheap when the sort comes free from an index. When it does not, the optimizer inserts a Sort, and the sort is where the cost went.

Hash Match: build an in-memory hash table from the smaller input, then probe it with the larger one. The right answer for big unsorted sets and the standard operator in analytical queries. It requires a memory grant decided at compile time from the estimates; if the estimate was low the grant is small, the hash table does not fit, and the operator spills to tempdb. That is the cliff edge where a query goes from two seconds to four minutes without any change to the data.

Everything else you will actually see

• Sort - blocking (it cannot emit a row until it has seen them all) and memory-hungry. Free if an index already provides the order.
• Stream Aggregate needs sorted input and is cheap; Hash Match (Aggregate) does not and is not.
• Spool - the optimizer caching an intermediate result in tempdb because it expects to reuse it. Occasionally clever, more often a sign that the query is fighting the schema.
• Parallelism (Exchange) operators - Distribute, Repartition, and Gather Streams. A parallel plan is not automatically faster: thread skew and exchange overhead can lose more than the parallelism gains. The default cost threshold for parallelism of 5 is a 1990s value that most shops raise substantially.

Statistics and estimation

Everything above rests on the optimizer's row estimates, and those come from statistics objects. A statistic on a column is remarkably small: a histogram of at most 200 steps describing the distribution of the leading column, a density vector summarising how unique the combinations are, and the total row count. That is the entire picture the optimizer has of a billion-row table.

SQL Server creates and updates them automatically by default. Modern versions use a dynamic threshold that scales roughly with the square root of the table size, which fixed a long-standing problem: the old rule of "20% of rows changed" meant very large tables would go years without a refresh.

Estimation goes wrong in a handful of specific, recognisable ways:

• Correlated columns. The model assumes predicates are independent and multiplies their selectivities. WHERE City = 'Chicago' AND State = 'IL' gets estimated as if living in Chicago said nothing about living in Illinois, so the estimate collapses toward one row and you get a nested loop over a large table.
• Values the optimizer cannot see. A local variable is not known at compile time, so instead of a histogram lookup you get an average based on density. The same query with a literal, a parameter, and a local variable can produce three different plans.
• Table variables and multi-statement functions. A table variable historically estimated at exactly one row regardless of contents; deferred compilation in SQL Server 2019 and later improved this, but on older compatibility levels it remains a reliable way to get a nested loop over ten million rows.
• The ascending key problem. Statistics were refreshed last night; today's orders are all beyond the histogram's final step. A query for "today" estimates almost nothing and plans accordingly.
• Expressions over columns. Wrap a column in a function and the histogram no longer describes what you are comparing, so the optimizer falls back to a fixed guess. The same rewrite that restores an index seek also restores the estimate.

One upgrade note worth carrying: the cardinality estimator was rewritten in SQL Server 2014, and the old and new models make different assumptions about exactly the cases above. A database that got slower after a compatibility-level change is a common and well-documented outcome. The modern remedy is Query Store rather than a global downgrade - keep the new estimator, and force the specific plans that regressed.

Cache and parameter sniffing

Optimization is expensive, so SQL Server caches the finished plan and reuses it for matching query text. This is the right default; recompiling every statement of a busy OLTP workload would burn a large fraction of the server's CPU on planning rather than working.

To build that first plan the optimizer looks at the parameter values it was handed and tailors the plan to them. That is parameter sniffing, and almost all of the time it is exactly what you want - a plan chosen from real values beats a plan chosen from an average.

It becomes a problem when the data is skewed. One customer has two million orders; every other customer has twelve. The plan that gets cached is whichever shape ran first after the last restart, index rebuild, or statistics update:

• Compiled for the small customer, the plan seeks and does key lookups. Run it for the large one and it performs two million lookups.
• Compiled for the large customer, the plan scans and hash joins with a large memory grant. Run it for the small one and it reads the whole table to return twelve rows.

This is the mechanism behind "it is slow for one client only", and behind the classic "we restarted the app and now it is slow" - nothing changed except which parameter happened to compile first.

The levers, in the order worth trying them:

• Fix the indexes. If a covering index makes both shapes cheap, the sensitivity disappears and no hint is needed. This is the only fix that does not need maintaining.
• OPTION (RECOMPILE) on the one statement that needs it. A fresh plan every execution, tailored to the real parameters, at the price of compiling every time. Excellent for a report that runs twice an hour, wrong for a statement running two thousand times a second.
• OPTIMIZE FOR UNKNOWN or an explicit OPTIMIZE FOR (@id = ...). Deliberately choose the average plan, or a specific known-good one, for everybody. Predictably mediocre beats occasionally catastrophic.
• Force a plan in Query Store. When you have a known-good plan and need it back today.

SQL Server 2022 added Parameter Sensitive Plan optimization, which caches several plan variants for a statement whose predicate is skewed and dispatches between them. It reduces how often you reach for the levers above; it does not remove the need to understand why they exist.

Whatever the cause, Query Store is the tool for "it was fast yesterday". It keeps plans and their runtime statistics over time, which turns an argument about whether the query regressed into a chart showing when it did and which plan replaced which.

A workflow for a slow query

A repeatable loop beats intuition, especially under pressure:

• 1. Capture the actual plan alongside SET STATISTICS IO, TIME ON. Note logical reads, CPU time, and elapsed time. CPU far below elapsed means the query is waiting on something, not computing.
• 2. Find the fattest arrow, then walk down to the first operator whose estimate diverges badly from actual. Start your investigation there, not at the highest cost percentage.
• 3. Classify the problem. Is it reading too much (scan, lookup, missing index)? Estimating badly (stale statistics, a local variable, correlated predicates)? Or waiting (blocking, a tempdb spill, an inadequate memory grant)? The three have completely different fixes and the plan tells you which one you have.
• 4. Take the cheap wins first. An implicit conversion warning, a non-SARGable predicate, a spill, a lookup running six figures of executions. These are usually a one-line change.
• 5. Change exactly one thing, then re-measure logical reads. Not the clock - a warm cache will happily tell you a bad plan got faster.
• 6. Validate at production shape. Production data volume, production parameter skew, production concurrency. A plan tuned against a 10,000-row development copy carries no information about a 40-million-row table.
• 7. Leave a note. A hint or a forced plan without a comment explaining the parameter distribution that justified it will be removed by someone next year, and rediscovered the hard way.

The reason to learn this is not that plans are interesting. It is that query performance is one of the few areas of engineering where the system will tell you exactly what it did and exactly what it expected, if you ask. Once you can read the plan, "the database is slow" stops being a complaint and becomes a specific operator, a specific estimate, and a specific thing to change.