Checking the data latency on an Always On Availability Group in ASYNC mode

Question

We have a number of SQL Servers with Always on Availability Groups in asynchronous mode between a primary and secondary server with Manual fail over.

I created a latency report utilizing the below query that collects the data every min on each server.

On one of our servers the secondary routinely shows that it has an earlier last_commit_time then the primary.

I verified the server times are the same on both servers.

Why might this be?

;WITH 
        AG_Stats AS 
                (
                SELECT  AR.replica_server_name,
                        AG.name as AGName,
                        HARS.role_desc, 
                        Db_name(DRS.database_id) [DBName], 
                        DRS.last_commit_time
                FROM   sys.dm_hadr_database_replica_states DRS 
                INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id 
                INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id 
                    AND AR.replica_id = HARS.replica_id 
                INNER JOIN [sys].[availability_groups] AG on AG.group_id = AR.group_id
                ),
        Pri_CommitTime AS 
                (
                SELECT  replica_server_name
                        , AGNAME
                        , DBName
                        , last_commit_time
                FROM    AG_Stats
                WHERE   role_desc = 'PRIMARY'
                ),
        Sec_CommitTime AS 
                (
                SELECT  replica_server_name
                        , AGNAME
                        , DBName
                        , last_commit_time
                FROM    AG_Stats
                WHERE   role_desc = 'SECONDARY'
                )
    SELECT p.replica_server_name [primary_replica]
        , p.AGNAME
        , p.[DBName] AS [DatabaseName]
        , s.replica_server_name [secondary_replica]
        , DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Latency_Secs]
    FROM Pri_CommitTime p
    LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName] and  s.AGNAME = p.AGNAME

Answer 1

It is worth noting that the value for last_commit_time is defined differently based on whether it is being queried from the secondary or primary replica.

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 point 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.

In addition to the above, a secondary replica that has a delay of a few seconds on the commit will yield a positive number, but one that hasn't finished the commit yet will yield a negative number in your query.

So if you have a latency of 10 seconds but query at the 5 second mark, since there is no commit time yet on the secondary for the primary's last commit, you're going to get the previous commit time which is probably before the primary's latest commit time. This is expected with asynchronous replication.

Answer 2

Modified the query I'm using based on LowlyDBA's answer.

    ;WITH 
        AG_Stats AS 
                (
                SELECT  AR.replica_server_name,
                        AG.name as AGName,
                        HARS.role_desc, 
                        Db_name(DRS.database_id) [DBName], 
                        DRS.last_commit_time
                FROM   sys.dm_hadr_database_replica_states DRS 
                INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id 
                INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id 
                    AND AR.replica_id = HARS.replica_id 
                INNER JOIN [sys].[availability_groups] AG on AG.group_id = AR.group_id

                ),
        Pri_CommitTime AS 
                (
                SELECT  replica_server_name
                        , AGNAME
                        , DBName
                        , last_commit_time
                FROM    AG_Stats
                WHERE   role_desc = 'PRIMARY'
                ),
        Sec_CommitTime AS 
                (
                SELECT  replica_server_name
                        , AGNAME
                        , DBName
                        , last_commit_time
                FROM    AG_Stats
                WHERE   role_desc = 'SECONDARY'
                )
    SELECT p.replica_server_name [primary_replica]
        , p.AGNAME
        , p.[DBName] AS [DatabaseName]
        , s.replica_server_name [secondary_replica]
        , CASE 
                WHEN s.last_commit_time >= p.last_commit_time THEN DATEDIFF(ss,s.last_commit_time,p.last_commit_time) 
                ELSE DATEDIFF(ss,p.last_commit_time,GETDATE()) 
          END AS [Sync_Latency_Secs]
    FROM Pri_CommitTime p
    LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName] and  s.AGNAME = p.AGNAME