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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy