In this post, I’m sharing a script that I use frequently as a Production SQL DBA to check Always On Availability Group (AAG) latency.
Always On Availability Groups (AAG) are a high-availability and disaster recovery solution in SQL Server, allowing you to synchronize databases across multiple replicas. These replicas can serve as failover targets or provide read access to offload queries from the primary. Latency in an AAG refers to the delay between when a transaction is committed on the primary replica and when it is applied on the secondary replicas.
Having proper monitoring and alerting in place for AAG latency is critical because high latency means secondary replicas may lag behind, potentially leading to outdated or incomplete data being read by applications or users. The goal is always to keep latency as low as possible—ideally no latency or just a few seconds.
Get AAG Latency Using SQL Server Script
Why This Script Is Useful
This script provides insight into the health of your Availability Group(s). Specifically, it shows:
- Time Behind Primary: How far behind the secondary replica is from the primary.
- Redo Queue Size & Redo Rate: Metrics that help estimate how long it will take for the secondary to catch up.
- Estimated Completion Time: A projection of when synchronization will finish based on current redo rates.
High latency can mean that secondary reads are outdated, which is a problem for systems relying on accurate, real-time data.
Async vs. Sync and AAG Latency
It’s worth noting that latency is influenced by the availability mode of your AAG:
Synchronous Commit: Ensures data is committed to secondary replicas before the transaction is confirmed on the primary. This minimizes latency but may introduce slight delays in write operations due to network overhead.
Asynchronous Commit: Prioritizes primary performance by allowing transactions to commit without waiting for secondary synchronization. While this reduces write delays, it can lead to higher latency on secondary replicas, making it unsuitable for scenarios requiring real-time reads.
When failing over an AAG configured for Asynchronous Commit, it’s crucial to switch to Synchronous Commit beforehand to avoid potential data loss. Asynchronous mode doesn’t guarantee that all transactions have been applied to the secondary replica, which could result in missing data after the failover.
Resolving AAG Latency Issues
For detailed guidance on troubleshooting AAG latency, refer to the official Microsoft Documentation: Troubleshoot SQL Server Always On issues
I say this, but it’s not the best article to recommend imo. I want to add to this, which I’ll do in a future post. I’ll loop back and update here with a link to it when done. For now, I hope all this info has been useful!
Leave a Reply