SQL Server Wait Stats Tool

Wait types help you determine a more specific bottleneck on a SQL Server—such as schema locking issues, plan cache memory pressures, or backup I/O problems—instead of simply generic locking, memory, or I/O issues you might identify through other methods. The promise of wait stat analysis is—you now have an excellent way to quickly determine where the strongest pressures are building up on specific resources within SQL Server.

When your application uses resources from the SQL Server relational database, SQL Server executes requests using threads. A thread executes code to communicate with the SQL Server on a tight schedule. If these threads are unable to execute their required commands due to a lack of server resources, they must wait to complete the query until they have enough resources to continue.

Within your SQL Server, a tool called a scheduler serves as the mechanism for moving individual threads through the SQL Server to retrieve data and complete a query. The scheduler serves several important purposes within your SQL Server, with each thread’s major life cycle occurring within your schedule. The scheduler contains the SQL Server processor, which contains threads before and during their execution of the desired code.

Before a thread executes code, the scheduler houses all the threads waiting to execute their respective codes. This runnable queue isn’t an inherent source of downtime since the threads typically move through the queue at the correct speed determined by the scheduler.

There are plenty of cases in which threads are unable to move through the runnable query on time due to a lack of resources. When a thread can’t execute code as scheduled because of insufficient resource availability, it gets stuck on the waiter list.

The scheduler typically contains a constant backup of threads on the waiter list unable to move forward for various reasons. For optimal wait statistics, SQL Server management tools can help identify the causative relationship between threads in your waiter list, and the resource deficiencies causing the bottleneck.

Each resource deficiency potentially causing a wait time is called a wait type. In short, a wait type is the reason why your thread is unable to move forward through the scheduler and properly execute its code.

There are hundreds of wait types, each of which provides unique insight into the causes of bottlenecks. Some common wait types include:

• PAGEIOLACH_EX waits indicate buffering, along with other PAGEIOLACH_* wait types like PAGEIOLACH_SH.
• OLEDB waits indicate a thread is waiting for a response from the OLE DB before it can continue.
• CXPACKET waits indicate your thread has been slowed due to parallel queries.
• And many more.

In a wait type-based SQL Server wait stats tool, like SQL Sentry, your wait type library provides a complete record of wait types for fast and easy identification. Once your SQL Server analytics tool has identified the wait type of a thread, you can understand which resources are limited, and you can troubleshoot the bottleneck.