Share via

Monitoring AlwaysOn Latency

  • Article

I have written following piece of code to monitor AlwaysOn Group latency. This will send an email alert if there is no data replicated in last 10 minutes. this will also provide information on amount of data to be replicated. This can be scheduled in a job to run in a specific interval. 

declare @servername sysname
declare @tsql nvarchar(max) 
declare @sub nvarchar(255)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

set @servername = (Select @@SERVERNAME)
set @sub = @servername + ': AG Latency Issue has Occured'
set @tsql = ' SELECT AGS.NAME AS AGGroupName
    ,AR.replica_server_name AS InstanceName
    ,HARS.role_desc
    ,DRS.synchronization_state_desc AS SyncState
    ,DRS.last_hardened_time
    ,DRS.last_redone_time
    ,((DRS.log_send_queue_size)/8)/1024 QueueSize_MB
,datediff(MINUTE, last_redone_time, last_hardened_time) as Latency_Minutes
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
    AND AR.replica_id = HARS.replica_id
Where HARS.role_desc = ''SECONDARY'''

if exists(SELECT AGS.NAME AS AGGroupName
    ,AR.replica_server_name AS InstanceName
    ,HARS.role_desc
    ,DRS.synchronization_state_desc AS SyncState
    ,DRS.last_hardened_time
    ,DRS.last_redone_time
    ,((DRS.log_send_queue_size)/8)/1024 QueueSize_MB
,datediff(MINUTE, last_redone_time, last_hardened_time) as Latency_Minutes
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
    AND AR.replica_id = HARS.replica_id
Where HARS.role_desc = 'SECONDARY' and datediff(MINUTE, last_redone_time, last_hardened_time) > 10)
begin

SET @xml =   CAST(( 
SELECT AGS.NAME AS 'td',''
    ,AR.replica_server_name AS 'td',''
    ,HARS.role_desc AS 'td',''
    ,DRS.synchronization_state_desc AS 'td',''
    ,DRS.last_hardened_time AS 'td',''
    ,DRS.last_redone_time AS 'td',''
    ,((DRS.log_send_queue_size)/8)/1024 AS 'td',''
,datediff(MINUTE, last_redone_time, last_hardened_time)  AS 'td'
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
    AND AR.replica_id = HARS.replica_id
Where HARS.role_desc = 'SECONDARY'
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>AG Latency Information</H3>
<table border = 1> 
<tr>
<th> AGGroupName </th> <th> InstanceName </th> <th> Role_Desc </th> <th> SyncState </th> <th> Last_hardened_time </th> <th> Last_redone_time </th> <th> QueueSize_MB </th> <th> Latency_Minutes </th></tr>'    

 
SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'To List',
@body = @body,
@subject = @sub,
@body_format ='HTML'

end

else 
begin
Print 'AG Latency is Good.'
end