For AlwaysOn AGs, can a secondary replica cause contention on the primary when the resources of the secondary are maxed out

availability-groupsblockingdata synchronizationlockingreplication

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?

Best Answer

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