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
Here are some things to consider looking at if you’re experiencing AAG Latency or any other issues:
> Verify there’s not a long running transaction or big insert happening at the Primary. You can check out my other post which will help quickly review the current performance of your SQL Server.
> Review the official Microsoft Docs for Troubleshooting SQL Server Always On Issues.
> Consider contacting Microsoft Support. They’ll help you however it may come at a charge if it’s not a bug or feature issue. You’ll likely have a Microsoft Support Contract in-place if working for medium/larger company.
Leave a Reply