Category: Wait Types

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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting 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:…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting 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…