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:
