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