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.
Let's execute the following query in SQL Server Management Studio.
UPDATE TOP(10) PERCENT Sales.SalesOrderHeader SET TaxAmt = (TaxAmt * 1)
Let's execute the following query in SQL Server Management Studio.
SELECT * FROM Sales.SalesOrderHeader2 WHERE TaxAmt > 3000.00
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'
"WAIT_ON_SYNC_STATISTICS_REFRESH" value has been updated. You can also determine that "STATMAN" works by checking with 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.