Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability DBMIRROR_EVENTS_QUEUE is recorded by the main database mirroring thread while it waits for events to process. It only exists where database mirroring is configured, and on a quiet principal the individual waits can run very…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability DBMIRROR_DBM_MUTEX is a database mirroring wait that occurs only on the mirror server, when a thread waits for parallel threads that are replaying (redoing) log records on the mirror database. It is synchronisation inside the…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting DAC_INIT is recorded while the TDS listener for the Dedicated Admin Connection (DAC) initialises at instance startup. The DAC is the emergency side door: a reserved connection with its own scheduler and resources that…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting 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…

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