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_TRANSMITTER is recorded when the Service Broker transmission queue is empty and the two internal transmitter threads (the ones that send messages to remote endpoints) wait for work. Two threads waiting simultaneously gives this…

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