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