Commonly encountered wait types in SQL Server

Commonly encountered wait types include:

PAGELATCH_ --Latches synchronize access to internal SQL Server memory structures. Many classes of Latches, but BUFFER is most common i.e. control access to buffer cache.

PAGELATCH_UP –Means Tasks are waiting for tempdb resources. Additionally, the wait type is PAGELATCH_UP, and the wait resource points to pages in tempdb.

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.

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.

IO_COMPLETION --wait represents SQL Server waiting for non-data page I/O.  It is common for this wait to occur during tempdb spill-over from sort and hash operations

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. Query tuning/ Index tuning may require.

SOS_SCHEDULER_YIELD—SQL Server instances with high CPU usage often show this wait type, which can indicate a need for further research and action.

LCK_M_* -- locking/blocking scenario--A session waiting on LCK_M_* wait is the victim.

RESOURCE SEMAPHORE --is an internal algorithm in SQL Server that grants requested memory to SQL Server queries based on their compiled execution plan to run and execute on the instance.  RESOURCE SEMAPHORE works on a first come first serve basis so all new queries will be in the queue and waiting queries will get their request memory based on their arrival in the queue.

HADR_SYNC_COMMIT --This wait type is only used for databases participating in an Availability Group. When modifications occur on the Primary Replica in synchronous mode it must commit all of these on the Secondary Replicas before committing on itself. This wait type accumulates during this period.

Suggested solutions:

1. If SLAs allow, investigate whether the Availability Group can be switched to asynchronous mode.
2. Ensure that the network between replicas is not over-utilized, if so consider upgrading.
3. Rewrite modification DML that performs small (row-by-row) changes. Batch modifications result in less transaction log usage and messages across the network.
4. Remove unnecessary indexes, less messages and transaction log usage on modifications.
5. Scale out database design.