Category: Wait Types

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting PREEMPTIVE_OS_WAITFORSINGLEOBJECT is recorded when a thread calls the Windows WaitForSingleObject function to synchronise with an external client process communicating through that kernel object. In practice it is commonly seen together with ASYNC_NETWORK_IO, depending on…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting PREEMPTIVE_SB_STOPENDPOINT is recorded when a thread calls out to Windows while shutting down a Service Broker endpoint, the network listener that lets Broker exchange messages between instances. Stopping an endpoint involves OS-level teardown of…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting PREEMPTIVE_SP_SERVER_DIAGNOSTICS is recorded when the background health-monitor thread is actually running the sp_server_diagnostics procedure, the component-by-component health check added in SQL Server 2012 that feeds AG and FCI failure detection. Its sibling SP_SERVER_DIAGNOSTICS_SLEEP covers…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting Five preemptive waits cover Extended Events session lifecycle operations, and this page covers the set: They meter the administrative edges of Extended Events rather than event capture itself. Are They a Problem? No; none…

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