SQL DBA Blog: Home

The LOGPOOL_* waits all belong to one structure, so this page covers the family: LOGPOOL_CACHESIZE, LOGPOOL_CONSUMER, LOGPOOL_CONSUMERSET, LOGPOOL_FREEPOOLS, and LOGPOOL_REPLACEMENTSET. The log pool is an in-memory cache of recently flushed log blocks. Its purpose: log readers, the components that consume the transaction log after it is written, can read recent log from memory instead of…

read more

LOGMGR_RESERVE_APPEND is a wait recorded when a thread needs to write a log record but there is no free space left in the transaction log. The thread first tries to grow the log file; if it cannot, and the database is in simple recovery (or behaving like it), it waits one second at a time…

read more

LOGMGR_QUEUE is recorded by the log writer thread (threads, plural, from SQL Server 2016 onward) while waiting for something to do: either new log to flush or an in-flight write to complete. The log writer is a permanent background resident, and whenever your workload is not committing transactions, it parks here. It is one of…

read more

LOGMGR is a wait that occurs when a database is being closed and a thread has to wait for all outstanding transaction log I/O to complete before the log can be shut down. Databases close during instance shutdown, when a database is taken offline or detached, and when the AUTO_CLOSE option closes an idle database.…

read more

LCK_M_SIX is a wait to acquire a Shared With Intent Exclusive (SIX) lock. SIX is a combination lock: the session wants to read the whole table (shared) while also modifying some rows within it (intent exclusive). SQL Server uses it for scan-then-update patterns, where a query reads broadly but writes selectively. You will see this…

read more

LCK_M_SCH_S is a wait to acquire a Schema Stability (SCH-S) lock. Every query takes SCH-S locks on the objects it touches, during compilation and execution, to guarantee the table’s structure does not change underneath it. SCH-S is the most compatible lock in the engine; it coexists with everything except one thing: a Schema Modification (SCH-M)…

read more

LCK_M_SCH_M is a wait to acquire a Schema Modification (SCH-M) lock. DDL needs this lock to change an object’s structure: ALTER TABLE, offline index rebuilds, partition switches, truncates, and drops all take SCH-M. It is the least compatible lock in the engine; it conflicts with every other lock, including the Schema Stability locks that every…

read more

LCK_M_RX_X is a wait to acquire an Exclusive Range lock: an exclusive lock on a key value plus an exclusive lock on the range between it and the previous key. Like all range locks it only exists under SERIALIZABLE isolation, where writers must lock the gaps between keys so no other transaction can insert a…

read more

LCK_M_RS_U is a wait to acquire an Update lock on a key together with an Update Range lock on the gap between it and the previous key. It belongs to the serializable range-lock family: SQL Server takes it when a serializable transaction searches a range it intends to update, using update-mode semantics to stay deadlock-friendlier…

read more