A beginner’s guide to SQL Server execution plans

An execution plan is SQL Server’s record of how it intends to run a query, including where it reads data, how rows move between operators, and what joins and filters it uses. It is not a performance score. Estimated plans show the optimiser’s expectations before execution; actual plans add row counts, elapsed times and other runtime measurements. Learning to read that map is one of the most useful skills a developer can build.

This guide follows a simple Orders and Customers query and shows how to move from a suspicious-looking plan to a measured diagnosis. It suits developers who can write basic T-SQL but have never learned the visual language of a plan. Across Sydney, Melbourne and Brisbane, Australian teams commonly look after a mixture of on-premises SQL Server, managed instances and Azure SQL workloads, often supporting finance, health or logistics systems; the operator names stay the same, even when the deployment model changes.

What the two plan types mean

SQL Server can produce an estimated execution plan, an actual execution plan and, in some circumstances, a live execution plan. The estimated plan is produced from the query’s structure, available statistics and the parameter values considered when the statement was compiled. The actual plan is generated by running the statement and records what happened, including the number of rows entering each operator. A live plan updates while the query executes, which can be useful for long-running commands.

The percentages attached to operators are relative cost estimates, not measurements of milliseconds. A plan showing a 90 per cent cost for one operator does not mean that operator consumed 90 per cent of the query’s time. Costs help the optimiser compare possible plans, while an actual plan tells you where time, CPU and data movement occurred. When investigating a slow query, use estimated costs to understand the optimiser’s reasoning and actual measurements to decide what to change.

An estimated plan is safe to inspect without running the statement, whereas an actual plan executes it. In SQL Server Management Studio, choose “Include Actual Execution Plan” before running a query, or use the MSSQL extension for Visual Studio Code. Take care with statements that modify data because capturing an actual plan also performs those modifications. For production diagnostics, favour a representative read-only query, Query Store history or an Extended Events session. That combination of database and application thinking also comes through in Steven’s background, which sets out the full-stack experience behind Kilt and Code.

Following data through common operators

A Table Scan reads every row in a table before filters are applied. It is often represented at the left of a plan, with a filter appearing as a separate operator above it. An Index Scan also reads many rows, but it traverses an index rather than the full table. Neither operation is automatically wrong: a scan can be efficient when it needs most of a small table or when reading pages sequentially is cheaper than many random reads.

An Index Seek uses a B-tree to locate qualifying rows. You will normally see a seek predicate such as CustomerId = 42, followed by a residual predicate for conditions the index cannot evaluate directly. If the index does not contain every selected column, SQL Server may add a Key Lookup to retrieve the missing values from the clustered index. A seek paired with hundreds of thousands of lookups might still perform poorly. For a Brisbane courier company retrieving one order, a seek makes sense; retrieving most orders from the same table may justify a scan.

Join operators show how matching rows are combined. A Nested Loops operator can be efficient when the outer input contains only a few rows. A Hash Match often suits larger, unordered inputs, while a Merge Join requires suitably sorted inputs. A quick reckon about which join “should” win is less reliable than checking the actual row counts. Other operators, including Sort, Aggregate and Parallelism, may introduce memory use or spills. When a query feels as though it has been running all arvo, these secondary costs can explain the delay even when the first scan looks reasonable.

Finding where estimates go wrong

Cardinality is the estimated number of rows an operator will pass onward. Its accuracy strongly influences join order, index selection and memory allocation. SQL Server displays estimated rows beside actual rows in an actual plan, so look for a large difference at each stage. An estimate of 20 rows followed by an actual count of 80,000 is a strong signal that the optimiser chose the rest of the plan under faulty assumptions. The cause might be stale statistics, correlated columns, an unfamiliar parameter value or a mismatch between the statistics and the current data distribution.

SQL Server builds statistics from sampled data and stores a histogram describing how values are distributed. Automatic statistics updates help, but they do not cover every filtering pattern or guarantee that a sample will represent a skewed table. Check the modification counter and last update date before changing an index. Sometimes the better response is updating or improving statistics, although running UPDATE STATISTICS on a schedule for every table is rarely a sensible universal policy. Test the statistics change and measure it in the same way as any other modification.

Parameter values can also make the optimiser choose a plan that suits one workload poorly for another. This behaviour is commonly called parameter sniffing. An estimated plan compiled for a selective customer may perform badly when reused for a large customer, while the reverse can also happen. Recompilation, Query Store plan variation tracking and revised parameter handling can help, but each has different operational costs. Many Australian organisations place workloads in Australia East, Australia Southeast or Australia Central to meet data-residency and latency expectations, so reproducing realistic parameter values and network conditions matters when comparing plans.

Measuring the expensive parts

Start with the operator showing the largest difference between estimated and actual rows, not necessarily the one with the largest percentage of estimated cost. An actual plan also exposes elapsed time, CPU time, logical reads and other runtime information. These values need careful interpretation: operator timings are aggregated across threads, and waits for locks or I/O may occur outside the operator itself. The overall statement duration remains the strongest measure of user impact, while logical reads often provide a useful indication of the work performed.

Before adding an index, determine what problem it is expected to solve. A covering nonclustered index may remove a costly Key Lookup, but it also consumes storage and adds write overhead. A more selective query might turn a scan into a seek, although a seek across scattered pages can still generate more physical I/O. Rewriting a predicate, reducing selected columns or changing the order in which rows are processed can sometimes provide a larger benefit than another index.

Use several tools to confirm the diagnosis. SET STATISTICS IO reveals logical and physical reads, while SET STATISTICS TIME reports CPU and elapsed time. Query Store helps compare repeated executions and identify meaningful plan changes, while Extended Events can capture waits and other diagnostics with less overhead. For another perspective on disciplined software diagnosis, Oscar Sanchez’s practical guidance complements the habit of working from evidence rather than folklore.

A repeatable tuning checklist

Reliable tuning follows the same sequence: reproduce the problem, capture an actual plan, identify the first major estimate or cost discrepancy, form one hypothesis, make one measured change and compare the results. Keep the query, parameter values and surrounding workload stable wherever possible. That discipline prevents a faster test from being mistaken for a genuine improvement.

Treat every tuning recommendation as an experiment rather than a rule copied from another system. A plan that performs well with 500 rows may fail with five million, and a network-heavy application can expose different costs from a local benchmark. Record the original measurement, the proposed change and the result so the team can explain not only what improved, but why.


Open the query your team complains about most, capture its actual plan and write down the first estimate-to-actual gap you find. That single habit gives you a concrete starting point and turns the next tuning session into an evidence-led exercise rather than an index-fixing guessing game.