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.
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'
;