AG Metric - Average Redo Queue Size across all Secondary Replicas

ashrecksiedler

Description

The Average Redo Queue Size across all Secondary Replicas metric utilizes the redo_queue_size column in the sys.dm_hadr_database_replica_states dynamic management view that reports the amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB).

Metric Definition

To create the custom metric, click on Options -> Custom Resource Metrics and configure the metric similar to this:

Database Versions: 11.0.0 and above

Display Name:  Average Log Send Rate for all Secondary Replicas

Description: Average amount of log records in the log files across all secondary replicas that have not yet been redone, in kilobytes (KB) 

Category:  Create New Category called “AG Metrics”

Units: (KB)/sec

Metric Type:  Single Value

Frequency:  600

Timeout:  20

SQL Statement:

SELECT COALESCE(AVG(drs.redo_queue_size),0) AS avg_redo_queue_size
  FROM sys.dm_hadr_availability_replica_states ars
  LEFT JOIN sys.availability_replicas ar
         ON ar.replica_id = ars.replica_id
  LEFT JOIN sys.availability_groups ag
         ON ag.group_id = ars.group_id
  LEFT JOIN sys.dm_hadr_database_replica_states drs
         ON drs.replica_id = ars.replica_id
 WHERE ars.role_desc = 'SECONDARY'
;

Advanced Version

The above metric is a high level measure of performance.  However, if you want to monitor a specific AG, replica or database, here is an advanced version of the query that you can modify to get the exact data you want by altering the filters after “ars.role.desc = ‘SECONDARY’”

• If you want data on just a single Availability Group across all replicas and databases, add a filter like this:
  • AND ag.name = 'AGName'
• If you want data on just a single replica across all Availability Groups and databases, add a filter like this:
  • AND ar.replica_server_name = 'ServerName'
• If you want data on just a single database across all Availability Groups and replicas, add a filter like this:
  • AND DB_NAME(drs.database_id) = 'DatabaseName'

If you needed to get even more specific, you can combine the above filters to get the data you want.

• If you want data on just one Availability Group on one replica, add a filter like this:
  • AND ag.name = 'AGName' AND ar.replica_server_name = 'ServerName'
• If you want data on a specific database in an Availability Group on a replica, add a filter like this:
  • AND ag.name = 'AGName' AND ar.replica_server_name = 'ServerName' AND DB_NAME(drs.database_id) = 'DatabaseName'
• And so on.

You should be able to vary the filter to get as broad or as specific as needed.

SELECT COALESCE(AVG(drs.redo_queue_size),0) AS avg_redo_queue_size
  FROM sys.dm_hadr_availability_replica_states ars
  LEFT JOIN sys.availability_replicas ar
         ON ar.replica_id = ars.replica_id
  LEFT JOIN sys.availability_groups ag
         ON ag.group_id = ars.group_id
  LEFT JOIN sys.dm_hadr_database_replica_states drs
         ON drs.replica_id = ars.replica_id
 WHERE ars.role_desc = 'SECONDARY'
   /* distributed AG filter */
   /* 0 = standard AG */
   /* 1 = distributed AG */
   AND ag.is_distributed = 0
   /* replica filter */
   AND ar.replica_server_name = 'DPA-WSFC-01'
   /* ag filter */
   AND ag.name = 'LocalAG1'
   /* database filter */
   AND DB_NAME(drs.database_id) = 'Xtry'
;