Troubleshooting waits previously.
Nobody likes to wait. SQL database is multithreaded system that can handle thousands of queries executed simultaneously. Since queries that are executed in parallel compete for the same resources (tables, memory, etc) they might need to wait for the resources to be available to proceed with execution. These cumulative waits can be very large and downgrade the whole database performance.
There are more than 900 wait types in SQL Server. Some are more important/frequent than others.
For a long time, the only way you could get closer to understanding what is waiting bottleneck of your workload was to look at instance (sys.dm_os_wait_stats) or recently added session (sys.dm_exec_session_wait_stats) level wait statistics. These options have certain limitations and might not provide optimal experience due to the following reasons:
- It is not possible to deterministically correlate wait stats with specific queries.
- These wait stats are kept only in memory, and are completely lost on server restarts and failovers, or when sessions are killed
Adding wait statistics in query store was one of the major asks from the community since we first released Query Store.
What changed?
Starting today in Azure SQL Database and from CTP 2.0 of SQL Server 2017 wait stats per query are available in Query Store. Now you can exactly identify why and how much every plan waited for some resource. Information about wait times are persisted so you can also analyze through the history what was the problems and why queries waited for resources
Wait categories explained
When we were designing this feature, two possible implementations were considered.
First and naïve approach, would have been to track all wait types statistics for every query. This approach has some drawbacks:
- Presenting the wait stats in their original current format might not have the expected value add for the customer, as it would imply that the customer would have to understand wait stats in detail, which are not intuitive.
- Due to a high number of different wait types, which will further grow in the future, it would not be possible to track them all separately in Query Store without introducing a significant performance and resource utilization overhead. As a side effect, Query Store will get into read-only state and it won’t be feasible to have it on by default in Azure SQL Database.
To address these issues, alternative approach was chosen. We are introducing wait categories, described here sys.query_store_wait_stats (Transact-SQL)
Wait categories are combining different wait types into buckets similar by nature. Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.
Besides, there are far less categories than actual wait types, so we address Query Store storage concerns.
Full mapping between wait types and wait categories is available
