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 COLUMNSTORE_BUILD_THROTTLE is recorded when the parallel threads of a columnstore index build wait for the first segment to be built by a single thread. The design is deliberate: the memory grant estimate…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORERelated pillar: Performance & Troubleshooting CMEMTHREAD means workers are waiting for a memory object that is protected by a synchronisation primitive inside the engine. In plain terms, many threads want the same internal memory structure at the same time, and they…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting CMEMPARTITIONED is a wait for access to a thread-safe partitioned memory object. It is the sibling of CMEMTHREAD: when a shared memory object becomes a contention point, SQL Server can partition it by node…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting CLR_SEMAPHORE is recorded when a task executing CLR code waits to acquire a semaphore, one of the synchronisation primitives CLR threads use to gate access to limited resources. Like its sibling CLR_AUTO_EVENT, it comes…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting CLR_MANUAL_EVENT is recorded when a task performing CLR execution waits for a manual-reset event to be signalled. In practice it is mostly CLR worker threads standing by for work, the manual-reset sibling of CLR_AUTO_EVENT,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting CLR_AUTO_EVENT is recorded when a task running Common Language Runtime (CLR) code waits on an auto-reset event, which in practice is mostly CLR worker threads waiting for something to do. If CLR integration is…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting Four waits cover CLR assembly loading and task startup, and this page covers them together: Together they describe .NET code arriving and getting under way inside the engine. Are They a Problem? Rarely; none…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Storage & Capacity CHKPT is recorded at instance startup while the background checkpoint process initialises and waits for the signal that it can start. It happens once per instance start, and that is the whole story: one…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Storage & Capacity CHECKPOINT_QUEUE is recorded by the background checkpoint process while it sits idle, waiting for the next checkpoint request to be placed on its queue. Between checkpoints there is nothing for it to do, so…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting CHECK_TABLES_THREAD_BARRIER is recorded when a parallel thread in a DBCC CHECKDB (or related) command waits at a barrier for its sibling threads, either while they finish initialising, or while they finish exiting after certain…