Category: Wait Types

XE_TIMER_MUTEX is recorded when a thread waits for access to the data structure holding the Extended Events engine’s timers, such as those implementing each session’s MAX_DISPATCH_LATENCY. Timer registration and firing serialise briefly on this mutex. It completes the XE timer trio with XE_TIMER_EVENT (the timers ticking) and the dispatcher waits (buffers being delivered). Is It…

XTP_PREEMPTIVE_TASK appears to be a generic wait for background worker threads involved in In-Memory OLTP processing, running preemptively, so the thread stays under Windows control (state RUNNING) until its work completes. Documentation labels it internal use only. It is the XTP family’s catch-all background wait. Is It a Problem? Normally no; it accumulates as XTP…

PARALLEL_REDO_TRAN_TURN is recorded on an Availability Group readable secondary when a parallel redo thread must redo a log record, but a prerequisite log record has not yet been redone by another thread. Some operations demand strict ordering, page splits and the creation of forwarded records in heaps are the documented ones, and when their log…

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 redo worker threads, so the main thread’s occasional visits usually get straight in, and this wait stays rare. Another of…

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, and it appears as an occasional after-effect of the pipeline having been throttled. It is one of the minor bookkeeping…

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 to finish before dispatching more. The same wait can also appear during crash recovery of a standalone database, which uses…

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…

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…

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…

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…