Finding Which Statistics a Query Used in the Execution Plan

A bad row estimate raises the question of which statistics a query used during compilation. SQL Server 2017 and later can show the OptimizerStatsUsage list in an execution plan, including sampling and modification details. Read that list before updating every statistic on the table.

Start With the Misestimated Operator

Open the actual execution plan for a representative run and find the operator where estimated rows diverge from actual rows. Note its predicate and join keys. The statistic you expected to help can be absent from the optimizer's used list, or the used statistic can have a histogram that misses a skewed value. A plan tells you what was compiled, not what every future parameter will use.

I save the parameter values, database compatibility level, plan, and runtime numbers before refreshing anything. Which predicate led to the bad estimate? That narrows the statistics review to a meaningful column or filtered subset.

Find Which Statistics a Query Used in SSMS

In SSMS, open the plan's properties or XML and search for OptimizerStatsUsage. Each StatisticsInfo element can show database, schema, table, statistics name, last update, modification count, and sampling percent. The detail available depends on SQL Server version and how the plan was captured. An actual plan includes runtime counters for the execution, while the stats list records compilation inputs.

SET STATISTICS XML ON;
SELECT TOP (100) SalesID, CustomerID
FROM dbo.FactSales
WHERE CustomerID = 42
ORDER BY SalesID DESC;
SET STATISTICS XML OFF;

Run the example only after replacing the table and key with a real query. In SSMS, Include Actual Execution Plan is another route. Do not confuse an index shown in the access path with a statistic listed as used by the optimizer. Index statistics can inform estimation even when the corresponding index is not selected for access.

Extract Which Statistics a Query Used From the Plan Cache

sys.dm_exec_cached_plans and sys.dm_exec_query_plan expose cached plan XML for plans still in cache. XQuery can read each StatisticsInfo element. The query below limits to 20 plans for a small investigation; filter by a known plan handle or query text in a production collector.

SELECT TOP (20)
       cp.plan_handle,
       x.n.value('@Database','nvarchar(128)') AS database_name,
       x.n.value('@Schema','nvarchar(128)') AS schema_name,
       x.n.value('@Table','nvarchar(128)') AS table_name,
       x.n.value('@Statistics','nvarchar(128)') AS stats_name,
       x.n.value('@LastUpdate','nvarchar(40)') AS last_update,
       x.n.value('@ModificationCount','bigint') AS modifications,
       x.n.value('@SamplingPercent','float') AS sampling_percent
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes
  ('//*:OptimizerStatsUsage/*:StatisticsInfo') AS x(n);

The XML methods need QUOTED_IDENTIFIER ON, which SSMS sets by default; plain sqlcmd without -I fails with Msg 1934. Names come back in brackets, such as [IX_Cust]. A cached plan can be evicted, and the query can be expensive if it scans the entire cache. Capture a known plan handle during the incident when possible. Metadata visibility requires the appropriate server permission.

Read Which Statistics a Query Used From Query Store

Query Store retains plan text beyond a single cache lifetime when enabled and healthy. Convert its query_plan text to XML and read the same nodes, joined to query and query text for context. Query Store can hold several plans for one query; compare plan IDs rather than assuming one statistic list applies to all.

SELECT TOP (50) q.query_id, pl.plan_id,
       x.n.value('@Statistics','nvarchar(128)') AS stats_name,
       x.n.value('@LastUpdate','nvarchar(40)') AS last_update,
       x.n.value('@ModificationCount','bigint') AS modifications,
       x.n.value('@SamplingPercent','float') AS sampling_percent
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS pl ON pl.query_id = q.query_id
CROSS APPLY
  (SELECT TRY_CONVERT(xml,pl.query_plan) AS plan_xml) AS pxml
CROSS APPLY pxml.plan_xml.nodes
  ('//*:OptimizerStatsUsage/*:StatisticsInfo') AS x(n)
WHERE pxml.plan_xml IS NOT NULL;

Add a query_id or time-window filter for an operational search. A Query Store plan is a stored compile artifact; it does not include every execution's live parameter values. Join runtime intervals separately when correlating the plan with a regression.

Interpret Modification and Sampling

A high modification count or low sampling percent can explain a poor estimate, but neither is automatic proof. Inspect the histogram with DBCC SHOW_STATISTICS for the named statistic, compare the problematic value with its steps, and consider correlation among columns. A recently updated statistic can still be unhelpful for a skewed predicate. A filtered statistic can represent a narrow subset better than an unfiltered one.

I update one relevant statistic on a test copy, recompile the specific query, and compare estimated versus actual rows, plan shape, CPU, and reads. If the list of used statistics changes, note that too. An update that makes one parameter faster can make another slower; keep several representative values in the test.

Keep Evidence With the Plan

Store the plan XML, query text, parameter sample, statistics names, last update, modification count, and measured row estimates. This lets another DBA reproduce the reasoning after the plan leaves cache. Do not clear the shared plan cache as a first diagnostic step; it discards useful evidence and changes the workload.

The used-list answers “what informed this compilation.” It does not certify that the statistic was adequate or that no other optimizer inputs mattered. Knowing which statistics a query used narrows the next check to one or two objects. I validate with a controlled change and a fresh actual plan. That is faster and safer than a blanket UPDATE STATISTICS followed by hope.

Read the Correct Plan Version

A parameter-sensitive procedure can have several plans, and Query Store can retain each. A cached plan found now is not necessarily the one that served the slow execution yesterday. Join plan ID to runtime intervals and the incident time before interpreting OptimizerStatsUsage. Save the plan XML from the affected execution if possible. The statistics list is a compile-time record; it does not update in place when table rows change.

In an actual plan, compare operator Actual Rows with Estimated Rows at each stage. A bad estimate downstream can originate from an earlier predicate or join, so do not update the statistic closest to the slow operator by name alone. Trace the first large divergence and check which statistics a query used for that decision.

Confirm the Statistic Still Exists

A plan can refer to a statistic that was later updated, dropped, or replaced. Read current sys.stats and STATS_DATE for the object, then compare with the plan's LastUpdate. The plan value describes the compilation moment; the catalog describes the current state. ModificationCount is a signal, but sampling percent and histogram distribution can matter more than age.

SELECT s.name, s.stats_id,
       STATS_DATE(s.object_id,s.stats_id) AS current_update
FROM sys.stats AS s
WHERE s.object_id = OBJECT_ID(N'dbo.FactSales');

Test a Focused Change

Update the relevant statistic on a test copy with a sample appropriate to the skew, recompile only the affected query, and compare estimates and actual rows. Capture CPU, reads, spills, and duration for several parameter values. If the optimizer still uses the old statistic shape or chooses another plan, inspect predicates and data correlation. A stats update is an experiment with measurable outcomes, not a ritual performed after every slow query.

Related reading on this blog: Find Oldest Updated Statistics: Outdated Statistics and Execution Plans and Indexing Strategies: Quick Guide.

A statistics list is not proof of the right estimate, it is a map for a focused test.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.