SQL Alwayson 常用查询
查询sys.dm_hadr_database_replica_states
sys.dm_hadr_database_replica_states DMV 为每个可用性组数据库报告一行。 报表中的一列是 redo_queue_size。 此值是以 KB 为单位的恢复队列大小。 可以设置类似于以下查询的查询，以每隔 30 秒监视恢复队列大小中的任何趋势。 查询在主要副本上运行。 它使用 is_local=0 谓词报告次要副本的数据，其中 redo_queue_size 和 redo_rate 相关
WHILE 1=1
BEGIN
SELECT drcs.database_name, ars.role_desc, drs.redo_queue_size, drs.redo_rate,
ars.recovery_health_desc, ars.connected_state_desc, ars.operational_state_desc, ars.synchronization_health_desc, *
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ars.replica_id=drcs.replica_id
JOIN sys.dm_hadr_database_replica_states drs ON drcs.group_database_id=drs.group_database_id
WHERE ars.role_desc='SECONDARY' AND drs.is_local=0
waitfor delay '00:00:30'
END
如何诊断恢复（重做）排队
确定特定辅助副本可用性组数据库的恢复队列后，连接到辅助副本，然后查询 sys.dm_exec_requests 以确定 wait_type 恢复线程和 wait_time 恢复线程。 下面是可在循环中运行的查询。 你正在寻找一个或多个等待类型的高频率，甚至等待这些等待类型。 下面是每秒运行一次的示例查询，并报告可用性组“agdb”的等待类型和等待时间：
WHILE (1=1)
BEGIN
SELECT db_name(database_id) AS dbname, command, session_id, database_id, wait_type, wait_time,
os.runnable_tasks_count, os.pending_disk_io_count FROM sys.dm_exec_requests der JOIN sys.dm_os_schedulers os
ON der.scheduler_id=os.scheduler_id
WHERE command IN('PARALLEL REDO HELP TASK', 'PARALLEL REDO TASK', 'DB STARTUP')
AND database_id= db_id('agdb')
waitfor delay '00:00:05.000'
END
查看是否已启用alwayson属性
Select serverproperty ('IsHadrEnabled')
查看主从lag延迟
--主库查：
select last_commit_time as 'primary_last_commit_tm' from sys.dm_hadr_database_replica_states where is_local=1;
select last_commit_time as 'standby_last_commit_tm' from sys.dm_hadr_database_replica_states where is_local=0;
-- 查看从库信息
select * from sys.dm_hadr_database_replica_states;
-- 找到 replica_id
select * from sys.availability_replicas where replica_id=xxx;
查有多少日志没有经过网络传过去
SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, dr_state.database_id as database_id,dr_state.log_send_queue_size, 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
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;
查已经传过去的日志，还有多少没有应用
SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, dr_state.database_id as database_id,dr_state.redo_queue_size, 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
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;