Category: Wait Types

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability DBMIRRORING_CMD is recorded when a thread waits for various database mirroring configuration and state change operations to complete, a bucket that includes waiting on log flushes within the mirroring machinery. Books Online notes this wait…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability DBMIRROR_WORKER_QUEUE is recorded when a database mirroring worker task waits for more work to be assigned. Mirroring keeps a small crew of workers per session, and when the mirrored database is quiet, generating no log…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability DBMIRROR_SEND is a database mirroring wait recorded when a task must wait for the network communications backlog to clear before it can send mirroring messages. The mirroring session is generating log faster than the network…

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