November 3, 2020 at 5:28 pm
How can I check if there is latency between primary and secondary on SQL Server 2016. Are there any scripts available?. What in specific you look for in the availability dashboard. For example, I see all the db's in green ( healthy) however found out that we still had 40 secs latency. Do you look at hardened LSN or something like that? please advise any best practices.
I am aware if could be due to network latency or amount of data or Performance issues. There could be more reasons, however I want to be able to tell if there is latency even in secs. please let me know your thoughts!
Thanks!
November 4, 2020 at 1:50 pm
this may help
https://www.sqlshack.com/measuring-availability-group-synchronization-lag/
My company uses SolarWinds DPA to monitor this and send an email alert if the sync lag is larger than 60 seconds.
February 26, 2025 at 7:06 pm
Find below/attached script to get the Always On Latency ,duration to sync Always-ON & data latency on an Always On Availability Group in ASYNC mode
--Always ON****
--Always On Latency -
SELECT getdate() as FetchTime, log_send_queue_size,log_send_rate,log_send_queue_size/log_send_rate as TimetoSend,
secondary_lag_seconds/60 as MinstoSync,redo_queue_size,
redo_rate,last_commit_time, * from
FROM sys.dm_hadr_database_replica_states
where database_id=db_id('ckoltp') and is_local=0
--duration to sync Always-ON
SELECT getdate() as FetchTime, redo_queue_size,
redo_rate,last_commit_time, *
FROM sys.dm_hadr_database_replica_states
where database_id=db_id('DB_Name') and is_local=0
--data latency on an Always On Availability Group in ASYNC mode
;WITH
AG_Stats AS
(
SELECT AR.replica_server_name,
AG.name as AGName,
HARS.role_desc,
Db_name(DRS.database_id) [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 [primary_replica]
, p.AGNAME
, p.[DBName] AS [DatabaseName]
, s.replica_server_name [secondary_replica]
, DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Latency_Secs]
FROM Pri_CommitTime p
LEFT JOIN Sec_CommitTime s ON .[DBName] = [p].[DBName] and s.AGNAME = p.AGNAME
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply