AlwaysOn Availability Groups – Query to Find Latency Part 1

AlwaysOn Availability Groups latency can be a real concern. Microsoft provides a AlwaysON Dashboard to monitor and some administration of Availability Groups in SQL Server Management Studio(SSMS). Ben Snaidero as an excellent posting about the dashboard and T-SQL monitoring of AG.

The query below uses sys.dm_hadr_database_replica_states to query the current state. It can be run on the primary or secondary server. We use a readable secondary and it is nice to see how close to real-time updates. As a roll-your-own solution, you could create a SQL Agent job to monitor and send an email when certain criteria is met.

/* 
AlwaysON AG Monitor
jackdonnell.com
12/29/2015
*/
SET NOCOUNT ON
GO
USE master
GO

SELECT CAST(DB_NAME(database_id)as VARCHAR(40)) database_name,
Convert(VARCHAR(20),last_commit_time,22) last_commit_time
,CAST(CAST(((DATEDIFF(s,last_commit_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,last_commit_time,GetDate())%3600)/60 as varchar) + ' min, '
+ CAST((DATEDIFF(s,last_commit_time,GetDate())%60) as varchar) + ' sec' as VARCHAR(30)) time_behind_primary
,redo_queue_size
,redo_rate
,CONVERT(VARCHAR(20),DATEADD(mi,(redo_queue_size/redo_rate/60.0),GETDATE()),22) estimated_completion_time
,CAST((redo_queue_size/redo_rate/60.0) as decimal(10,2)) [estimated_recovery_time_minutes]
,(redo_queue_size/redo_rate) [estimated_recovery_time_seconds]
,CONVERT(VARCHAR(20),GETDATE(),22) [current_time]
FROM sys.dm_hadr_database_replica_states
WHERE last_redone_time is not null;

GO
Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Tiny URL for this post:
 

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress Anti Spam by WP-SpamShield