SQL DBA Blog: Home

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, build their output rows from parallel workers, and those workers must take turns inserting into the shared rowset. It is queueing around a…

read more

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 practice. Its siblings do the family’s real work: PAGEIOLATCH_SH for pages being read from disk, PAGEIOLATCH_EX and PAGEIOLATCH_UP for write-side I/O…

read more

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 flight, and since KP conflicts only with Destroy mode, actual waits require an improbable collision. It rounds out the PAGEIOLATCH_* family…

read more

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 wait type exists largely for completeness, so that every latch mode has a matching PAGEIOLATCH_* wait. Real workloads acquire pages for…

read more

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 that cannot queue behind ordinary tasks, and the lane being empty is its normal state. Books Online says it directly: long…

read more

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 false positive built in. The false positive first: DBCC CHECKDB and its siblings generate OLEDB waits, because the DBCC…

read more

MSQL_XP is recorded while a task waits for an extended stored procedure (XP) to finish. Extended stored procedures are external code, DLLs loaded into the SQL Server process, called through the old XP interface: xp_cmdshell, xp_fixeddrives, third-party backup and monitoring XPs, and vendor add-ons. The wait lasts exactly as long as the external code runs,…

read more

MSQL_DQ is recorded while a task waits for a distributed query operation to finish, a query spanning a linked server or another OLE DB data source. The wait runs for the duration of the remote call, and the engine also uses it to detect potential MARS application deadlocks in distributed scenarios. Together with OLEDB, it…

read more

METADATA_LAZYCACHE_RWLOCK is a wait on the reader-writer lock protecting lazily-populated metadata caches, internal caches of object metadata that fill on first use rather than up front. Threads reading a cache while another thread populates or invalidates it queue on this lock. Version note: this name belongs to older versions; SQL Server 2012 replaced it with…

read more

MEMORY_ALLOCATION_EXT is recorded when a thread switches to preemptive mode while allocating memory from the OS or from SQL Server’s internal allocators. The switch exists so the allocation code does not have to keep checking whether the thread’s scheduling quantum has run out; the thread steps outside cooperative scheduling, allocates, and steps back in. Memory…

read more