Version 1

    Description

    The Average Redo Queue Size across all Secondary Replicas metric utilizes the redo_queue_size column in the sys.dm_hadr_database_replica_states dynamic management view that reports the amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB).

     

    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 amount of log records in the log files across all secondary replicas that have not yet been redone, in kilobytes (KB) 

    Category:  Create New Category called “AG Metrics”

    Units: (KB)/sec

    Metric Type:  Single Value

    Frequency:  600

    Timeout:  20

     

    SQL Statement:

     

    SELECT COALESCE(AVG(drs.redo_queue_size),0) AS avg_redo_queue_size
      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 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.

     

    SELECT COALESCE(AVG(drs.redo_queue_size),0) AS avg_redo_queue_size
      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'
       /* 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'
    ;