Category: Wait Types

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: High Availability These two waits synchronise I/O against database snapshot sparse files, and this page covers both: FCB_REPLICA_READ for reads through a snapshot’s context (queries against the snapshot, or DBCC’s internal snapshot reading its own copied pages)…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting EXECSYNC is a parallelism wait recorded when the threads of a parallel plan wait for a shared construct to be built by a single thread. Unlike CXPACKET, which covers the exchange iterators…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting EXCHANGE is a wait that occurs during synchronisation inside the query processor’s exchange iterator, the operator that moves rows between threads in a parallel plan. Exchange operators (you see them as Distribute…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting EE_PMOLOCK is recorded when a thread waits for access to a thread-safe memory object used by the query execution engine while allocating certain types of memory during statement execution. The PMO in the name…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGEIOLATCH_SH Wait TypeRelated pillar: Performance & Troubleshooting EC is a wait type with two lives. It fell into disuse in older versions, then was repurposed around SQL Server 2014 as the wait recorded when a page is read from a Buffer…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting DUMP_LOG_COORDINATOR is recorded when the coordinator thread of an fn_dump_dblog operation waits for its sub-threads to complete work. fn_dump_dblog is the undocumented function for reading log records directly out of a backup (its sibling…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: Performance & Troubleshooting DPT_ENTRY_LOCK is recorded when a thread waits for access to individual entries in the dirty page list on an Availability Group readable secondary, with parallel redo threads and read queries as the contending parties.…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting DISPATCHER_QUEUE_SEMAPHORE is recorded by threads in a dispatcher pool, a special-purpose thread pool some subsystems maintain separately from the main worker pool, while they wait for work to arrive. Known users of dispatcher pools…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: Performance & Troubleshooting DIRTY_PAGE_TABLE_LOCK is recorded when a thread waits for access to the list of dirty pages on an Availability Group readable secondary, contended between parallel redo threads (which update it as they apply log) and…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting DIRTY_PAGE_POLL is recorded by the background task that supports indirect checkpoints while it sleeps between polls looking for dirty pages (modified pages in the buffer pool that need writing to disk). It wakes roughly…