SQL Server Wait Types Explained

-- Common Significant Wait types with BOL explanations

-- *** Network Related Waits ***

-- ASYNC_NETWORK_IO Occurs on network writes when the task is blocked behind the network

-- *** Locking Waits ***

-- LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock

-- LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock

-- LCK_M_S Occurs when a task is waiting to acquire a Shared lock

-- *** I/O Related Waits ***

-- ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish

-- IO_COMPLETION Occurs while waiting for I/O operations to complete.

-- This wait type generally represents non-data page I/Os. Data page I/O completion waits appear

-- as PAGEIOLATCH_* waits

-- PAGEIOLATCH_SH 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.

-- PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer that is in an I/O request.

-- The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

-- WRITELOG Occurs while waiting for a log flush to complete.

-- Common operations that cause log flushes are checkpoints and transaction commits.

-- PAGELATCH_EX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.

-- The latch request is in Exclusive mode.

-- BACKUPIO Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

-- *** CPU Related Waits ***

-- SOS_SCHEDULER_YIELD Occurs when a task voluntarily yields the scheduler for other tasks to execute.

-- During this wait the task is waiting for its quantum to be renewed.

-- THREADPOOL Occurs when a task is waiting for a worker to run on.

-- This can indicate that the maximum worker setting is too low, or that batch executions are taking

-- unusually long, thus reducing the number of workers available to satisfy other batches.

-- CX_PACKET Occurs when trying to synchronize the query processor exchange iterator

-- You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

-- Often caused by missing indexes or poorly written queries