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 BROKER_TRANSMISSION_WORK is one of the sparsely documented Service Broker waits; the best available understanding is that it behaves like BROKER_TRANSMITTER, threads in the message transmission machinery waiting for transmission work to become available. It…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting BROKER_RECEIVE_WAITFOR is recorded when a session has issued a WAITFOR (RECEIVE …) against a Service Broker queue and is waiting for a message to arrive. Queue readers are designed to block until there is…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting Service Broker has a cluster of internal waits around its startup, shutdown, and endpoint machinery that share one story, so this page covers them together: Are They a Problem? No. Every one of these…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting BROKER_EVENTHANDLER is recorded by the per-instance Service Broker primary event handler thread while it waits for something to handle: a Broker timeout event, or Service Broker starting up or shutting down. One thread, always…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting BPSORT is recorded when a thread participates in a batch-mode sort, the columnstore-adjacent sort implementation (batch mode required columnstore involvement before SQL Server 2019; since then batch mode on rowstore widened the…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting BMPALLOCATION is a wait associated with allocating bitmap structures used by parallel query plans. Bitmap operators let SQL Server filter rows early during parallel hash joins: the build side produces a bitmap…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: BACKUPIO and BACKUPBUFFERRelated pillar: Storage & Capacity ASYNC_IO_COMPLETION means SQL Server issued asynchronous I/O against a data file and is waiting for it to complete. Unlike the PAGEIOLATCH_* family, which covers ordinary page reads into the buffer pool, this wait belongs…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting ASYNC_DISKPOOL_LOCK is recorded when a thread synchronises with the worker threads that manage long-duration file operations: creating and deleting files, zero-initialising new file space, and encrypting files for Transparent Data Encryption. Those operations run…