In a SQL Server availability group, why do commits slow down and HADR_SYNC_COMMIT top the waits after adding a synchronous-commit secondary in a second datacenter, and what can you do?
answer
- the commit waits for someone else
- network round trip plus remote log flush
- log_send_queue_size and secondary_lag_seconds
- asynchronous commit for the far replica
basics
~20 sSynchronous commit returns only after the secondary hardens the log, so each commit pays the inter-site round trip and a remote log flush. Make the far replica asynchronous with a nearby synchronous partner, or speed up the link and log storage.
solid answer
~50 sWith `AVAILABILITY_MODE = SYNCHRONOUS_COMMIT` on both the primary and the secondary, the primary writes the log block locally, ships it, and completes the commit only after the secondary has **hardened** it to its own log; `HADR_SYNC_COMMIT` is that wait. A remote datacenter adds a network round trip and a remote disk flush to every commit. Confirm it with `sys.dm_hadr_database_replica_states` and the link latency. The usual fix is a synchronous, automatic-failover partner in the same datacenter and `ASYNCHRONOUS_COMMIT` for the remote replica, accepting seconds of possible loss there. Otherwise speed up the link and the secondary's log I/O, commit in fewer, larger transactions, or on SQL Server 2025 lower the `availability group commit time` grouping delay. If the remote replica stops answering, commits wait up to the `SESSION_TIMEOUT` (10 seconds by default) before the primary marks it `NOT SYNCHRONIZING` and carries on, at the default `REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT` of 0.
code
sql · 9 linesSELECT ar.replica_server_name,
ar.availability_mode_desc,
drs.synchronization_state_desc,
drs.log_send_queue_size,
drs.redo_queue_size,
drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
ON ar.replica_id = drs.replica_id;go deeper
Recall that synchronous commit makes the primary wait for a secondary and asynchronous commit does not.
Explain the commit steps, what hardening means, and why automatic failover needs synchronous commit on both replicas.
Diagnose HADR_SYNC_COMMIT with the replica-state DMVs and wait statistics, separate send, harden and redo delays, and choose a fix without silently giving up the durability promise.
Decide which replicas are worth a per-commit latency tax, and whether REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT trades availability for a stronger durability guarantee the business actually needs.
## What synchronous commit waits for An availability replica's **availability mode** decides whether the primary waits for it. Synchronous commit happens only when both the primary and the secondary are configured `SYNCHRONOUS_COMMIT`. In that case a commit follows these steps: 1. The primary receives the commit and writes the log record to its own transaction log, sending it to the secondaries at the same time. 2. The primary waits for confirmation from each synchronous-commit secondary. 3. Each such secondary writes the record to its own log on disk, which SQL Server calls **hardening**, and acknowledges. 4. The primary finishes commit processing and reports success to the client. The time spent in step 2 is recorded as the `HADR_SYNC_COMMIT` wait type in `sys.dm_os_wait_stats`. Hardening is not the same as applying: the secondary's redo thread replays the change into data pages later, so a synchronized replica can still show slightly old data to readers. ## Why distance hurts Local commit cost is one log flush. Synchronous commit to another datacenter adds, for every commit: - the network round trip between the sites; - the secondary's own log flush, on whatever storage it has; - any queueing when the link or the secondary cannot keep up with the log rate. A batch job that commits once per row pays that cost per row, so it slows far more than a workload that commits large transactions. SQL Server also waits briefly, 10 ms by default, to fill a log block with several commits before sending it to the secondaries; SQL Server 2025 adds the `availability group commit time` server option to change that delay. ## Measuring it Before changing anything, confirm where the time goes: - `HADR_SYNC_COMMIT` waits rising with the new replica, compared with `WRITELOG` waits for the local flush. - `sys.dm_hadr_database_replica_states`: `log_send_queue_size` (KB not yet sent), `redo_queue_size` (KB not yet redone on the secondary) and `secondary_lag_seconds`. - `sys.availability_replicas`: `availability_mode_desc` and `failover_mode_desc` for each replica, to check which ones the primary is actually waiting for. - Network latency between the sites and log-write latency on the secondary. ## Options | Option | Commit latency | Data-loss exposure at the remote site | |---|---|---| | Keep remote replica synchronous | Pays round trip on every commit | None while it is `SYNCHRONIZED` | | Make remote replica asynchronous, add a local synchronous partner | Local flush plus a nearby replica | Whatever had not been sent when the site was lost | | Faster link and secondary log storage | Lower, still distance-bound | None while synchronized | | Fewer, larger transactions in the application | Fewer waits per unit of work | Unchanged | | Lower `availability group commit time` (SQL Server 2025) | Shorter grouping delay | Unchanged | The common design is synchronous commit with `FAILOVER_MODE = AUTOMATIC` between replicas in the same datacenter, and asynchronous commit to the far one. Automatic failover requires synchronous commit on both replicas, so the remote replica becomes a target for forced failover only. ## When the synchronous replica stops answering Synchronous commit does not let a dead secondary stall the primary forever. If the secondary does not respond within the primary's `SESSION_TIMEOUT`, which defaults to 10 seconds, the primary marks it `NOT SYNCHRONIZING` and `NOT_HEALTHY` and stops waiting for it; commits then complete without that copy. While the session times out, commits stall, which shows up as a burst of `HADR_SYNC_COMMIT` waits. `REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT`, available since SQL Server 2017, changes that rule. Set to 1 or more, it makes the primary refuse commits unless that many synchronous secondaries have hardened them: no commit can exist on the primary alone, but losing those secondaries makes commits fail. The default, 0, keeps the drop-and-continue behaviour. ## Hardened is not readable Readers on a synchronous secondary still see changes only after redo. If reports there look stale while `synchronization_state_desc` says `SYNCHRONIZED`, look at `redo_queue_size`: the log is safe on disk, but the redo thread is behind. The redo queue matters for failover too. A synchronized secondary loses no committed data when it takes over, but its databases come online only after redo has caught up, so a long redo queue stretches the failover time even when it causes no loss. Two common causes are a reporting query whose schema-stability lock blocks redo of a DDL change, and a secondary with weaker CPU or storage than the primary.
- Why can a synchronized SQL Server secondary still return stale reads?Synchronous commit guarantees that the log is hardened on the secondary, not that the redo thread has applied it. Readers there see a change only after redo replays it, so a busy or blocked redo thread, visible as a growing redo_queue_size, makes a SYNCHRONIZED replica look behind even though no committed data would be lost on failover.
- What does REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT buy, and what does it cost?Set to 1 or more, it makes the SQL Server primary refuse to commit unless that many synchronous secondaries have hardened the transaction, so no commit exists on the primary alone. The cost is availability: if too few synchronous secondaries are reachable, commits fail instead of continuing unprotected. The default of 0 drops an unresponsive replica after the session timeout.
- Synchronous commit waits for the secondary to redo the change before acknowledging.
- HADR_SYNC_COMMIT means queries on the secondary are blocking the primary.
- A dead synchronous secondary blocks primary commits until it comes back.
- Automatic failover works with an asynchronous-commit secondary.
- Raising SESSION_TIMEOUT lowers synchronous-commit latency.
Assumes SQL Server 2025; the availability group commit time option is new in 2025, and REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT has existed since SQL Server 2017.