In SQL Server, how do you capture an estimated versus an actual plan, and get runtime plan data for a production query?
answer
- one compiles, the other also runs
- SHOWPLAN_XML versus STATISTICS XML
- the plan cache stores compile-time plans
- in-flight and last-known actual plans
basics
~10 sSET SHOWPLAN_XML ON compiles without executing and returns the estimated plan; SET STATISTICS XML ON executes and adds runtime rows and warnings. Cached plans hold estimates only; runtime data comes from sys.dm_exec_query_statistics_xml or sys.dm_exec_query_plan_stats.
solid answer
~40 sBoth plans come from the same compilation. `SET SHOWPLAN_XML ON` (or `SHOWPLAN_ALL`/`SHOWPLAN_TEXT`) returns the estimated plan and does not run the statement. `SET STATISTICS XML ON` runs it and returns the actual plan: actual rows and executions per operator, granted and used memory, actual DOP and runtime warnings such as spills. For a production query I would not rerun, the plan cache via `sys.dm_exec_query_plan(plan_handle)` only gives the compile-time plan with estimates. For a request that is running now, `sys.dm_exec_query_statistics_xml(session_id)` returns its plan with the counters so far. From SQL Server 2019, `ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON` makes `sys.dm_exec_query_plan_stats(plan_handle)` return the last known actual plan of a cached query.
code
sql · 8 linesSET SHOWPLAN_XML ON;
GO
SELECT o.OrderId, o.Total
FROM dbo.Orders AS o
WHERE o.CustomerId = 42;
GO
SET SHOWPLAN_XML OFF;
GOgo deeper
Know which SET option gives a plan without running the query and which one runs it and shows actual rows.
Explain that both plans come from one compilation, what runtime data the actual plan adds, and why cached plans only carry estimates.
Show how you get runtime plan data in production without rerunning a write: in-flight plans, last-known actual plans and their opt-in cost.
Weigh leaving LAST_QUERY_PLAN_STATS on fleet-wide for faster diagnosis against its overhead, and decide where Query Store must back up the volatile plan cache.
## Two views of one compiled plan SQL Server's optimizer compiles a statement into one plan. The **estimated plan** is that plan as compilation left it: the operators, the join order, the indexes chosen, and the optimizer's *estimated* row counts and costs. The **actual plan** is the same compiled plan after the statement has run, annotated with what happened: actual rows and executions per operator, the actual degree of parallelism, memory granted and memory used, and runtime warnings such as a sort or hash spilling to `tempdb`. Because the actual plan needs an execution, capturing one in your own session means running the statement. That matters before you ask for the actual plan of an `UPDATE` or `DELETE` on production data: the rows really change. ## Capturing each in a session | Session option | Runs the statement? | What comes back | | --- | --- | --- | | `SET SHOWPLAN_XML ON` | No | Estimated plan as an XML document per batch | | `SET SHOWPLAN_ALL ON`, `SET SHOWPLAN_TEXT ON` | No | Estimated plan as a rowset or text | | `SET STATISTICS XML ON` | Yes | The results, then the actual plan as XML | | `SET STATISTICS PROFILE ON` | Yes | The results, then a per-operator rowset with actual rows and executions | Details worth knowing about the `SHOWPLAN` options: - `SET SHOWPLAN_XML` must be the only statement in its batch and cannot be used inside a stored procedure. - While it is on, nothing executes. A `CREATE TABLE` in a later batch is only planned, so a following `SELECT` against that table fails because the table was never created. - You need permission to run the statements plus `SHOWPLAN` permission on every database the statements touch. - `EXPLAIN` is PostgreSQL's and MySQL's plan statement, not how you ask a SQL Server instance for a plan. ## Production: the plan cache holds compile-time plans For a query that already ran in production, the first stop is the plan cache. `sys.dm_exec_query_stats` and `sys.dm_exec_cached_plans` give a `plan_handle`, and `sys.dm_exec_query_plan(plan_handle)` returns its `query_plan` column, which the documentation describes as the **compile-time** Showplan. It has estimates and no actual row counts. A few traps: - Some statements are never cached, such as bulk operations and statements with string literals larger than 8 KB, so there is nothing to retrieve unless the batch is still running. - Plans leave the cache on restart, failover, memory pressure and recompilation. - For an ad hoc statement that was simply or forcibly parameterized, the cached entry holds only the statement text; the real plan sits under the prepared, parameterized query. ## Runtime data without rerunning the query SQL Server's **lightweight profiling** infrastructure collects per-operator row counts cheaply, and it is on by default from SQL Server 2019 (trace flag 7412, which enabled it on older releases, has no effect from 2019; the `LIGHTWEIGHT_QUERY_PROFILING` database scoped configuration can switch it off). On top of it: 1. `sys.dm_exec_query_statistics_xml(session_id)` returns the plan of an **in-flight** request with the transient statistics collected so far, which is what you want for a query that has been running for twenty minutes. 2. `sys.dm_exec_query_profiles` exposes live per-operator progress for running requests. 3. `sys.dm_exec_query_plan_stats(plan_handle)`, from SQL Server 2019, returns the equivalent of the **last known actual plan** for a cached plan. It is opt-in: `LAST_QUERY_PLAN_STATS` is `OFF` by default and is enabled per database with `ALTER DATABASE SCOPED CONFIGURATION`, or server-wide with trace flag 2451. For queries that are not complex or resource consuming it may return a simplified plan. 4. The `query_post_execution_plan_profile` extended event captures actual-plan equivalents through lightweight profiling, while `query_post_execution_showplan` uses the heavier standard profiling. ## What to look at once you have the actual plan - **Estimated versus actual rows** on each operator, starting where the two first diverge; interpreting that gap is the general material on reading execution plans. - **Warnings** on operators, such as a sort or hash spill and missing column statistics. - The `OptimizerStatsUsage` element, whose `StatisticsInfo` children name each statistics object the optimizer loaded with its `ModificationCount`, `SamplingPercent` and `LastUpdate` at compile time. - The `MissingIndexes` element, the same suggestion the missing-index DMVs collect. - The parameter list, which records the values the plan was compiled with; in an actual plan you can compare them with the values of this execution.
- The actual plan carries a missing-index suggestion. Should you create that index as shown?Not blindly. The suggestion comes from the optimizer during compilation and feeds the same data as the `sys.dm_db_missing_index_*` DMVs. It only proposes nonclustered rowstore indexes, never unique or filtered ones, gives key columns without an order, does no cost-benefit check on large `INCLUDE` lists and stops collecting after 600 missing-index groups. Review it with the table's existing indexes, merge overlapping suggestions and test before creating anything.
- Why might sys.dm_exec_query_plan return no plan for a statement you know ran?It only sees what is in the plan cache. The statement may never have been cached (bulk operations, string literals over 8 KB), its plan may have been evicted by memory pressure, a recompile, a restart or a failover, or it was auto-parameterized so the plan sits under the parameterized query. Query Store, if enabled, keeps plans beyond the cache's lifetime.
- Thinks plans read from sys.dm_exec_query_plan include actual row counts.
- Believes SET STATISTICS XML only shows a plan and does not run the statement.
- Expects an EXPLAIN statement to return a plan on a SQL Server instance.
- Enables trace flag 7412 on SQL Server 2019 to get lightweight profiling.
- Assumes sys.dm_exec_query_plan_stats works without enabling LAST_QUERY_PLAN_STATS.
Assumes SQL Server 2025; lightweight profiling is on by default and sys.dm_exec_query_plan_stats exists from SQL Server 2019.