This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
A HADR_SYNC_COMMIT wait occurs when the session is waiting for a transaction to commit or rollback when the database is part of a Database Availability Group (High Availability.)
A transaction is considered committed when hardened (written) to the local database log file and when hardened (written) to the configured number of secondaries to establish the quorum level. The transaction needs only to be hardened (written to secondary database log file), not recovered on the secondary.
The Log Sequence Number (LSN) is used to track hardened database log records. As soon as the hardened LSN advances to include the commit or rollback record the session wakes and continues execution.
The default behavior for HADR_SYNC_COMMIT is to wait for up to (woken as soon as LSN harden level is achieved) 2 seconds, usually more than enough time to achieve the harden LSN level. However, if the desired LSN harden level has yet to be achieved the session validates the state of the database (lease has not expired, online, etc.) and returns to wait. The wait and check state loop continues until the LSN harden level is achieved or the high availability state is changed.
Because the HADR_SYNC_COMMIT uses the 2 second wait and retry looping, monitoring can be cumbersome. For example, the wait_time provided in sys.dm_exec_requests for a HADR_SYNC_COMMIT advances to ~2sec, then restarts the wait. Use the transaction_id column in sys.dm_exec_requests to join with the transaction DMVs and better determine the elapsed wait time for the commit or rollback request.
The XEvent hadr_db_commit_mgr_harden_still_waiting can also be used to determine the elapsed HADR_SYNC_COMMIT wait times.
Newer versions of SQL Server argument may include:
- Trace flag 3992 introducing a longer, individual wait period. The looping wait is doubled (from 2, then 4, then 8, then 16, and finally 20) capped at a max of 20 seconds for individual wait period within the loop.
- The elapsed wait_resource column in the sys.dm_exec_requests provides the LSN and elapsed wait time: <HadrSyncCommit Elapsed=\"%I64u ms\" LSN=\"%s\"/>