Lessons Learned #562: Measuring the Work Behind Statistics Updates in Azure SQL Database
A few extra milliseconds during a stress test While working on a support case, our customer was running stress tests against an Azure SQL Database. They noticed a small but interesting variation: occasionally, a query took a few milliseconds longer and appeared to use slightly more CPU. They wanted to understand what was happening during those executions. The difference was small, but the question was useful. What additional work was being performed during those periods, and could we measure it? While reviewing Query Store, I came across queries containing a familiar pattern: SELECT StatMan(...). They reminded me of an article I had written some time ago, Lesson Learned #498: Understanding the Role of STATMAN in SQL Server and Its Resource Consumption, about the internal work involved in statistics updates. Recognizing that pattern gave me a starting point: I could use the execution statistics recorded in Query Store to investigate how much time, CPU, and I/O those statements had consumed during the stress test I would like to share what I found and the query I used to inspect that work. It provides a way to measure the duration, CPU, and reads attributed to recorded StatMan executions, and then compare their activity with the period being investigated. Finding that statistics calculation consumed resources is one part of the investigation. Establishing how much it contributed to the customer's extra milliseconds requires correlating those records with the affected workload. The sections below explain both what we can measure and how to interpret it. Looking beyond the last update time sys.dm_db_stats_properties provides the last update time and sampling information, but does not preserve the duration or resource consumption of the update. The workload query plan identifies statistics loaded during compilation through OptimizerStatsUsage, but does not provide a separate runtime measurement for each statistics calculation. Because Query Store already contained these internal statements in our test, we could inspect their historical resource consumption without configuring a new Extended Events session. Finding StatMan in Query Store StatMan is used internally during statistics calculation. Its query text can include the table and columns being processed and an alias such as _MS_UPDSTATS_TBL. We are inspecting recorded engine-generated statements, not invoking StatMan directly. The following read-only query was tested successfully in our Azure SQL Database. Run it in the database being investigated and adjust the UTC dates. It returns statistics by query, plan, interval, and execution type. Query Store must already contain the relevant statements and runtime data. Query Store inspection query DECLARE @FromUtc datetimeoffset = '2026-10-20T00:00:00+00:00'; DECLARE @ToUtc datetimeoffset = '2026-10-26T00:00:00+00:00'; ;WITH RuntimeStats AS ( SELECT rs.plan_id, rs.runtime_stats_interval_id, rs.execution_type_desc, MIN(rs.first_execution_time) AS first_execution_end_utc, MAX(rs.last_execution_time) AS last_execution_end_utc, SUM(rs.count_executions) AS executions, SUM(rs.avg_duration * rs.count_executions) AS total_duration_us, MAX(rs.max_duration) AS max_duration_us, SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu_us, SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads, SUM(rs.avg_physical_io_reads * rs.count_executions) AS total_physical_reads FROM sys.query_store_runtime_stats AS rs JOIN sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id WHERE i.start_time < @ToUtc AND i.end_time > @FromUtc GROUP BY rs.plan_id, rs.runtime_stats_interval_id, rs.execution_type_desc ) SELECT q.query_id, p.plan_id, q.is_internal_query, i.start_time AS interval_start_utc, i.end_time AS interval_end_utc, r.execution_type_desc, r.first_execution_end_utc, r.last_execution_end_utc, r.executions, r.total_duration_us / NULLIF(r.executions, 0) / 1000.0 AS avg_duration_ms, r.max_duration_us / 1000.0 AS max_duration_ms, r.total_duration_us / 1000000.0 AS total_duration_seconds, r.total_cpu_us / 1000000.0 AS total_cpu_seconds, r.total_logical_reads, r.total_physical_reads, qt.query_sql_text FROM RuntimeStats AS r JOIN sys.query_store_plan AS p ON p.plan_id = r.plan_id JOIN sys.query_store_query AS q ON q.query_id = p.query_id JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id JOIN sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id = r.runtime_stats_interval_id WHERE qt.query_sql_text LIKE N'%StatMan%' AND qt.query_sql_text NOT LIKE N'%query_store%' ORDER BY total_duration_seconds DESC; Reading the results Start by checking query_sql_text to confirm that a returned row is an actual SELECT StatMan(...) statement. The text filter is a search aid, not a definitive classification. The second predicate excludes common diagnostic queries that reference Query Store while searching for StatMan. executions shows the number of recorded executions for that plan, interval, and execution type. avg_duration_ms is the execution-count-weighted average duration. max_duration_ms identifies the slowest recorded execution within the group. total_duration_seconds and total_cpu_seconds estimate accumulated execution duration and CPU from the recorded averages multiplied by execution counts. total_logical_reads and total_physical_reads estimate accumulated page reads from the recorded averages. Query Store reports these reads as counts of 8 KB pages. is_internal_query identifies whether Query Store marks the query as internally generated. Inspect execution_type_desc to distinguish regular, aborted, and exception executions. Query Store reports runtime duration and CPU in microseconds. The query converts them to milliseconds or seconds as indicated by the output names. Its grouping also accounts for multiple runtime-statistics rows that can exist for the active interval. Interpreting the time window The date filter selects intervals that overlap the requested window. Those intervals are included in full, including executions outside the precise requested boundaries. Use interval boundaries aligned with your investigation when possible. first_execution_end_utc and last_execution_end_utc identify execution completion times, not start times. Query Store aggregates executions. When executions is greater than one, a row does not describe one individual update. Summed durations are not an end-to-end wall-clock measurement, especially when operations overlap. CPU can also exceed elapsed time when execution is parallel. From recorded consumption to workload impact The results quantify the recorded work attributed to StatMan. A high total can justify investigating its contribution to CPU or I/O during the same period, but does not by itself prove that it caused application latency or blocking. Correlate those observations with workload performance and resource metrics. The StatMan statement need not include every phase of the surrounding statistics update. Its text alone does not establish whether the update was automatic or explicitly requested. If the query returns no rows, it does not prove that statistics were not updated. The relevant statements may not have been captured or retained, or their runtime data may not be available in the selected window. Extended Events as an alternative We also observed StatMan through sp_statement_completed in a database-scoped Extended Events session during testing. Extended Events can provide individual statement observations when a suitable capture is available. Lessons learned When StatMan is present in Query Store, we can examine historical duration, CPU, and reads for the statistics-calculation statements already recorded. This provides a practical starting point for understanding their resource consumption while preserving the distinction between internal statement cost, the full statistics update, and impact on the workload. References Query Store runtime statistics Query Store query metadata Statistics in SQL Server and Azure SQL Database Lesson Learned 498 Understanding the Role of STATMAN in SQL Server and Its Resource ConsumptionLesson Learned #498:Understanding the Role of STATMAN in SQL Server and Its Resource Consumption
Today, I worked on a service request that our customer reported a performance issue and they reported that this query: SELECT StatMan([SC0], [LC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [LC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM ..] TABLESAMPLE SYSTEM (@samplePercent PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 16, RECOMPILE). I would like to share my lessons learned here.
