Top Wait Statistics Indicative of Performance Issues

(Source : sentry.com)

The following wait statistics may be useful when creating a baseline or running a benchmark test. You need only collect the appropriate data from the sys.dm_os_wait_stats DMV (dynamic management view) while running your tests. You should also be certain to collect the time that the data is collected, since wait statistics are cumulative and cannot give you a moment-by-moment record of their growth over time. As with collecting PerfMon objects and counters during a baseline or benchmark test, record the results to a location not on the system under test. In addition, recognize that the polling frequency adds overhead. So collect only what you need, usually at a somewhat lower frequency than you might with PerfMon object counters, say, every 1 to 15 minutes. A few details about the top wait statistics follow:

CPU Pressure

• SOS_SCHEDULER_YIELD: Indicates waits caused by CPU pressure due to frequently yielding a scheduler among threads.
• CXPACKET: Represents the amount of wait time consumed by parallelized operations. This is not usually an indicator of “bad” behavior, although SQL Server’s default choice is to aggressively parallelize operations.

Locking

• LCK_X, LCK_M_U, & LCK_M_X: Shows the time spent waiting on long term blocking. Often an indicator of poorly coded T-SQL, transactions, or isolation levels.

Memory

• PAGELATCH_X: Shows the time spent waiting for a data buffer latch. A high value may indicate a shortage of memory for SQL Server’s data buffer.
• LATCH_X: Represents the time spent waiting for a non-data buffer latch, such as in the plan cache. A high value may indicate a shortage of SQL Server’s non-data buffer.
• RESOURCE_SEMAPHORE: Shows the time spent waiting for a memory grant from Windows Server to SQL Server. A high value may indicate that other processes on the server also need memory and are “stealing” it back from SQL Server.

I/O

• PAGEIOLATCH_X: This is the time spent waiting for data to be loaded from the I/O subsystem to the SQL Server data cache buffers. High values may indicate either a slow I/O subsystem and/or inadequate memory to hold the amount of data needed by the workload.
• WRITELOG & LOGBUFFER: This is the amount of time spent managing the transaction log I/O subsystem. High values in either counter may indicate that the SQL Server database transaction log is on a slow or overworked I/O subsystem.
• ASYNC_IO_COMPLETION & IO_COMPLETION: These wait statistics show the amount of time spent waiting for general I/O operations to complete. High values may indicate a slow or overworked I/O subsystem.

Network Pressure

• ASYNC_NETWORK_IO: Indicates the overall wait time SQL Server spends waiting for asynchronous network I/O, the most commonly used sort of network I/O. High values may indicate an inadequate or overused NIC.