Calculating Always On Latency

http://sqlblogcasts.com/blogs/blakmk/archive/2012/12/18/calculating-estimated-data-loss-with-always-on.aspx
-- Slightly modified from above


WITH DR_CTE ( replica_server_name, database_name, last_commit_time)

AS

                (

                                select ar.replica_server_name, database_name, rs.last_commit_time

                                from master.sys.dm_hadr_database_replica_states  rs

                                inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id

                                inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id

                                where replica_server_name != @@servername

                )

select ar.replica_server_name as PrimaryServerName, dcs.database_name, DR_CTE.replica_server_name SecondaryServerName, rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', datediff(ss,  DR_CTE.last_commit_time, rs.last_commit_time) 'lag_in_seconds'

from master.sys.dm_hadr_database_replica_states  rs

inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id

inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id

inner join DR_CTE on DR_CTE.database_name = dcs.database_name

where ar.replica_server_name = @@servername

order by lag_in_seconds desc