Wait Types in SQL Server

Often we we get in a scenario where Application team knocks our door complaining slow processing of data causing delay to the scheduled processes and first reaction a DBA provides is checking running processes and blocking if any on the server. Many a times blocking is a culprit which keeps system busy and processes halted but many a time there is another thing which we need to check and that is checking wait types on the server.

There are many wait types which and we need to know their meaning so I would like to focus on that point today.

Common Wait Types in SQL Server

ASYNC_NETWORK_IO—This wait types points towards a possible network-related issue and most-often caused by a client application not consuming and processing results efficiently from the SQL Server and causing delays. In this wait type SQL is helpless as nothing can be done from SQL Server point of view. We have to escalate and engage the network team if there’s a long distance between servers or the application team to check on application/server resources.

CXPACKET—CXPACKET wait type is one of the most misinterpreted wait stats. The CXPACKET means Class Exchange Packet and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using parallel plan.

This CXPACKET wait type is normal for SQL Server and it is an indicator that SQL Server is using a parallel plan in executing a query, which is generally faster comparing to a query executed in a serialized process. When the parallel plan is used, the query is executed in multiple threads and the query can continue only when all parallel threads are completed. This mean that query will be as fast as the slowest thread. Look at researching and changing Maximum Degree of Parallelism (MAXDOP).

DTC—This wait type is not on the local system. When using Microsoft Distributed Transaction Coordinator (MS-DTC), a single transaction is opened on multiple systems at the same time, and the transaction cannot be concluded until it’s been completed on all of the systems. This wait type accumulates while SQL Server is waiting for the Distributed Transaction Co-Ordinator (DTC) to access the internal service global state object. Distributed transactions run across multiple database instances and sometimes other platforms like Oracle. This wait type is uncommon and if values are excessive then further investigation is required

LCK_M*—This wait type happens when a query locks an object while another query is waiting to lock the same object. A common scenario is when a query is trying to modify a row while another query is trying to read the same row. Review the day and time the locking occurred and which SQL statements were being executed. This wait occurs when a request is waiting to acquire a shared lock. This typically happens when read requests are blocked by write transactions (implicit or explicit) that have been kept open for extended periods of time. Tuning these statements will reduce the session holds on the locks.

NETWORKIO—This wait indicates that one of two scenarios are happening. The first scenario is that the session (i.e., SPID) is waiting for the client application to process the result set and send a signal back to SQL Server that it is ready to process more data. The second is that there may be a network performance issue.

OLEDB—This wait type indicates that a SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This wait type may also indicate the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands, or full-search queries.

PAGEIOLATCH_*— This wait type occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem. Buffer latches, including the PAGEIOLATCH_EX wait type, are used to synchronize access to BUF structures and associated pages in the SQL Server database. The most frequently occurring buffer latching situation is when SQL Server is waiting to read a data file page or workload from storage. These pages and workloads were not cached in memory and need to be retrieved from the disk. Additional memory will help prevent pages from getting pushed out.

SOS_SCHEDULER_YIELD—SQL Server instances with high CPU usage often show the SOS_SCHEDULER_YIELD wait type. This doesn’t mean the server is underpowered; it means further research is needed to find which individual task in a query needs more CPU time. Check the Max Degree of Parallelism (MAXDOP) to ensure proper core usage. Ensure high CPU performance from both within Windows and the system BIOS.

WRITELOG—When a SQL Server session waits on the WRITELOG wait type, it’s waiting to write the contents of the log cache (user delete/update/inserts operations) to the disk where the transaction log is stored and before telling the end user his or her transactions have been committed. Disabling unused indexes will help, but the disk is the bottleneck here, and the transition log should be moved to more appropriate storage.