Performance Monitor- Wait types

Common Significant Wait types with 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