Author: Peter Whyte

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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability DBMIRRORING_CMD is recorded when a thread waits for various database mirroring configuration and state change operations to complete, a bucket that includes waiting on log flushes within the mirroring machinery. Books Online notes this wait…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability DBMIRROR_WORKER_QUEUE is recorded when a database mirroring worker task waits for more work to be assigned. Mirroring keeps a small crew of workers per session, and when the mirrored database is quiet, generating no log…