SQL DBA Blog: Home

Part of the SQL Server Wait Types Library.Related deep dive: HADR_SYNC_COMMIT Wait Type. PWAIT_HADR_WORKITEM_COMPLETED is recorded by a thread waiting for an asynchronous Availability Group work task to complete. AG management operations frequently hand their real work to async tasks, adding a database to an AG, removing one, joining a replica, and the initiating thread…

read more

Part of the SQL Server Wait Types Library.Related deep dive: IO_COMPLETION Wait Type. QDS_ASYNC_QUEUE is recorded when a thread waits for access to the queue of Query Store information that is being asynchronously persisted to disk. Query Store collects runtime stats in memory and stages them through this queue for background writing; the wait is…

read more

Part of the SQL Server Wait Types Library.Related deep dive: IO_COMPLETION Wait Type. The longest wait name most DBAs will ever paste into a search engine is also one of the least interesting: QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP is recorded by the Query Store cleanup task while it sleeps between runs that remove old query information from the store.…

read more

Part of the SQL Server Wait Types Library.Related deep dive: RESOURCE_SEMAPHORE Wait Type. 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 queries it tracks, so brief synchronisation on its…

read more

Part of the SQL Server Wait Types Library.Related deep dive: IO_COMPLETION Wait Type. 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: by default (before SQL Server 2019), that load…

read more

Part of the SQL Server Wait Types Library.Related deep dive: IO_COMPLETION Wait Type. 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 out on an interval (the DATA_FLUSH_INTERVAL_SECONDS setting, 15…

read more

Part of the SQL Server Wait Types Library.Related deep dive: RESOURCE_SEMAPHORE Wait Type. 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 to add the new hash, so every genuinely…

read more

Part of the SQL Server Wait Types Library.Related deep dive: SOS_SCHEDULER_YIELD Wait Type. 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 that later versions enable by default). Mostly invisible…

read more

Part of the SQL Server Wait Types Library.Related deep dive: CXPACKET and CXCONSUMER Wait Types. 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 coordination passes through this mutex. It keeps company…

read more

Part of the SQL Server Wait Types Library.Related deep dive: HADR_SYNC_COMMIT Wait Type. 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 nothing to do and parks here until…

read more