Description
The Average Log Send Rate metrics utilizes the log_send_rate column in the sys.dm_hadr_database_replica_states dynamic management view that reports the average rate at which the primary replica instance sent data during last active period, in kilobytes (KB)/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 Log Send Rate for all Secondary Replicas
Description: Average rate at which primary replica instance sent data during last active period, in kilobytes (KB)/second across all secondary replicas.
Category: Create New Category called “AG Metrics”
Units: (KB)/sec
Metric Type: Single Value
Frequency: 600
Timeout: 20
SQL Statement:
/* AVG log send rate */
SELECT AVG(drs.log_send_rate) AS avg_log_send_rate_all
FROM sys.dm_hadr_availability_replica_states ars WITH(NOLOCK)
LEFT JOIN sys.availability_replicas ar WITH(NOLOCK)
ON ar.replica_id = ars.replica_id
LEFT JOIN sys.availability_groups ag WITH(NOLOCK)
ON ag.group_id = ars.group_id
LEFT JOIN sys.dm_hadr_database_replica_states drs WITH(NOLOCK)
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.
/* AVG log send rate */
SELECT AVG(drs.log_send_rate) AS avg_log_send_rate_all
FROM sys.dm_hadr_availability_replica_states ars WITH(NOLOCK)
LEFT JOIN sys.availability_replicas ar WITH(NOLOCK)
ON ar.replica_id = ars.replica_id
LEFT JOIN sys.availability_groups ag WITH(NOLOCK)
ON ag.group_id = ars.group_id
LEFT JOIN sys.dm_hadr_database_replica_states drs WITH(NOLOCK)
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'
;