Mapping between performance counter and SQL Server wait type.

• Lock waits - LCK_M_*

  Lock waits occur when a query is waiting for a lock to be released by another transaction. Locks are used to ensure the consistency of data. SQL Server has various lock types (e.g., Shared, Exclusive), and the LCK_M_* prefix indicates different lock modes such as:

  • LCK_M_S: Shared lock
  • LCK_M_X: Exclusive lock
  • LCK_M_U: Update lock

  Examples of scenarios:

  • A read operation is waiting for a write lock to be released (LCK_M_S).
  • A write operation is waiting for another write lock to be released (LCK_M_X).

• Log buffer waits - LOGBUFFER

  This wait type occurs when a session is waiting for space to become available in the log buffer to write log records. The log buffer is a memory area where SQL Server stores transaction log records before writing them to disk.

  Typical cause:

  • Heavy transactional workloads causing the log buffer to fill up faster than it can be written to disk.

• Log write waits - WRITELOG

  This wait type occurs when a session is waiting for the log manager to write log records to disk. It indicates the time spent waiting for the transaction log to be flushed to disk.

  Typical cause:

  • Disk I/O contention or slow disk subsystems impacting log write performance.

• Memory grant queue waits - RESOURCE_SEMAPHORE

  This wait type occurs when a query is waiting for sufficient memory to execute. SQL Server uses memory grants for operations such as sorts and hash joins.

  Typical cause:

  • Insufficient memory available for query execution due to high memory usage by other processes or large queries.

• Network IO waits - ASYNC_NETWORK_IO

  This wait type occurs when SQL Server is waiting for a network packet to be sent or received. It can be related to network latency or throughput issues.

  Typical cause:

  • Network latency or slow network connections impacting data transmission.

• Non-Page latch waits - LATCH_*

  These waits occur for internal memory structures and synchronization objects not related to disk I/O. Latches are lightweight synchronization objects used to protect internal data structures.

  Typical cause:

  • Contention on internal memory structures, such as data buffers or internal caches.

• Page IO latch waits - PAGEIOLATCH_*

  These waits occur when SQL Server is waiting for a data page to be read from disk into memory. This can indicate disk I/O bottlenecks.

  Examples:

  • PAGEIOLATCH_SH: Waiting for a page to be read into memory for a shared latch.
  • PAGEIOLATCH_EX: Waiting for a page to be read into memory for an exclusive latch.

• Page latch waits - PAGELATCH_*

  These waits occur for latch operations on in-memory pages. Unlike page I/O latches, these do not involve disk reads.

  Examples:

  • PAGELATCH_SH: Waiting for a shared latch on an in-memory page.
  • PAGELATCH_EX: Waiting for an exclusive latch on an in-memory page.

• Thread-safe memory objects waits - SOS_MEMORY_TOPLEVELBLOCKALLOCATOR

  This wait type occurs when threads are waiting for memory allocation using the SOS (SQL Operating System) memory allocator.

  Typical cause:

  • High contention on memory allocation for thread-safe memory objects.

• Transaction ownership waits - XACT_OWNERSHIP

  This wait type occurs when a session is waiting to acquire ownership of a transaction. This can happen during activities like distributed transactions.

  Typical cause:

  • Contention on transaction objects or coordination of distributed transactions.

• Wait for the worker - THREADPOOL

  This wait type occurs when there are no worker threads available to execute tasks. SQL Server uses a pool of worker threads to handle user requests.

  Typical cause:

  • All worker threads are busy, typically due to a high number of concurrent requests or long-running queries.

• Workspace synchronization waits - EXECSYNC

  This wait type occurs when a session is waiting for synchronization of the execution workspace. This can involve coordination between different parts of the query execution process.

  Typical cause:

  • Synchronization issues between execution tasks, such as parallel query execution coordination.

Recap Table: