An estimated plan shows what SQL Server intends to do. An actual plan adds what happened when the statement ran. Comparing estimated and actual execution plans reveals where the optimizer’s picture diverged from reality.
Understand the Shared Plan Behind Estimated and Actual Execution Plans
The optimizer compiles a plan using statistics, available indexes, parameters, and settings. Both estimated and actual execution plans show that compiled choice. The actual plan is not a second independently optimized route created after the query finishes. It adds runtime counters and warnings to the plan that executed. That distinction matters when someone says the two plans disagree.
I compare them to find where estimates failed, not to vote on which picture looks nicer. The graphical icons can distract from the numbers. Start with the operator where estimated rows and actual rows separate sharply. Then ask what data or predicate led the optimizer there. One wrong estimate can influence joins, memory grants, and downstream work.
Use Estimated Plans Safely
An estimated plan does not execute the statement. In SSMS, display it before running a costly or modifying query. It reveals chosen operators, estimated rows, estimated cost, and warnings available at compile time. It cannot tell you actual row counts, spills that occur only at runtime, or how long the execution took.
I use it as an early review for a proposed change, especially when running the statement would modify data. It still needs an appropriate database context and parameter values to be meaningful. A plan generated in test with different statistics can differ from production. Treat it as a prediction under those compile conditions, not a universal promise.
Get Actual Runtime Evidence
An actual plan requires execution and includes runtime row counts, executions per operator, and warnings when available. Use it in a safe environment or during an approved read-only production test. A large result set can make SSMS appear slow for reasons separate from the database query, so manage output carefully.
The first code block turns on XML runtime plan output for the current session. Run a reviewed query next, then turn it off in the same session. It is a session setting, not a server switch. Avoid using it for an uncontrolled batch that returns sensitive data.
SET STATISTICS XML ON;
SELECT name FROM sys.databases WHERE database_id > 4;
SET STATISTICS XML OFF;Request a Compile-Time Plan
SET SHOWPLAN_XML produces a compile-time plan without executing the query. SQL Server requires it to be the only statement in its batch, so use SSMS’s Display Estimated Execution Plan command for the simplest workflow. The second code block uses a catalog query that is safe to run normally. Choose the SSMS estimated-plan command rather than adding a batch separator to the snippet.
I keep the query text, parameter values, database compatibility level, and relevant SET options with both plans. Without those details, a side-by-side comparison can mix different compile contexts. A different plan shape can result from different parameters or statistics, not from the estimated-versus-actual label.
SELECT name, state_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
Compare Row Counts First in Estimated and Actual Execution Plans
Look for the first operator where actual rows differ substantially from estimated rows. A mismatch near a table access can point to statistics, parameter sensitivity, or a non-sargable predicate. A mismatch later can arise from join correlation or earlier errors. Read actual executions too: one operator processing a few rows per call can still do enormous total work when called repeatedly.
I do not use a fixed ratio as an automatic alarm. One row estimated and ten returned can be harmless. A modest relative error on a huge intermediate result can matter. Connect the estimate to the chosen join and memory grant. Then test the root cause, not only the visual red warning.
Read Runtime Warnings
Actual plans can show sort and hash spills, implicit conversions, and other warnings. Some warnings also appear in estimated plans. A warning is a lead, not an instruction to create an index or raise memory immediately. Check the operator, query frequency, resource use, and user impact. A tiny one-time spill can matter less than a frequent scan without a warning icon.
I have seen teams chase every yellow triangle while the slowest query had none. The icon is helpful, but the workload decides priority. Compare a representative execution, not an isolated test with a rare parameter. Record the warning and the measured effect before changing schema or settings.
Inspect Parameters and Settings
A cached plan can be compiled for one parameter and reused for another. The actual plan’s runtime values and compiled values help explain that situation when available. SET options can create separate plans for similar text. Check compatibility level and database-scoped configuration before concluding that one environment has a different optimizer.
I ask which parameter produced the slow execution and which value compiled the plan. A plan that fits a rare customer can be poor for a common one. Test more than one value. If the engine’s newer parameter-sensitive features apply, confirm their state before adding a broad RECOMPILE hint. Keep the plan choice tied to the real workload.
Keep Estimated and Actual Execution Plans Comparable
Collect estimated and actual plans from the same query text, schema, statistics, and settings when possible. If you compare across deployments, record what changed. An index addition can alter the plan for both types. An actual plan captured after a statistics update cannot fairly be compared with an estimated plan saved before it.
I save plan files with the query version and capture time. A screenshot can lose operator properties and warnings. The XML plan preserves details for later review, though it can contain sensitive literals. Store it under the team’s data policy. A careful comparison saves arguments about which icon looked more expensive.
Turn the Difference into a Test
When you find the first meaningful estimate gap, form a small hypothesis. Update one targeted statistic in test, rewrite one predicate, or try one appropriate index. Capture the new actual plan and measure CPU, reads, and duration. Check several representative parameter values. A plan improvement that harms the common path is not a complete fix.
What did SQL Server expect at the operator, and what arrived there? Answer that from the actual plan. Then the next change has a reason you can verify. Estimated and actual execution plans are most valuable together when they lead to a specific, reversible experiment.
Related reading on this blog: Execution Plan: Estimated vs Actual: SQL in Sixty Seconds #113 and Execution Plans and Indexing Strategies: Quick Guide.
An estimated plan is not an execution record, it is the prediction that an actual plan can test.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
