Assessing the Latency of Availability Group Database Synchronization

The SQL script provided below can be utilized to assess the real-time latency in seconds of SQL Server AlwaysOn synchronization.

;WITH

AG_Stats AS

(

SELECT

AR.replica_server_name,

AG.name AS AGName,

HARS.role_desc,

DB_NAME(DRS.database_id) AS DBName,

DRS.last_commit_time

FROM sys.dm_hadr_database_replica_states DRS

INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id

INNER JOIN sys.dm_hadr_availability_replica_states HARS

ON AR.group_id = HARS.group_id AND AR.replica_id = HARS.replica_id

INNER JOIN sys.availability_groups AG ON AG.group_id = AR.group_id

),

Pri_CommitTime AS

(

SELECT

replica_server_name,

AGName,

DBName,

last_commit_time

FROM AG_Stats

WHERE role_desc = 'PRIMARY'

),

Sec_CommitTime AS

(

SELECT

replica_server_name,

AGName,

DBName,

last_commit_time

FROM AG_Stats

WHERE role_desc = 'SECONDARY'

)

SELECT

p.replica_server_name AS PrimaryReplica,

p.AGName,

p.DBName AS DatabaseName,

s.replica_server_name AS SecondaryReplica,

DATEDIFF(SECOND, s.last_commit_time, p.last_commit_time) AS Sync_Latency_Secs

FROM Pri_CommitTime p

LEFT JOIN Sec_CommitTime s

ON s.DBName = p.DBName AND s.AGName = p.AGName;

Example output as shown below: