Category: Wait Types

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting QDS_DYN_VECTOR is recorded when a thread waits for access to one of Query Store’s thread-safe internal data structures (a dynamic vector, per the name). Query Store’s in-memory machinery is shared across every session whose…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting QDS_LOADDB is recorded while Query Store loads its data for a database during that database’s startup, after instance restarts, failovers, crash recovery, or the database coming back online. Here is the part that bites:…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting QDS_PERSIST_TASK_MAIN_LOOP_SLEEP is recorded by the Query Store background persistence task while it sleeps between rounds of flushing captured query metrics from memory to disk. Query Store collects runtime stats in memory and writes them…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting QDS_STMT is recorded when a thread waits for access to Query Store’s map of query hashes, the structure that tracks which queries have been seen. Registering a previously-untracked query requires latching that map exclusively…

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