MosherData

When there is a SQL Server performance issue, most people may have no idea where to start looking. One of my favorite places to check is the wait stats since they provide a good indication of the general health of the server before drilling down further.

Waits measure things like how busy the server is, if a specific resource is bogged down, and if a resource (RAM, CPU, Disk) is a bottleneck. They are useful for general server contention but may not be as useful for specific queries.

Objects to check for waits:

• sys.dm_os_waiting_tasks: Shows current waits.
• sys.dm_os_wait_stats: Shows logged waits after waits have completed.

Example query to check waits:

This query retrieves the types of waits, their total wait times in seconds, the average wait time per task in milliseconds, the count of waiting tasks, and the maximum wait time in seconds. The results are ordered by the total wait time in descending order.

Common Wait Stats and Their Descriptions:

1. SOS_SCHEDULER_YIELD:
   • Description: Indicates CPU contention. This wait occurs when a task voluntarily yields the CPU to let other tasks run.
   • Resource Type: CPU
2. PAGEIOLATCH_XX (e.g., PAGEIOLATCH_SH, PAGEIOLATCH_EX):
   • Description: Indicates waits on I/O operations for pages to be read from disk into memory. The suffix (_SH, _EX) denotes different latch types.
   • Resource Type: Disk I/O
3. PAGELATCH_XX (e.g., PAGELATCH_SH, PAGELATCH_EX):
   • Description: Indicates waits on in-memory page latches, often due to contention on buffer pages.
   • Resource Type: Memory/RAM
4. CXPACKET:
   • Description: Indicates parallelism waits. This wait type occurs when tasks are waiting for parallel operations to complete.
   • Resource Type: CPU (Parallelism)
5. WRITELOG:
   • Description: Indicates waits for log writing operations. Often due to slow disk subsystems where transaction logs are stored.
   • Resource Type: Disk I/O
6. LCK_M_XX (e.g., LCK_M_S, LCK_M_X):
   • Description: Indicates waits due to locking. The suffix (_S, _X) denotes different lock modes (shared, exclusive).
   • Resource Type: General (Locking contention)
7. ASYNC_NETWORK_IO:
   • Description: Indicates waits on network I/O operations. Often due to slow network response times or issues on the client side.
   • Resource Type: Network
8. RESOURCE_SEMAPHORE:
   • Description: Indicates waits due to memory grants. Occurs when queries need more memory than available, leading to memory contention.
   • Resource Type: Memory/RAM
9. IO_COMPLETION:
   • Description: Indicates waits for I/O operations to complete. General indication of disk performance issues.
   • Resource Type: Disk I/O
10. BUFFER_IO:
    • Description: Indicates waits on buffer I/O operations, typically due to reading or writing data to disk.
    • Resource Type: Disk I/O
11. BACKUPIO:
    • Description: Indicates waits during backup operations. Generally related to disk I/O or backup device performance.
    • Resource Type: Disk I/O
12. CLR_SEMAPHORE:
    • Description: Indicates waits related to CLR (Common Language Runtime) execution, often related to memory pressure or CPU contention.
    • Resource Type: Memory/CPU

How to Use This Information:

• CPU Contention: Look for SOS_SCHEDULER_YIELD, CXPACKET, and CLR_SEMAPHORE.
• Disk I/O Contention: Look for PAGEIOLATCH_XX, WRITELOG, IO_COMPLETION, BUFFER_IO, and BACKUPIO.
• Memory Contention: Look for PAGELATCH_XX, RESOURCE_SEMAPHORE, and CLR_SEMAPHORE.
• Network Contention: Look for ASYNC_NETWORK_IO.
• Locking Contention: Look for LCK_M_XX.