New Diagnostics For Synchronous Statistics Updates

Joe Sack announces a new wait type and request command:

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 now available in SQL Server 2019 CTP 2.1.  Read the whole thing.

Related Posts

Accelerated Database Recovery and Filegroups

Randolph West shows a change to Accelerated Database Recovery in SQL Server 2019 CTP 3.2 and later: ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. In most cases, the payoff more than makes up for valuable transaction log and TempDB storage. In my testing, enabling […]

Read More

Waiting on Stats Refreshes

Erik Darling looks at a new wait type: I mean ever really wondered just how long a query of yours waited on stats to automatically update before running? Have you ever been absolutely, positively sure that those gosh dang darn stats updates were putting your query on the trolley to Slowsville? Your wishes will be […]

Read More

Categories

November 2018
MTWTFSS
« Oct Dec »
 1234
567891011
12131415161718
19202122232425
2627282930