This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

AG Metrics - Average Redo Rate across all Secondary Replica Databases

Description

The Average Redo Rate across all Secondary Replicas metric utilizes the redo_rate collumn in the sys.dm_hadr_database_replica_states dynamic management view that reports the rate at which the log records are being redone on a given secondary database, in kilobytes (KB) per second.

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 Redo Rate across all Secondary Replica Databases

Description:  Average rate at which the log records are being redone across all secondary replica databases, in kilobytes (KB) per second. 

Category:  Create New Category called “AG Metrics”

Units: (KB)/sec

Metric Type:  Single Value

Frequency:  600

Timeout:  20

SQL Statement:

SELECTCOALESCE(AVG(drs.redo_rate),0) ASavg_redo_rate
  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 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 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.
If you needed to get even more specific, you can combine the above filters to get the data you want.You should be able to vary the filter to get as broad or as specific as needed.

SELECTCOALESCE(AVG(drs.redo_rate),0) ASavg_redo_rate  FROMsys.dm_hadr_availability_replica_states ars
  LEFTJOINsys.availability_replicas ar
         ONar.replica_id = ars.replica_id
  LEFTJOINsys.availability_groups ag
         ONag.group_id = ars.group_id
  LEFTJOINsys.dm_hadr_database_replica_states drs
         ONdrs.replica_id = ars.replica_id
WHEREars.role_desc = 'SECONDARY'
   /* distributed AG filter */
   /* 0 = standard AG */
   /* 1 = distributed AG */
   ANDag.is_distributed = 0
   /* replica filter */
   ANDar.replica_server_name = 'DPA-WSFC-01'
   /* ag filter */
   ANDag.name= 'LocalAG1'
   /* databasefilter */
   ANDDB_NAME(drs.database_id) = 'Xtry'
;