Category: Wait Types

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

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

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

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

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

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

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

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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability HADR_NOTIFICATION_DEQUEUE is recorded by the background task that processes Windows Server Failover Clustering notifications while it waits for the next notification to arrive. Availability Groups need to hear about cluster events (node changes, resource moves,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability HADR_LOGPROGRESS_SYNC is recorded when a thread waits for exclusive access to the structure holding the latest log-harden LSN on an Availability Group primary with a synchronous replica. That structure is the release gate for synchronous…