Author: Peter Whyte

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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting CHECK_TABLES_INITIALIZATION is recorded when a parallel thread belonging to a DBCC CHECKDB (or CHECKTABLE/CHECKFILEGROUP) command initialises and has to wait on a mutex that another initialising thread currently holds. Parallel DBCC spins up a…