1

Is it possible and in what ways can the secondary replica cause contention against the primary replica when the secondary replica's resources are maxed out (e.g. does the primary "lock" up while waiting on the secondary to catch up on synchronizing)?

Does it make a difference if the Availability Mode of the secondary replica is set to Asynchronous commit vs Synchronous commit?

2

In very simple terms:

In asynchronous mode, if the replica can't keep up with its primary then the log send queue will just increase indefinitely until the load is reduced.

In perfmon: SQLServer:Database Replica Log Send Queue

Bascially the primary will keep humming along but the replicas will just end up further and further behind.

In synchronous mode, if the replica can't catch up, then you will see an increase in transaction delay on the primary. In other words the time the primary spends waiting on replicas to commit / harden a transaction.

In perfmon: SQL Server Database Replica :Transaction Delay

Unless ofcourse the replica is offline, then there is no delay.

For more detailed reading on what causes latency in synchronous mode: https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-data-movement-latency-between-synchronous-commit-always-on-availability-groups/

@scismon makes a good point. The logs will fill up in async mode if the replica(s) can't keep up.

select log_reuse_wait_desc, * from sys.databases

log_reuse_wait_desc shows up with AVAILABILITY_REPLICA

  • 1
    I'd add for async that it could get so far behind that the transaction logs on the primary could fill up since the secondary hasn't hardened them and thus they haven't been backed up clearing the primary. – scsimon Nov 26 '19 at 16:09
  • Good catch @scsimon, I have added your point to my answer. – Craig Efrein Nov 26 '19 at 16:14
0

In synchronous mode, the Primary has to wait for the Secondary to harden the transaction before the transaction can be committed on the primary. Latency, either from hardware or traffic, can cause the log files on the primary to fill which can put additional load on the CPU and disks to clear the log file(s) during a log backup.

Differences between synch and asynch

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.