DBA Scripts: Check Always On Availability Group Latency

---

How Far Behind Is Each Database, Really

Availability Group Replica State answers whether a replica is connected and generally healthy. It doesn’t answer the database-level question that actually matters during an incident: how much data is sitting in the send queue right now, how fast is it draining, and how far behind is the redo on the secondary. Two databases in the same healthy AG can have wildly different latency profiles depending on their own workload.

This script reads log send queue size, log send rate, redo queue size, and redo rate per database per replica directly from the AG’s database-level health DMV, the numbers that tell you whether “healthy” also means “caught up.”

---

Why Availability Group Latency Matters

• log_send_queue_size growing faster than log_send_rate can drain it means the secondary is falling further behind over time, not just momentarily busy
• redo_queue_size matters specifically for readable secondaries and failover time, a large redo queue means a longer wait before a failed-over secondary is actually usable
• A synchronous-commit replica with growing queues can start stalling transactions on the primary before its connection state ever shows unhealthy
• Latency here is a leading indicator, by the time synchronization health flips to unhealthy, the queue numbers have usually already been telling the story for a while

---

When to Run This Script

• Routine SQL Server health checks on any AG-protected instance
• When application teams report slow commits on a synchronous-commit AG primary
• Before and after a planned failover, to confirm the target replica is genuinely caught up first
• After a large batch load or index maintenance operation, to see how much log traffic it generated for replicas to absorb

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-AvailabilityGroupLatency
Category    : high-availability
Purpose     : Display AG replica synchronization timing, queue health, and replication rates.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-check-always-on-availability-group-latency/)
Requires    : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-check-always-on-availability-group-latency/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

IF SERVERPROPERTY('IsHadrEnabled') = 0
    OR NOT EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
    SELECT 'Always On Availability Groups is not enabled or no groups are configured on this instance.' AS status;
END
ELSE
BEGIN

SELECT
    ag.name                          AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    DB_NAME(drs.database_id)         AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.last_hardened_time,
    drs.last_redone_time,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate
FROM sys.dm_hadr_database_replica_states    AS drs
INNER JOIN sys.availability_replicas        AS ar  ON ar.replica_id  = drs.replica_id
INNER JOIN sys.availability_groups          AS ag  ON ag.group_id    = ar.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id = ar.replica_id
ORDER BY ag.name, database_name, ar.replica_server_name;

END

Unlike replica-level state, this runs per database per replica, so a single AG with several databases returns one row per database on every replica, exactly the granularity needed to spot one lagging database rather than assuming the whole AG is uniformly healthy or unhealthy.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check per-database AG sync queue size and rates:
.\run.ps1 Get-AvailabilityGroupLatency

# To run against a remote sql server:
.\run.ps1 Get-AvailabilityGroupLatency -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/high-availability/always-on/Get-AvailabilityGroupLatency.sql
• powershell/wrappers/high-availability/always-on/Get-AvailabilityGroupLatency.ps1

---

Example Output

This lab instance doesn’t have Always On enabled at all, so the script correctly returns its guard-clause status message rather than an error or an empty result:

status
Always On Availability Groups is not enabled or no groups are configured on this instance.

On an instance with a real Availability Group and some replica lag, a row looks like this:

ag_name	replica_server_name	role_desc	database_name	log_send_queue_size	log_send_rate	redo_queue_size	redo_rate
SalesAG	SQL02	SECONDARY	SalesDB	128	512	4096	2048

---

Understanding the Results

• log_send_queue_size — KB of log not yet sent to this replica. Should stay small and stable; a value that keeps climbing across repeated checks means the primary is generating log faster than the network/replica can absorb it.
• log_send_rate — KB/sec currently being sent. Compare against queue size to estimate how long the backlog will take to clear, if it’s clearing at all.
• redo_queue_size — KB of log received but not yet applied (redone) on the secondary. This is what determines how current a readable secondary’s data actually is, and how long failover takes before the database is usable.
• redo_rate — KB/sec currently being redone. A redo_queue_size that’s large but redo_rate is healthy means it’s actively catching up; large and flat means something is blocking redo.
• synchronization_state_desc = SYNCHRONIZED / SYNCHRONIZING — SYNCHRONIZED (synchronous mode) means fully caught up. SYNCHRONIZING is expected for asynchronous replicas and simply means log is still in flight, not necessarily a problem on its own.

---

Best Practices

• Watch trend, not just a single snapshot, a queue size that’s stable is very different from one that’s climbing every time you check
• For synchronous-commit AGs, treat any sustained non-zero log_send_queue_size as worth investigating, that mode exists specifically to keep this near zero
• Check redo_queue_size specifically before relying on a readable secondary for reporting, or before a planned failover to that replica
• Pair with Availability Group Replica State, connection health and queue latency are two different failure modes that can occur independently

---

Related Scripts

You may also find these scripts useful:

• High Availability (hub)
• Availability Group Replica State
• Log Reuse Waits
• Replication Status
• Transaction Log Size and Usage
• AG Failover Readiness and Readable Secondary Usage

---

Frequently Asked Questions

Summary

Replica connection state answers whether an Availability Group is broadly healthy; this script answers the sharper question of how far behind each database actually is, in KB and in time, not just a healthy/unhealthy label. Queue size and rate together tell you whether a replica is keeping pace or quietly falling further behind.

Run this alongside Availability Group Replica State as routine health checks, and specifically before trusting a secondary for reporting or a planned failover.