Author: Peter Whyte

⏳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 PRINT_ROLLBACK_PROGRESS is recorded by a session that has issued ALTER DATABASE … WITH ROLLBACK IMMEDIATE (or ROLLBACK AFTER n) and is now waiting for the user transactions it killed to finish…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting PVS_PREALLOCATE is recorded by the background task that preallocates space in the Persistent Version Store (PVS) for databases with Accelerated Database Recovery (ADR) enabled. The task adapts to demand: on a quiet database it…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting PWAIT_DIRECTLOGCONSUMER_GETNEXT is recorded when a thread reading from the transaction log waits for a log block to be supplied to it. The direct log consumer interface serves the components that stream the log as…

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

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

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

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