Great feature additions about automatic synchronous statistics update

I was seeing "SQL Database Engine Blog", I found the following article.
blogs.msdn.microsoft.com

Following scenarios are assumed.

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.

There was a time when the query suddenly became slow one day. but you do not know the cause unknown well. You were told by a customer, so you try to confirm without reproducibility. do you have experience like? Lol

Add wait type "WAIT_ON_SYNC_STATISTICS_REFRESH" for automatic statistics updates. Let's try it. Create non-clustered index on the AdventureWorks2017.Sales.SalesOrderHeader TaxAmt column.

non-clustered index of TaxAmt

Let's execute the following query in SQL Server Management Studio.

UPDATE
    TOP(10) PERCENT Sales.SalesOrderHeader
SET
    TaxAmt = (TaxAmt * 1)

Execution Result of UPDATE statement

Let's execute the following query in SQL Server Management Studio.

SELECT
    *
FROM
    Sales.SalesOrderHeader2
WHERE
    TaxAmt > 3000.00

Execution Result of SELECT statement

Let's execute the following query in SQL Server Management Studio.

SELECT
    *
FROM
    sys.dm_os_wait_stats
WHERE
    wait_type = 'WAIT_ON_SYNC_STATISTICS_REFRESH'

Execution Result of SELECT statement

"WAIT_ON_SYNC_STATISTICS_REFRESH" value has been updated. You can also determine that "STATMAN" works by checking with SQL Server Profiler.

Execution Result of SQL Server Profiler

Very Helpful. Thanx!

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.