SQL DBA Blog: Home

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…

read more

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…

read more

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

read more

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…

read more

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…

read more

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…

read more

HADR_WORK_QUEUE is recorded by Availability Group background worker threads while they sit waiting for new work to appear in the AG work queue. AGs keep a pool of ready workers for replication tasks; a worker with nothing to do parks here until something is assigned. Idle workers waiting for work is the normal, healthy state…

read more

HADR_WORK_POOL is recorded when a thread waits for access to the Availability Group work queue object, the concurrency control around AG background workers getting into position to wait for work. It sits one step before its sibling HADR_WORK_QUEUE (workers idle waiting for work); this one is workers coordinating access to the queue itself. Both are…

read more

HADR_TIMER_TASK is recorded around the Availability Group timer machinery: threads waiting for access to the list of AG timers, plus the deliberate waits between runs of periodic AG tasks. A task that runs every ten seconds spends roughly ten seconds in this wait between executions, so the wait’s total mostly measures scheduled idleness. Every AG-enabled…

read more

HADR_SEEDING_LIMIT_BACKUPS is recorded during Availability Group automatic seeding operations, and its observed behaviour is long waits, thousands of seconds in reported cases, lasting for the duration of some seeding runs. The name suggests throttling around concurrent backup-style operations inside the seeding machinery (seeding streams a backup under the hood). Documentation is thin; field evidence is…

read more