By
Published
Mar 23 2019 06:52 PM
1,953
Views
First published on MSDN on Nov 13, 2018
Consider the following query execution scenario:
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 .
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 .