Lessons Learned #562: Measuring the Work Behind Statistics Updates in Azure SQL Database

During a recent support case, a customer was performing stress tests on their Azure SQL Database. They noticed a slight but intriguing variation: occasionally, a query would take a few milliseconds longer and seem to use a little more CPU. They were keen to delve deeper into what was happening during those specific query executions.

Although the difference was minor, it raised an important question. What extra processes were occurring at these times, and was it possible to measure them?

As I explored the Query Store, I discovered some queries that exhibited a familiar pattern: SELECT StatMan(…). This reminded me of a previous article I’d penned, Lesson Learned #498: Understanding the Role of STATMAN in SQL Server and Its Resource Consumption. This recognition gave me a solid starting point; I could leverage the execution statistics stored in the Query Store to explore how much time, CPU, and I/O were consumed by those statements during the stress tests.

I’d like to share what I found along with the query I used to investigate this activity. It allows you to measure the duration, CPU usage, and reads associated with the recorded StatMan executions, and then compare this data with the period in question.

Identifying that the calculations for statistics were consuming resources is just one part of the puzzle. To determine how much these contributed to the customer’s increased execution time, we need to correlate those records with the affected workload. The following sections will detail both what we can measure and how to interpret the results.

sys.dm_db_stats_properties provides information like the last update time and sampling details, but unfortunately, it doesn’t keep track of the duration or resources used during the update. While the workload query plan highlights the statistics loaded during compilation via OptimizerStatsUsage, it fails to provide specific runtime measurements for each statistics calculation.

Thanks to the Query Store already logging these internal statements during our tests, we could examine their historical resource consumption without the need for setting up a new Extended Events session.

StatMan is employed internally during statistics calculations. The query text may include the table and columns being worked on, along with an alias like _MS_UPDSTATS_TBL. What we’re doing here is inspecting the engine-generated statements rather than directly invoking StatMan ourselves.

The following read-only query has been tested successfully in our Azure SQL Database. You’ll want to run this in the relevant database and adjust the UTC dates as necessary. This query will provide you with statistics sorted by query, plan, interval, and execution type. Ensure that the Query Store contains the corresponding statements and runtime data.

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;

Start by checking query_sql_text to verify that the returned row is indeed an actual SELECT StatMan(…) statement. The text filter helps in searching but isn’t a definitive classification. The second filter is designed to exclude common diagnostic queries that reference Query Store when we search for StatMan.

• executions indicates how many recorded executions pertain to that plan, interval, and execution type.
• avg_duration_ms shows the execution-count-weighted average duration. Meanwhile, max_duration_ms identifies the slowest recorded execution within that group.
• total_duration_seconds and total_cpu_seconds estimate total execution duration and CPU time based on recorded averages times the number of executions.
• total_logical_reads and total_physical_reads estimate total page reads based on recorded averages. Query Store reports these reads in terms of 8 KB pages.
• is_internal_query indicates whether Query Store marks the query as generated internally. You can check execution_type_desc to differentiate between regular, aborted, and exceptional executions.

The Query Store provides runtime duration and CPU in microseconds. This query converts them to milliseconds or seconds, as indicated in the output. The grouping also considers multiple runtime-statistics rows that might exist for an active interval.

The date filter focuses on the intervals that overlap with the requested timeframe. These intervals are included in full, meaning executions outside the exact boundaries might be counted. When possible, adjust the interval boundaries to align with your investigation. first_execution_end_utc and last_execution_end_utc mark the times when execution completed, not when it started.

Query Store aggregates executions. If executions exceed one, a single row won’t describe just one update. The summed durations aren’t meant to reflect an end-to-end wall-clock measurement, particularly when some operations might overlap. CPU usage can also be higher than elapsed time when the execution is parallel.

The results provide a quantitative measure of the work associated with StatMan. A significant total can warrant further investigation into its contribution to CPU or I/O during the same time frame, but it doesn’t automatically indicate that it caused application delays or blocking. It’s crucial to correlate these findings with workload performance and resource metrics.

The StatMan statement might not encompass every phase of the surrounding statistics update. Its text alone doesn’t clarify whether the update was automatic or explicitly requested.

If the query yields no rows, that doesn’t mean statistics weren’t updated. Some relevant statements might not have been captured or retained, or their runtime data might not be present in the selected timeframe.

During our testing, we also noted StatMan using sp_statement_completed in a database-scoped Extended Events session. When a suitable capture is available, Extended Events can provide insights into individual statement operations.

With StatMan appearing in the Query Store, we’re able to look into historical duration, CPU usage, and reads for the statistics-calculation statements already logged. This offers a practical foundation for understanding their resource consumption without muddling the distinction between the costs of internal statements, the complete statistics update, and their impact on the workload.