skip to content

Plans & Wait Statistics

Estimated vs actual plans, stale statistics, parameter sniffing, Query Store and wait statistics are the toolkit an interviewer expects you to use on a slowdown.

questions

6

In SQL Server, how do you capture an estimated versus an actual plan, and get runtime plan data for a production query?

level: middleimportance: must knowfreq 62%

answer

  1. one compiles, the other also runs
  2. SHOWPLAN_XML versus STATISTICS XML
  3. the plan cache stores compile-time plans
  4. in-flight and last-known actual plans

basics

~10 s

SET 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 s

Both 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 lines
sql
SET SHOWPLAN_XML ON;
GO
SELECT o.OrderId, o.Total
FROM dbo.Orders AS o
WHERE o.CustomerId = 42;
GO
SET SHOWPLAN_XML OFF;
GO

go deeper

for a junior

Know which SET option gives a plan without running the query and which one runs it and shows actual rows.

for a middle

Explain that both plans come from one compilation, what runtime data the actual plan adds, and why cached plans only carry estimates.

for a senior

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.

for a principal

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.

open as a page

In SQL Server, a stored procedure is fast for one customer and slow for another; how do you confirm parameter sniffing and choose a fix?

level: seniorimportance: must knowfreq 74%

basics

~20 s

Compare the value the cached plan was compiled for with the slow customer's value and its actual rows. Then fix per statement: OPTION (RECOMPILE), OPTIMIZE FOR, a forced Query Store plan or hint, or PSP optimization from SQL Server 2022.

open as a page

In SQL Server, how do you use sys.dm_os_wait_stats to diagnose a slowdown, and what do PAGEIOLATCH_SH, CXPACKET and LCK_M_X waits point to?

level: seniorimportance: must knowfreq 60%

basics

~10 s

sys.dm_os_wait_stats totals completed waits per wait type since startup, so diff two snapshots and drop idle waits. PAGEIOLATCH_SH means reading pages from disk, CXPACKET parallel-exchange waiting, LCK_M_X waiting for an exclusive lock.

open as a page

In SQL Server, when does AUTO_UPDATE_STATISTICS actually refresh a statistics object, and why can a large table's estimates still go stale?

level: middleimportance: should knowfreq 52%

basics

~20 s

SQL Server refreshes a statistic only when a query using it compiles after modifications pass a threshold: 500 + 20% of rows below compatibility level 130, and the smaller of that and SQRT(1000 * rows) from 130.

open as a page

In SQL Server, sessions creating temp tables pile up on PAGELATCH_UP waits in tempdb; what is contending and how do you fix it?

level: seniorimportance: should knowfreq 42%

basics

~10 s

Sessions queue on latches for tempdb's allocation pages (PFS, GAM, SGAM) because there are too few data files. Add equal-size files, one per logical processor up to eight, then more in fours.

open as a page

After moving a fleet to SQL Server 2025, how would you decide when to raise each database's compatibility level, and what role should Query Store play?

level: principalimportance: should knowfreq 35%

basics

~20 s

Upgrade the engine but leave each database at its old compatibility level, enable Query Store and baseline a business cycle, then raise levels in risk order, forcing known good plans for regressions and keeping a rollback path.

open as a page