SELECT 

ag.name AS ag_name

, ar.replica_server_name

, db_name(dr_state.database_id)

, is_ag_replica_local = CASE

WHEN ar_state.is_local = 1 THEN N'LOCAL'

ELSE 'REMOTE'

END 

, ag_replica_role = CASE

WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'

ELSE ar_state.role_desc

END

, dr_state.last_redone_time

, dr_state.last_hardened_lsn

, dr_state.last_hardened_time

, datediff(ms,last_hardened_time, getdate()) as 'seconds behind primary'

FROM (

( sys.availability_groups AS ag 

JOIN sys.availability_replicas AS ar 

ON ag.group_id = ar.group_id 

)

JOIN 

sys.dm_hadr_availability_replica_states AS ar_state 

ON ar.replica_id = ar_state.replica_id

)

JOIN 

sys.dm_hadr_database_replica_states dr_state 

on 

ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id

order by last_hardened_lsn desc,last_hardened_time

last_hardened_time:这个时间点表示AlwaysOn主节点的事务日志传到从节点的时间。

last_redone_time:这个时间点表示 AlwaysOn主节点的事务日志传到从节点后完成redo的时间（完成落盘），完成redo表示从节点已经完全同步了主节点的事务日志。