Diagnostic Data for Synchronous Statistics Update Blocking

Consider the following query execution scenario:

• You execute a SELECT query that triggers an automatic synchronous statistics update.
• The synchronous statistics update begins execution and your query waits (is essentially blocked) until the fresh statistics are generated.
• The query compilation and execution does not resume until the synchronous statistics update operation completes.

During this time, there are no external signs via common troubleshooting channels that the query is specifically waiting for the synchronous statistics update operation to complete.  If the statistics update takes a long time (due to a large table and\or busy system), there is no easy way to determine root cause of the high duration.

This is not an uncommon scenario and up until now there has been a lack of obvious telemetry surfaced to the customer that helps them (or Microsoft customer support) diagnose the root cause of this type of slow-running query.

In SQL Server 2019 CTP 2.1 (and coming soon to Azure SQL Database), we have introduced new diagnostic data to help troubleshoot this specific scenario...

When a query is blocked behind a synchronous statistics update, the command column in sys.dm_exec_requests will now show ‘Command (STATMAN)’ while a statistics update is happening in the background and will revert to the initial command name after the statistics update operation is finished.

Additionally, the new WAIT_ON_SYNC_STATISTICS_REFRESH wait type will measure aggregated wait time (blocks) on synchronous statistics updates. This wait time accumulation will be available in the sys.dm_os_wait_stats dynamic management view.

We believe these two small changes will help address a significant diagnostic gap. For feedback or questions, please reach out to us at IntelligentQP@microsoft.com.

Comments

• Anonymous
  November 13, 2018
  Very neatly written for this compact and useful feature.
• Anonymous
  November 15, 2018
  The comment has been removed
  • Anonymous
    November 15, 2018
    Hi Kevin,Thanks for the question Kevin!The threshold is still applicable. But the data modifications themselves don't trigger the update of stats - rather - when a query * needs * the stats and we detect the threshold has been reached due to data modifications, we then kick off automatic statistics.For example:-- Modify enough rows to trigger auto update - but still - we don't kick off stats update just because we exceeded a thresholdUPDATE TOP (10 ) PERCENT dbo.orders_historySET [O_TotalPrice] = ([O_TotalPrice] * 1);GO-- Now if we have a query that needs the associated stats, we'll see the threshold was exceeded and will now actually trigger stats update -- This is where the sync stats update occursSELECT [O_OrderKey], [O_TotalPrice]FROM dbo.orders_historyWHERE [O_TotalPrice] = 93719.57;GOLet me know if there are follow-up questions. Thanks!Joe
• Anonymous
  November 16, 2018
  if statistics already existing, would it not be better while UPDATE STATISTICS is in progress for any query compiles to use the existing statistics?Incremental statistics recompute is a nice feature. but what about tables that are not partitioned. Assume that there are only inserts to a table with increasing key. Would it be feasible to marked the value of the earlier statistics, then sample only newer values?Failing that, how about a manual option to extend the current range to some higher value (yet to be actually populated) at existing density (or specified density)?
  • Anonymous
    November 16, 2018
    The comment has been removed