Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting MSQL_DQ is recorded while a task waits for a distributed query operation to finish, a query spanning a linked server or another OLE DB data source. The wait runs for the duration of the…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting METADATA_LAZYCACHE_RWLOCK is a wait on the reader-writer lock protecting lazily-populated metadata caches, internal caches of object metadata that fill on first use rather than up front. Threads reading a cache while another thread populates…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting MEMORY_ALLOCATION_EXT is recorded when a thread switches to preemptive mode while allocating memory from the OS or from SQL Server’s internal allocators. The switch exists so the allocation code does not have to keep…

⏳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…