SQL DBA Blog: Home

CXSYNC_PORT is the second of the parallelism waits split out of CXPACKET for Azure SQL Database and SQL Server 2022. It is recorded when parallel plan threads wait to open, close, and synchronise exchange iterator ports, the connection points through which producer threads hand rows to consumer threads. A long-running blocking operator, a big sort…

read more

CXSYNC_CONSUMER is one of the newer parallelism waits, recorded when consumer threads in a parallel plan wait to reach an exchange iterator synchronisation point. It first appeared in Azure SQL Database and Managed Instance and arrived on-premises with SQL Server 2022, where it was split out of CXPACKET (together with CXSYNC_PORT) to make parallel plan…

read more

CXROWSET_SYNC is a wait recorded during a parallel range scan, when a worker thread needs exclusive access to the scan’s shared parent rowset to coordinate with the other child threads. In a parallel scan, threads grab ranges of pages from a shared structure as they finish their previous range; this wait is the brief queue…

read more

COMMIT_TABLE is recorded when a thread waits for access to the in-memory portion of the hidden commit table (sys.syscommittab) that underpins Change Tracking. Every transaction that modifies a change-tracked table adds a row to this structure, and the storage engine touches it from several directions: transaction commits, checkpoints, and the periodic automatic cleanup that trims…

read more

COLUMNSTORE_BUILD_THROTTLE Wait Type in SQL Server 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 for a columnstore build assumes segments of a million rows, so SQL Server builds the first segment…

read more

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 queue. This wait is usually a concurrency pattern problem, not a disk problem. It often appears with heavy parallel…

read more

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 or by CPU so threads mostly touch their local partition. CMEMTHREAD is contention on an unpartitioned object; CMEMPARTITIONED is the (much…

read more

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 with the territory of CLR integration being active, whether through user assemblies or the engine’s own internal CLR usage. Most instances…

read more

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, and it appears wherever CLR integration is active, including through the engine’s internal CLR use (spatial types, hierarchyid, and friends). Idle…

read more