Why Your Synchronous SQL Commit Waits on the VPN Round Trip

Write latency jumped after a replica moved to the second site, and the tunnel is the newest component, so it takes the blame. The tunnel may be innocent: inside a synchronous-commit availability group the primary cannot acknowledge a commit until a remote log write completes, so every transaction pays a round trip however many megabits the link can carry. Throughput measures bytes per second, latency measures how long one exchange takes, and a commit needs only one small exchange to finish. One number settles the argument: the commit duration of a small transaction, measured inside the availability database and again against a local table on the same instance.

What the primary actually waits for

Under synchronous commit the primary holds the commit acknowledgement until the partner confirms its log records are hardened. Hardened means durably written to the secondary's transaction log, not redone into its data pages. Redo can trail by minutes without adding a millisecond to the primary's commit.

Microsoft's availability-mode documentation walks through that handshake and describes what the platform does when a partner drops out. A replica marked synchronous is a rule about waiting, not a standing promise that a healthy remote copy exists for every commit. The disconnected path is defined separately, and it is not simply that the commit fails.

So the wait you observe is a sum: log transport, scheduling on both hosts, and the remote storage write. A secondary with a saturated log volume adds time that no speed test will show. That disk rarely gets measured when the complaint names the VPN.

Bandwidth is not round-trip delay

A speed test floods the path briefly and reports a rate. A commit asks something narrower: how long one small message takes to reach the far side, be persisted, and come back. Path length, queueing and packet loss set that number, and none of them improve when the provider raises the ceiling.

A "fast" tunnel can therefore add a visible penalty to a write-heavy workload built from many small sequential transactions. The same byte total split across a thousand commits pays the round trip a thousand times; one bulk transfer pays it once.

Ask the network owner for delay and loss measured over the replica route itself under representative load. A ping from an office laptop to the VPN gateway, or a speed test to a nearby node, attributes almost nothing to the route the log actually travels. Get the measurement window to overlap the hours when writes are slow.

Read the replica state before changing anything

Start from what the instances report about themselves, because the two sides can disagree about how healthy the partnership is and a dashboard average hides which queue is moving. The queries below answer different questions: one reads per-database replication state, the other reads how each replica is configured. Run both before touching a mode.

SELECT ag.name AS ag_name, drs.database_id, 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_groups AS ag ON ag.group_id = drs.group_id;

SELECT ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc
FROM sys.availability_replicas AS ar;

The first result separates log transport from redo. log_send_queue_size climbing while redo_queue_size stays flat means the log has not arrived; the reverse means it arrived and replay is behind. Only the first case sits on the commit path.

The second result confirms which replicas are really configured the way you assume, since a mode changed during an earlier incident tends to stay changed and undocumented. Read the row you care about rather than the group default: availability_mode_desc and failover_mode_desc are per replica. secondary_lag_seconds only exists on SQL Server 2016 and later, so an older build leaves you with the send and redo queue columns alone.

Then time a representative write instead of trusting a dashboard average. Run the block below in the availability database, then run it again against the same table shape in a database outside the group on the same instance. The gap between the two durations is a rough price for the remote log, and it holds up in an argument with the network team far better than a graph does. Change one variable at a time after that, or the gap stops meaning anything.

IF OBJECT_ID('dbo.CommitProbe') IS NULL
    CREATE TABLE dbo.CommitProbe (probe_id int PRIMARY KEY, touched_at datetime2(3));
IF NOT EXISTS (SELECT 1 FROM dbo.CommitProbe WHERE probe_id = 1)
    INSERT INTO dbo.CommitProbe (probe_id) VALUES (1);
SET STATISTICS TIME ON;
BEGIN TRANSACTION;
UPDATE dbo.CommitProbe SET touched_at = SYSUTCDATETIME() WHERE probe_id = 1;
COMMIT TRANSACTION;
SET STATISTICS TIME OFF;

What async commit buys, and what it costs

With asynchronous commit the primary stops waiting for the remote hardening confirmation, so the round trip leaves the commit path. Commit latency drops and the secondary is free to fall behind by an amount nobody controls. That is a real fix for the wait and a real change to the promise.

The price is a recovery-point decision. If the primary dies before its log reaches a failover target you can actually use, pushing the service onto the async copy discards transactions the application already reported as committed. No configuration dialog frames it that way, because it is a business question about acceptable loss rather than a performance setting. Write the tolerated loss window down before the incident, not during it.

Agree the mode with whoever owns the data, in writing, alongside the failover procedure for the case where the preferred target is unavailable. Synchronous in a design document is not an operational runbook, and asynchronous is not a licence to stop watching the queues.

Choose a fix you can defend afterwards

Four honest options exist. Cut avoidable commit round trips where the application's semantics allow batching; fix a transport or storage bottleneck you have measured, which may mean a faster log volume on the secondary rather than a bigger pipe; move the replica closer so the round trip shrinks; or relax the protection policy and accept a wider loss window. Each one buys latency by spending something else, and the fourth spends durability. Name the spend before you choose.

Decide with the workload's latency target and its durability requirement in front of you at the same time, because optimising one quietly spends the other. Then re-run the same transaction you measured before the change. A fix that cannot move that number is a guess wearing a change ticket.

Keep the before-and-after readings in the change record. Otherwise the next person to see slow writes repeats the investigation from the beginning and starts by blaming the tunnel again.