Category: Wait Types

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting 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:…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting 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;…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_RS_S is a wait to acquire a Range Shared lock: a shared lock on a key value plus a shared lock on the range between it and the previous key. Range…