Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PARALLEL_REDO_TRAN_LIST is recorded when the main parallel redo thread on an Availability Group replica needs access to the shared list of transactions currently being redone. That list is mostly the working territory of the parallel…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PARALLEL_REDO_LOG_CACHE is recorded infrequently on an Availability Group replica after the main redo thread has hit a bottleneck, that is, following episodes of PARALLEL_REDO_FLOW_CONTROL. It relates to the log cache the redo pipeline reads from,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PARALLEL_REDO_FLOW_CONTROL is recorded on an Availability Group secondary when the main redo thread has dispatched as much log as it is allowed to the parallel redo worker threads and must wait for one of them…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: BACKUPIO and BACKUPBUFFER Wait TypesRelated pillar: Backup & Recovery PARALLEL_BACKUP_QUEUE is recorded when a thread in a parallel restore operation waits for access to a shared output result set. The commands that read backup metadata, RESTORE HEADERONLY, RESTORE FILELISTONLY, RESTORE LABELONLY,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGEIOLATCH_SH Wait TypeRelated pillar: Storage & Capacity PAGEIOLATCH_NL is the Null (NL) mode member of the I/O page latch family, a placeholder so every latch mode has a PAGEIOLATCH_* counterpart. Null mode acquires nothing, and this wait type goes unused in…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGEIOLATCH_SH Wait TypeRelated pillar: Storage & Capacity PAGEIOLATCH_KP is a wait for a Keep (KP) mode latch on a buffer page that is currently part of an I/O request. Keep mode pins the page against destruction while the I/O is in…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGEIOLATCH_SH Wait TypeRelated pillar: Storage & Capacity PAGEIOLATCH_DT is a wait for a Destroy (DT) mode latch on a buffer page that is currently part of an I/O request. Destroy mode is used when a buffer is being removed, and this…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting ONDEMAND_TASK_QUEUE is recorded by a background task while it waits for high-priority system task requests to arrive, DTC commit and abort processing being the documented example. The engine keeps this lane clear for work…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IORelated pillar: Performance & Troubleshooting OLEDB indicates SQL Server is waiting for an OLE DB provider call to return. This usually means linked server access and distributed queries, but the same interface is used internally, so the wait has a famous…