A Guide to SQL Server Wait Monitoring

As a data professional using a SQL Server product on bare metal, on-premises virtual machines, containers, IaaS virtual machines, or PaaS offerings the term “WAIT STATISTICS” or “WAITS” is probably not new to you. In reality, the general answer to “why wait” is simply our query needs a resource that isn’t available in order for that query to move on to the next step of execution or completion.

Perhaps a better question is, why are waits tracked?

However, that has an equally simple initial answer. Tracking wait time is a design decision from the SQL Server development team to help them understand how queries are going through the engine. It turns out that same information is useful to use in diagnosing performance issues.

Query Status

In SQL Server, a query is generally considered to be “active” from the time it is initiated until the result set is returned, regardless of the query being called in Azure Data Studio (ADS), SQL Server Management Studio (SSMS), a web-app, console app, mobile app, or other source. There are several statuses that a query may be in while it is active, these can be seen via the sys.dm_os_exec_requests DMV. However, for the purposes of this post we are only concerned with three of them.

  • Running – All of the resource needs have been met, thread and scheduler requests have been answered, and the query is following the execution plan.
  • Runnable – All of the resource needs have been met, a thread is available, and the query is waiting for scheduler assignment.
  • Suspended – One or more resources are needed for the query to be ready for thread and scheduler assignment.

Query duration is the total time spent in all three states, which is likely not a surprise. The time that a query spends in the runnable or suspended state is combined to become the total wait time. When a query is in the runnable state, it is on the runnable queue. This is a true fist in, first out queue structure as the SPID (query) waits for an available scheduler. Additionally, SQL Server tracks the time a query spends, specifically in the runnable queue.

This time is known as the signal wait time, as it is the time from when a SPID is signaled that the resource is ready until the query starts running again. Queries in the suspended state are on the suspended or waiter list. It is a key point to realize this is a list, not a queue, thus when the resource becomes available for a SPID it can move to the runnable queue regardless of the order it was placed on suspended list.

The time on the suspended list is known as the resource wait time. By tracking the total wait time and the signal wait time, the resource wait time can be calculated. Having both the resource and signal wait times adds to our flexibility when we performance tune as we can see if we have a delay in obtaining the resource or a delay once the resource is obtained as the SPID waits for the CPU.

While it is true that our best course of action is frequently to reduce or eliminate the wait as whole, there are certainly situations where the trend on on part or the other may open our eyes to an underlying issue.

WAITS

There are hundreds of specific waits tracked by SQL Server covering resource categories such as CPU, Memory, Storage, and many others. Many of these wait types are tracked to fulfill the directive to account for all of the time it takes to run a query yet have no real relevance to performance tuning.

At the same time, there are some wait types which are tracked but only relevant to certain configuration options being used, such as the series of HADAR waits related to Availability Groups. Additional details pertaining to which waits can be ignored or the specifics of any given wait are beyond the intent of this post. However, Paul Randal has written on the topic and can be found via your search engine of choice. 

Conclusion

Generally speaking, having accumulated wait statistics in SQL Server is expected behavior which, in and of itself, is neither good nor bad. Realistically, wait statistics are nothing more than data to be analyzed, though the waits and queues method of tuning is indeed a powerful approach to improving the performance characteristics of your SQL Server instance.

Explore IDERA's SQL Server solutions.

Written by Peter Shore/posted by Zak Cole

Anonymous