In SQL Server, wait types represent the discrete steps in query processing, where a query waits for resources as the instance completes the request. Response time analysis assesses database performance by analyzing time spent at each wait type.
Figure 1. SQL Server Wait types and times associated with each, as collected by SolarWinds Database Performance Analyzer continuous performance monitoring solution.
According to Microsoft, “Wait Types as the best opportunity to improve performance, the biggest bang for the buck.”
There are hundreds of Wait Types in SQL 2005, more in 2008, and 169 new ones in SQL 2012. Commonly encountered wait types include:
- ASYNC_NETWORK_IO—The async_network_io (in SQL 2005/2008) and networkio (in SQL 2000) wait types can point to network related issues, but most often are caused by a client application that is not processing results from the SQL Server quickly enough.
- CXPACKET—This wait type is involved in parallel query execution, and indicates that the SPID is waiting on a parallel process to complete or start. Excessive CXPacket waits may indicate a problem with the WHERE clause in the query.
- DTC—This wait type is not on the local system. When utilizing 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 has been completed on all of the systems.
- NETWORKIO—The async_network_io (in SQL 2005/2008) and networkio (in SQL 2000) wait types can point to network related issues, but most often are caused by a client application that is not processing results from the SQL Server quickly enough.
- 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 that the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands or full-search queries.
- PAGEIOLATCH_EX—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 serialization is required on a buffer page.
- SOS_SCHEDULER_YIELD—SQL Server instances with high CPU usage often show the SOS_SCHEDULER_YIELD wait type, which can indicate a need for further research and action.
- WRITELOG—When a SQL Server session waits on the WRITELOG wait type, it is waiting to write the contents of the log cache to disk where the transaction log is stored.