As a production SQL Server DBA, one of the fastest health checks I run on an Always On Availability Group is a latency check.
AG latency is the delay between a transaction committing on the primary replica and that same transaction being hardened and redone on a secondary replica.
If the secondary is behind, it is serving stale data.
In asynchronous commit mode, that also means real data loss exposure during failover.
If you just need the script:
👉 Check Always On Availability Group Latency Script
This post explains what the numbers actually mean.
What AG Latency Really Represents
Latency is not simply “seconds behind”.
It is the combined effect of:
- Log transport delay
- Redo queue backlog
- Redo throughput
- Network round-trip
- Secondary I/O and CPU capacity
The DMV most scripts rely on is:
sys.dm_hadr_database_replica_states
Key fields:
last_commit_timelast_redone_timeredo_queue_sizeredo_rate
Together they tell you:
- How far behind the secondary is in time
- How much log is waiting to be applied
- How quickly that backlog is clearing
Time alone is misleading. Queue depth without throughput is misleading. You need both.
Time Behind Primary
Most scripts calculate lag using:
DATEDIFF(SECOND, last_commit_time, GETDATE())
As a rough guide:
- 0–5 seconds → Normal in most environments
- 10–30 seconds → Watch workload patterns
- Minutes → Investigate
- Hours → You have sustained pressure
But seconds behind is not the real risk metric.
A replica can be “60 seconds behind” and still healthy if redo is stable and clearing.
It can also be “5 seconds behind” with a rapidly growing redo queue.
Always correlate time with queue depth.
Redo Queue Size
redo_queue_size represents how much log (in KB) is waiting to be applied on the secondary.
Interpretation depends on context:
Large queue + healthy redo rate
→ Catching up.
Large queue + falling redo rate
→ Secondary pressure.
Common causes:
- Slower storage on secondary
- CPU saturation
- Index rebuilds or large batch operations
- Very large transactions
- Maintenance running concurrently
Redo is single-threaded per database. Adding CPU does not parallelise redo in the way many expect.
If the secondary cannot flush log records quickly enough, the queue grows.
Redo Rate
redo_rate is usually reported in KB/sec.
This is the most underappreciated number in AG latency checks.
If redo rate drops while redo queue grows, the bottleneck is on the secondary.
Check:
- Write latency
- CPU utilisation
- Competing maintenance
- Storage tier mismatches between replicas
If redo rate is stable but queue still grows, the primary is simply generating log faster than the secondary can apply it.
That is workload pressure, not necessarily failure.
Estimated Catch-Up Time
Most scripts estimate recovery time using:
redo_queue_size / redo_rate
This is a directional estimate only.
It assumes:
- Redo rate stays constant
- Workload does not spike
- No additional log is generated
It is useful for judging failover readiness, not for making promises.
Synchronous vs Asynchronous Commit
Availability mode changes what latency actually means.
Synchronous Commit
- Primary waits for secondary to harden log
- Data loss exposure is minimised
- Commit latency includes network RTT
Important:
Hardened is not the same as redone.
Even in synchronous mode, redo queue can grow.
The transaction is safe, but not necessarily readable on the secondary yet.
Asynchronous Commit
- Primary does not wait
- Throughput is prioritised
- Replica lag is expected
Failover in async mode while lag exists can result in data loss.
Before a planned failover:
- Switch to synchronous commit
- Wait for
synchronization_state = SYNCHRONIZED - Confirm redo queue is minimal
- Confirm no large active transactions
- Then fail over
Failover should be boring. If it feels risky, you are not ready.
When Latency Becomes Operationally Risky
Latency becomes dangerous when:
- The secondary serves reporting or read workloads
- RPO requirements are strict
- Redo queue grows continuously during peak hours
- Async replicas are candidates for manual failover
AG latency is not just performance telemetry.
It is a data protection signal.
Common Root Causes in Production
In real environments, the usual culprits are predictable.
Large transactions
Bulk operations and long-running deletes inflate log generation.
Secondary I/O limitations
Redo is write-heavy and sensitive to storage latency.
CPU pressure on secondary
Redo rate drops immediately when CPU is constrained.
Network constraints (async)
High RTT or packet loss increases transport delay before redo even begins.
The fix is rarely inside the AG configuration itself. It is almost always workload or infrastructure related.
What I Check Before Failover
Before failing over an Availability Group:
- Time behind primary
- Redo queue size
- Synchronization state
- Commit mode
- Active long-running transactions
- Recent workload spikes
I want to see:
- Stable redo rate
- Minimal queue depth
- Confirmed synchronization
- No large write activity
If any of those are unstable, wait.
Monitoring Strategy
Latency should not be reactive.
At minimum, monitor:
- Sustained redo queue growth
- Significant redo rate drops
- Synchronization state transitions
- Replica role changes
Alert on sustained trends, not short spikes.
AG latency naturally fluctuates under load. That is normal.
What is not normal is sustained backlog growth.
Related Script
For the script used to calculate latency and estimated recovery time:
👉 Check Always On Availability Group Latency Script
This post explains how to interpret the output.
Final Thoughts
AG latency is not just “seconds behind”.
It represents:
- Data protection exposure
- Failover readiness
- Secondary read consistency
- Infrastructure health
Your job is not to observe the number.
Your job is to understand why it exists and whether it is acceptable for your RPO and RTO.
Leave a Reply