Author: Peter Whyte

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

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

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

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

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

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