Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting QRY_PROFILE_LIST_MUTEX is recorded when a thread waits for access to a profile list inside the query profiling statistics mechanism, the infrastructure behind lightweight query execution statistics (live query stats, sys.dm_exec_query_profiles, and the lightweight profiling…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting QUERY_TASK_ENQUEUE_MUTEX appears to be recorded when a thread involved in certain batch-mode query operations waits for its sibling threads to finish initialising. Batch-mode execution spins up cooperating thread sets, and their startup…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability REDO_THREAD_PENDING_WORK is recorded on an Availability Group secondary when a redo thread is waiting to be signalled that more log has arrived to apply. When the secondary is fully caught up, the redo thread has…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: High Availability REPLICA_WRITES is recorded while a task waits for page writes to a database snapshot or a DBCC replica to complete. Both targets are the same machinery: user-created database snapshots receive copy-on-write pages, and DBCC CHECKDB…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: BACKUPIO and BACKUPBUFFER Wait TypesRelated pillar: Performance & Troubleshooting REQUEST_DISPENSER_PAUSE is recorded while a task waits for all outstanding I/O against a database to complete, so that I/O can be frozen for an external snapshot backup. It is the setup phase…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting RESERVED_MEMORY_ALLOCATION_EXT is recorded when a thread switches to preemptive mode while allocating memory that was already reserved for its query, in other words, drawing down its execution memory grant. The preemptive switch spares the…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting RESOURCE_GOVERNOR_IDLE is recorded when a query is forced to sit idle because of a Resource Governor CPU cap (CAP_CPU_PERCENT). A cap is a hard ceiling: where the softer MAX_CPU_PERCENT only bites under contention, CAP_CPU_PERCENT…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting RESOURCE_SEMAPHORE_MUTEX is a wait for the mutex protecting the critical section where queries acquire the resources they need to compile or execute, memory grants and thread reservations. Only one thread at a time can…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting RESOURCE_SEMAPHORE_SMALL_QUERY is a wait on the small-query memory grant semaphore. SQL Server reserves a separate slice of grant memory for cheap queries so they never queue behind memory-hungry monsters. This wait means even that…