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 ConsumptionHigh CPU Usage from ‘Service Host: SysMain’ Should I Disable It ??
There has been persistent system slowdowns on my windows 10 PC, and after checking Task Manager. I noticed that "Service Host: SysMain" is consistently using 20-40% of my CPU, even after I boot it. The specs on the PC : Windows 10 pro Intel core i5. 8gb ram. 1tb hdd. is it safe to permanently disable SysMain on a system with an HDD? will it affect boot times or app performance long term? What can I do to reduce SysMain's resource usage rather than disabling it completely ?CPU Management in IIS Application Pools: A Deep Dive into Advanced Settings for Optimal Performance
Application pools provide an isolation between different web application in Internet Information Services (IIS) by allowing you to manage the resources, recycling, and performance per application. Control of CPU usage is going to be one of the most critical factors in the performance and management of an application pool. IIS includes a dedicated section under ApplicationPool -> Advance Settings for the management of CPU. it provides varied settings that enable you to optimize the utilization of the CPU so that a particular server resource does not become dominated by one application. This article will dive into the CPU settings for an ApplicationPool, explaining each option and its configuration.AVD Pools using AMD CPU/GPU vs. NVIDIA-Intel CPU/.GPU
Has anyone setup / run any AVD Host Pools using AMD GPU (and AMD CPU)? NVas V4 series? How was the experience for users compared to the NVv3 NVIDIA / Intel VMs? Could you run 1 user per vCPU on a Host with no issues? How was GPU performance? I know the NV V4 series VMs use partial GPUs (except the NV32as V4 which uses a whole). For Applications that rely on GPU performance how did the AMD GPUs perform vs. NVDIA. Did you see any issues using the partial GPUs? Was CPU performance good vs, Intel CPUs?Getting Started Using Phi-3-mini-4k-instruct-onnx for Text Generation with NLP Techniques
In this tutorial, we'll cover how to use the Phi-3 mini models for text generation using NLP techniques. Whether you're a beginner or an experienced AI developer, you'll learn how to download and run these powerful tools on your own computer. From setting up the Python environment to generating responses with the generate() API, we'll provide clear instructions and code examples throughout the tutorial. So, let's get started and see what the Phi-3 mini models can do!General AVD Slowness
We have a pool of NV24s_V3 VMs running. The VMs have 170+ applications on them so we have to keep the total number of users per VM down to 12. That being said, I can power on one of our VMs, let it boot up and sit for 10-15 minutes to get through all its startup processes and then login. When I login and get to my desktop, OneDrive will sync for a bit, but outside of that the CPU is under 10%, RAM is under 10% used and Disk Util is 15-20%. Even with those numbers low and me being the ONLY user on the VM, its still slow to open applications. It even takes 5-6 seconds for File Explorer to open. I see defender processes running under Disk stats, but it alone shouldnt be causing that kind of slow down, especially when the CPU / RAM / Disk stats are low. Anyone have any advice of something to look at to speed up performance? We have already excluded Defender from scanning Program Files and Program Files (x86). Just very troubling that its slow with just 1 user logging onto a 24 proc, 200+ GB RAM VM.
CPU Context Switches
I've done a fair bit of research on this topic but there are diverse opinions on what constitutes CPU contention as it relates to CPU Context Switches. I'm curious if anyone has specific thoughts on the topic. Do you measure contention based on a generic threshold value (say, 5000-10000 context switches per CPU)? I've read in some places that context switches should be evaluated on a per-server basis, with some servers having higher values than others while still not experiencing contention.
