lessons learned
9 TopicsLessons 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 Consumption288Views2likes0CommentsLessons learned: Updating an app from copied Duo lib files to Xamarin.Forms.DualScreen NuGet package
I just updated my https://github.com/tscholze/xamarin-road-to-surface-duo to use the freshly released https://www.nuget.org/packages/Xamarin.Forms.DualScreen/4.5.0.282-pre4. That's why I wanna share my lessons learned, maybe some will be for others obvious, maybe not. Do not forget Update Xamarin.Forms package, to the latest pre-release (don't forget to mark the checkbox) Install the Xamarin.Forms.DualScreen package, it's a pre-release (don't forget to mark the checkbox) Delete all old copy-and-pasted "Duo Lib" that you copy pasted from the very first sample project Embed new namespace in duo-specific (e.g. master/detail pages) files xmlns:dualScreen="clr-namespace:Xamarin.Forms.DualScreen;assembly=Xamarin.Forms.DualScreen" Update, usage of TwoPaneView to new syntax with child elements <dualScreen:TwoPaneView> <dualScreen:TwoPaneView.Pane1> <views:ItemsView x:Name="MasterPane" /> </dualScreen:TwoPaneView.Pane1> <dualScreen:TwoPaneView.Pane2> <views:ItemView x:Name="DetailPane" /> </dualScreen:TwoPaneView.Pane2> </dualScreen:TwoPaneView> Check if you set the config values correct in the MainActivity.cs to listen on Duo-specific changes ConfigurationChanges = ConfigChanges.ScreenSize | ConfigChanges.Orientation | ConfigChanges.ScreenLayout | ConfigChanges.SmallestScreenSize Conclusion It's great to see that you do not longer need copy & (w/p)asted files in your library. There are not really breaking changes. It took me max. an hour to update my app.746Views2likes0CommentsLessons Learned #558: What an Execution Plan XML Can Tell Us in Azure SQL Database
During a performance investigation in Azure SQL Database, we requested the execution plan to understand what the query was doing. While reviewing the evidence, the customer asked a broader question: when you open the XML of an execution plan, what do you usually look for? It was a useful question. The graphical plan helps us follow the flow of data, but the XML contains details about parameters, statistics, warnings and execution that can explain why the query behaved as it did. I do not read every attribute. I start with a few checks that help connect the customer symptom to the work performed by the query. In this article, I will walk through those checks: the type of plan, parameter values, estimated versus actual rows and statistics, missing indexes and implicit conversions, and reasons for a serial plan. The XML fragments below are simplified examples that use invented values or are adapted from examples discussed in previous articles in the Lessons Learned troubleshooting series. First check whether the plan contains execution evidence Plan What we can inspect Estimated plan Optimizer choices and estimates; the query has not executed Actual plan Plan plus available counters and warnings from that execution Cached or Query Store plan Compiled plan; normally no operator actuals for the incident For the checks involving actual rows, I request an actual execution plan. In SSMS, use Include Actual Execution Plan, or capture it using SET STATISTICS XML. Choose one method. The latter executes the query and returns its plan. The account needs the permissions to run the statement and SHOWPLAN permission. SET STATISTICS IO ON; SET STATISTICS TIME ON; SET STATISTICS XML ON; -- Execute the SELECT being investigated here. SET STATISTICS XML OFF; SET STATISTICS TIME OFF; SET STATISTICS IO OFF; Save the plan as a .sqlplan file and keep the Messages output. Record when it was captured, the parameters and the reported symptom. An execution captured later may not represent the incident. Review query literals, parameter values and object names before sharing the file. Check the right statement and its parameters A procedure or batch may contain several statements. I locate the relevant StmtSimple and its QueryPlan before interpreting a warning. StatementId and operator NodeId help keep the observations attached to the correct statement and operator; NodeId is not a stable identifier across different compilations. Next, I look at ParameterList. When available, it shows both the value used when the plan was compiled and the value used during the captured execution. <ParameterList> <ColumnReference Column="@CustomerId" ParameterDataType="int" ParameterCompiledValue="(42)" ParameterRuntimeValue="(900)" /> </ParameterList> Here, the plan was compiled with CustomerId 42 and executed with CustomerId 900. If one customer has a few orders and another has thousands, I would compare their data distribution and actual plans. Different values are a reason to investigate parameter sensitivity; they do not prove that plan reuse caused the problem. A missing runtime value means this capture did not provide it. It does not mean the runtime value was the same as the compiled value. Also, eligible queries can intentionally use different plan variants through Parameter Sensitive Plan optimization. Make the comparison match the application If the query is slow in the application but fast in SSMS, I compare the SQL text, parameter types and values, database context and session options. StatementSetOptions records options associated with compilation. <StatementSetOptions ARITHABORT="true" ANSI_WARNINGS="true" ANSI_NULLS="true" QUOTED_IDENTIFIER="true" ANSI_PADDING="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" /> A difference in context can help explain why the application and SSMS use different cached plans. I capture both rather than assuming that a fast SSMS execution represents the application. The difference itself still needs to be connected to the measured performance. Compare estimated and actual rows then inspect statistics I follow the rows through the plan and look for the first useful difference between what the optimizer expected and what the operator actually processed. <RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="100"> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="20000" ActualRowsRead="80000" ActualExecutions="1" /> </RunTimeInformation> <!-- Access details omitted --> </RelOp> In this single-execution example, the operator estimated 100 rows and returned 20,000: a 200-fold underestimate. It also read 80,000 rows. I would ask why the estimate was inaccurate, and why the operator read substantially more rows than it returned. These are separate questions. For repeated or parallel operators, account for executions and threads before comparing totals. Do not add the rows from every operator to calculate the query result size. The next question is often whether the statistics were out of date. OptimizerStatsUsage identifies statistics consulted during compilation, where that information is exposed. <OptimizerStatsUsage> <StatisticsInfo Schema="[PlanXmlLab]" Table="[Orders]" Statistics="[IX_Orders_CustomerId]" LastUpdate="2026-10-10T07:00:00" SamplingPercent="20" ModificationCount="15000" /> </OptimizerStatsUsage> I check the relevant statistics object, its LastUpdate, SamplingPercent and ModificationCount together. An old timestamp or a low sampling percentage alone does not prove that it caused the estimate. Updated statistics cannot explain every discrepancy; parameter values, predicates and data distribution also matter. The XML describes the statistics consulted at compilation. To check their current state, I use sys.dm_db_stats_properties, as shown next. A subsequent update does not rewrite the evidence in an already saved plan. Check when the statistics were last updated -- Run in the affected database and set the actual table name. SELECT st.name AS statistics_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter FROM sys.stats AS st OUTER APPLY sys.dm_db_stats_properties( st.object_id, st.stats_id) AS sp WHERE st.object_id = OBJECT_ID(N'PlanXmlLab.Orders'); last_updated identifies the latest statistics update; rows and rows_sampled describe the population and sample at that update. modification_counter counts modifications to the leading statistics column since the update. Metadata visibility and permissions can affect the output. Compare this information with the same statistics object in the plan. Timestamps alone do not tell us whether the update was automatic or manual. If an update is justified, validate it in a controlled comparison using the same query and representative parameters, and capture a newly compiled plan. Read the access path alongside the row counts An Index Seek can still read many rows. I inspect SeekPredicates, any residual Predicate and ActualRowsRead where available. A broad seek range followed by filtering can explain why the rows read greatly exceed the rows returned. Likewise, a Key Lookup may be inexpensive once but expensive when repeated for thousands of rows. A scan can be the right choice when a large part of the table qualifies. The operator name is a starting point; the amount of work tells us more. For the customer, the useful explanation is concrete: this operator returned many more rows than expected, these were the statistics used, and this is the next comparison that will help establish why. That is more informative than recommending a statistics update from the timestamp alone. Read missing indexes and conversion warnings as clues Missing-index messages are easy to notice. I inspect the suggestion, but I also check whether it fits the query and the existing indexes. <MissingIndexes> <MissingIndexGroup Impact="85.0"> <MissingIndex Schema="[PlanXmlLab]" Table="[Orders]"> <ColumnGroup Usage="EQUALITY"> <Column Name="[CustomerId]" ColumnId="2" /> </ColumnGroup> <ColumnGroup Usage="INCLUDE"> <Column Name="[Amount]" ColumnId="3" /> </ColumnGroup> </MissingIndex> </MissingIndexGroup> </MissingIndexes> This example suggests a key on CustomerId with Amount included for coverage. EQUALITY and INEQUALITY describe predicate columns, while INCLUDE describes covering columns. The suggestion does not settle every detail of index design. Impact is an estimated reduction in optimizer cost. A value of 85 does not promise an 85% reduction in elapsed time. The XML may contain more suggestions than the graphical plan displays. Review existing indexes and write overhead, then measure the candidate change. Check which side of the predicate is converted <Warnings> <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[dbo].[Customers].[Code],0)" /> </Warnings> In this separate example, I would inspect the Code column type and the application parameter declaration. A varchar column compared with an nvarchar parameter can introduce a conversion on the column side because nvarchar has higher type precedence. The warning identifies something that may affect the plan, not a measured root cause. Whether a conversion prevents an efficient seek depends on the types, collation and expression. Test an appropriate parameter declaration while preserving required character semantics, and compare results, reads and CPU. A conversion in the projection is different from one in a search predicate. Look for an explicit reason when the plan is serial Customers sometimes ask why a query does not run in parallel when the database has multiple vCores. I look for NonParallelPlanReason in QueryPlan before assuming that more resources or a higher MAXDOP will change the result. <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="TSQLUserDefinedFunctionsNotParallelizable"> <!-- Other properties omitted --> </QueryPlan> Reason What I inspect next MaxDOPSetToOne Effective MAXDOP setting or query hint TSQLUserDefinedFunctionsNotParallelizable Scalar UDF and its inlining behavior NonParallelizableIntrinsicFunction The specific intrinsic function referenced A serial plan can be appropriate. The attribute can identify a restriction, but its absence does not fully explain the optimizer decision. Scalar UDF inlining can remove a restriction without guaranteeing parallel execution. DegreeOfParallelism="1" does not demonstrate parallel work. In Azure SQL Database, check database-scoped MAXDOP and query hints. More vCores do not override every restriction or guarantee selection of a parallel plan. Lessons learned The answer to the customer was a reading process: identify the statement and capture type, inspect parameters, compare estimated and actual work, and investigate the relevant statistics and warnings. Each observation should help us ask a better question and select a useful test. For a before-and-after comparison, preserve the query, parameters, data and relevant settings. Compare returned results, logical reads, CPU and duration. A plan describes one compilation or execution; Query Store can help establish whether the behavior changed over time. A short checklist Confirm that the plan belongs to the affected statement and execution. Check parameter values and the application context. Compare estimated and actual rows, accounting for repeated work. Inspect compilation statistics and their current update metadata. Review missing indexes, implicit conversions and serial-plan reasons. Validate the explanation with a measured comparison.