I have implemented Always on Availability Group in asynchronous mode between two servers primary and secondary.

When i tried to check the data latency between the servers in performance monitor using performance counters like MS SQL :availability replica,MS SQL:database replica and MS SQL:databases ,I'm not able to see any results in the graph.

But the graph is showing results for other performance counters.I've tried to get the data latency using DMV sys.dm_hadr_database_replica_states.

Can any one tell me what is the issue ?

How can I check the data latency between the two servers in performance monitor using graphs as shown in the msdn article ?

4 Answers 4


I used this script in a custom report once.

;WITH AG_Stats AS (
            SELECT AGS.name                       AS AGGroupName, 
                   AR.replica_server_name         AS InstanceName, 
                   Db_name(DRS.database_id)       AS DBName, 
                   AR.availability_mode_desc      AS SyncMode, 
                   DRS.synchronization_state_desc AS SyncState, 
                   DRS.last_hardened_time, -- On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn).
                   DRS.last_redone_time, -- Time when the last log record was redone on the secondary database.
                    --Time corresponding to the last commit record.
                    --On the secondary database, this time is the same as on the primary database.
                    --On the primary replica, each secondary database row displays the time that the secondary replica that hosts that secondary database 
                    --   has reported back to the primary replica. The difference in time between the primary-database row and a given secondary-database 
                    --   row represents approximately the recovery time objective (RPO), assuming that the redo process is caught up and that the progress 
                    --   has been reported back to the primary replica by the secondary replica.
            FROM   sys.dm_hadr_database_replica_states DRS 
            LEFT JOIN sys.availability_replicas AR 
            ON DRS.replica_id = AR.replica_id 
            LEFT JOIN sys.availability_groups AGS 
            ON AR.group_id = AGS.group_id 
            LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id 
            AND AR.replica_id = HARS.replica_id 
    Pri_CommitTime AS 
            SELECT  DBName
                    , last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'PRIMARY'
    Rpt_CommitTime AS 
            SELECT  DBName, last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'SECONDARY' AND [InstanceName] = 'InstanceNameB-PrimaryDataCenter'
    FO_CommitTime AS 
            SELECT  DBName, last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'SECONDARY' AND ([InstanceName] = 'InstanceNameC-SecondaryDataCenter' OR [InstanceName] = 'InstanceNameD-SecondaryDataCenter')
SELECT p.[DBName] AS [DatabaseName], p.last_commit_time AS [Primary_Last_Commit_Time]
    , r.last_commit_time AS [Reporting_Last_Commit_Time]
    , DATEDIFF(ss,r.last_commit_time,p.last_commit_time) AS [Reporting_Sync_Lag_(secs)]
    , f.last_commit_time AS [FailOver_Last_Commit_Time]
    , DATEDIFF(ss,f.last_commit_time,p.last_commit_time) AS [FailOver_Sync_Lag_(secs)]
FROM Pri_CommitTime p
LEFT JOIN Rpt_CommitTime r ON [r].[DBName] = [p].[DBName]
LEFT JOIN FO_CommitTime f ON [f].[DBName] = [p].[DBName]
  • Saved my day! Working on adding some more data and make it even more handy for monitoring AlwaysOn AG. Awesome!
    – Yaroslav
    Commented Mar 31, 2015 at 12:33
  • Great script, thanks! Just had to edit the "InstanceName"s out in the subquery :) Commented Feb 2, 2021 at 15:32

The first graph of that link doesn't show the data latency between two servers. Instead it shows :

  • SQL Server:Database Replica --> Transaction Delay (GREEN COLOR) /
  • SQL Server:Database Replica --> Mirrored Write Transactions/sec (RED COLOR)

And then it says that if you want to get the data latency between two servers, you have to divide both values, but that result is not represented in the graph.

If you use DMV to get these values it wont be exactly as using PERFMON because DMV only shows an accumulative value since the last server start.

I recommend you to get the values of the two counters mentioned before with perfmon and put them in an Excel sheet, use a formula to get the data latency between the two servers and then make a graph.

Hope it's clear. Good luck


I know I'm late to the party but I'll try to address this. I think you may be misunderstanding either the difference between the synch and asynch methods or what those perfmon metrics deal with. As an asynch replica does not reply to the primary as to the state of the transaction (whether it's committed or not), the perfmon metrics are not applicable. The same blog post series you refer to deals with assessing asynch performance. But in this case it is about seeing whether there are queues in send or receiving logs and how much time has elapsed between the secondary receiving and hardening the logs.


For an async replica, perhaps you could (a) create a table with a single row that has a timestamp column that is updated by an job scheduled to run every 10 secs (I don't think agent jobs can be scheduled to run with shorter frequency). This record will then be replicated over to the async replica. Then perform a query against both tables (primary replica and async secondary replica) and display the time difference.

Not an ideal solution I know but one that might just work for you.

Cheers, Mark

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.