Category: Wait Types

LAZYWRITER_SLEEP is recorded by the lazy writer thread while it sleeps between checks of the buffer pool free list. Roughly once a second it wakes, checks whether memory pressure requires freeing buffers, does whatever little work exists, and goes back to sleep. Each sleeping second lands in this wait, per lazy writer thread (large NUMA…

KSOURCE_WAKEUP is recorded by SQL Server’s background control task while it waits to be told, by the Windows Service Control Manager, to shut the instance down. One thread (typically SPID 7 or thereabouts) holds this wait for the entire life of the instance. Its accounting quirk: sys.dm_os_wait_stats shows it with near-zero time because the wait…

IO_QUEUE_LIMIT is a wait recorded when a session cannot issue another asynchronous I/O because its queue of outstanding async I/Os is already at its limit. The engine caps how much async I/O a single task can have in flight; when the cap is hit, the task waits here until some of the in-flight I/O completes.…

IMPPROV_IOWAIT is recorded when a thread has issued an asynchronous read against a bulk load source file and is waiting for the read to complete. It belongs to BULK INSERT, OPENROWSET(BULK…), BCP, and similar import paths: the engine reads the source data file, and time spent waiting on those reads lands here. It is effectively…

HTREPARTITION completes the batch-mode hash table wait family. When a parallel batch-mode hash join or aggregation discovers its shared hash table will not fit as built, it repartitions the table’s input to process it in smaller pieces, and every thread must synchronise around that repartitioning. This wait is the queue at that synchronisation point. As…

HTREINIT is part of the batch-mode hash table wait family. Parallel batch-mode hash joins and aggregations share one hash table across threads (since SQL Server 2014), and some join strategies process their input in multiple passes or partial joins. HTREINIT is the threads synchronising before the shared hash table is reset for the next partial…

HTMEMO belongs to the batch-mode hash table wait family (HTBUILD, HTDELETE, HTREINIT, HTREPARTITION). Since SQL Server 2014, parallel batch-mode hash joins and aggregations use one shared hash table across all threads; HTMEMO is the synchronisation before threads scan that shared table to output matches and non-matches, the pattern used by outer joins and some aggregations.…

HTDELETE is the best known of the batch-mode hash table waits. Since SQL Server 2014, parallel batch-mode hash joins and aggregations share a single hash table across threads (2012 built one per thread), trading memory savings for synchronisation. HTDELETE is threads waiting on that synchronisation at the end of the hash join or aggregation, when…

HTBUILD is one of the batch-mode hash table waits (with HTDELETE, HTMEMO, HTREINIT, and HTREPARTITION). From SQL Server 2014 onward, parallel batch-mode hash joins and aggregations share one hash table between all threads instead of building one per thread, which saves a lot of memory but means the threads must synchronise. HTBUILD specifically is threads…

HP_SPOOL_BARRIER is a curiosity: a wait type introduced across various versions as part of a bug fix for parallel spool behaviour, then removed again in the very next cumulative update for each of those versions because the fix itself was flawed. Paul White documented the underlying bug and its circumstances in detail. It exists in…