SELECT ar.replica_server_name AS [副本名称],
ar.availability_mode_desc as [同步模式],
DB_NAME(dbr.database_id) AS [数据库名称],
dbr.database_state_desc AS [数据库状态],
dbr.synchronization_state_desc AS [同步状态],
dbr.synchronization_health_desc AS [同步健康状态],
ISNULL(CASE dbr.redo_rate
WHEN 0 THEN
-1
ELSE
CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate
END,
-1) AS [Redo延迟(秒)], ISNULL(CASE dbr.log_send_rate
WHEN 0 THEN
-1
ELSE
CAST(dbr.log_send_queue_size AS
FLOAT) /
dbr.log_send_rate
END, -1) AS [Log传送延迟(秒)], dbr.redo_queue_size AS [Redo等待队列(KB)], dbr.redo_rate AS [Redo速率(KB/S)], dbr.log_send_queue_size AS [Log传送等待队列(KB)],
dbr.log_send_rate AS [Log传送速率(KB\S)]
FROM master .sys.availability_replicas AS AR
INNER JOIN master .sys.dm_hadr_database_replica_states AS dbr ON ar.replica_id =
dbr.replica_id
WHERE dbr.redo_queue_size IS NOT NULL
1.
ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate END, -1) AS [redo延迟(s)]
redo_rate:在给定的辅助数据库上重做日志记录的平均速率(kb (KB) 。
redo_queue_size:辅助副本的日志文件中尚未重做的日志记录量 (KB)。
翻译:备库redo导入至最新(追平)所需时间
2.
ISNULL(CASE dbr.log_send_rate WHEN 0 THEN -1 ELSE CAST(dbr.log_send_queue_size AS FLOAT) / dbr.log_send_rate END, -1) AS [log传送延迟(s)]
log_send_rate:主副本实例在上一个活动期间(kb (KB) 。)发送数据的平均速率(kb)
log_send_queue_size:主数据库中尚未发送到辅助数据库的日志记录量 (KB)。
翻译:redo传送至备库至最新(追平)所需时间
3.
dbr.redo_queue_size AS [redo等待队列(kb)]
辅助副本的日志文件中尚未重做的日志记录量 (KB)。
4.
dbr.redo_rate AS [redo速率(kb/s)]
在给定的辅助数据库上重做日志记录的平均速率(kb (KB) 。
5.
dbr.log_send_queue_size AS [log传送等待队列(kb)]
主数据库中尚未发送到辅助数据库的日志记录量 (KB)。
6.
dbr.log_send_rate AS [log传送速率(kb\s)]
主副本实例在上一个活动期间(kb (KB) 。)发送数据的平均速率(kb)
7.
dbr.last_sent_time as [发送最后日志块时间]