Network Management
HADR_SYNC_COMMIT Sql Wait Type
This content will describe SQL Server wait type that can be encountered more or less commonly, which includes the description, steps that should be taken, and additional resources that could be used for troubleshooting.
First published date
10/18/2021 7:16 AM
Last published date
10/18/2021 7:16 AM
Overview
HADR_SYNC_COMMIT indicates the time between when a transaction-ready to commit in the primary replica, and all secondary synchronous-commit replicas have acknowledged the hardening of the transaction commit LSN in an AG. It means a transaction in the primary replica cannot be committed until the primary replica received greater hardened LSNs from all secondary synchronous-commit replicas.
If transactions in the primary replica are slower than usual, and HADR_SYNC_COMMIT is unusually long, it means there is some performance issue in at least one Primary-Secondary replica data movement flow, or at least one secondary replica is slow in log hardening.
If transactions in the primary replica are slower than usual, and HADR_SYNC_COMMIT is unusually long, it means there is some performance issue in at least one Primary-Secondary replica data movement flow, or at least one secondary replica is slow in log hardening.
Product section
Network Performance Monitor
Cause
Description:
This wait type is only used for databases participating in an Availability Group. When modifications occur on the Primary Replica in synchronous mode it must commit all of these on the Secondary Replicas before committing on itself. This wait type accumulates during this period
This wait type is only used for databases participating in an Availability Group. When modifications occur on the Primary Replica in synchronous mode it must commit all of these on the Secondary Replicas before committing on itself. This wait type accumulates during this period
Resolution
Suggested solutions:
- If SLAs allow, investigate whether the Availability Group can be switched to asynchronous mode
- Ensure that the network between replicas is not over-utilized, if so consider upgrading
- Rewrite modification DML that performs small (row-by-row) changes. Batch modifications result in less transaction log usage and messages across the network
- Remove unnecessary indexes, less messages and transaction log usage on modifications
- Scale out database design
The content posted herein are provided as a suggestion or recommendation to you for your internal use. The information set forth herein may come from third party website or customers. SolarWinds is not liable for any downtime or any issue that may occur if you perform the following suggestions on the link provided. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.