通过

排查Always On可用性组中的恢复队列问题

  • 项目

本文提供与恢复队列相关的问题的解决方法。

什么是恢复队列？

对可用性组数据库中的主副本 (replica) 所做的更改将发送到同一可用性组中定义的所有辅助副本。 这些更改到达次要副本后，首先会将这些更改写入可用性组数据库的事务日志文件中。 然后，Microsoft SQL Server使用恢复或重做操作来更新数据库文件。

如果对可用性组的更改到达并强化数据库事务日志文件的速度超过恢复速度，则会形成 恢复队列 。 此队列由未恢复和还原到数据库的强化事务日志事务组成。

恢复 (恢复) 排队的症状和影响

查询主要副本和次要副本会返回不同的结果

查询次要副本的只读工作负荷可能会查询过时的数据。 如果发生恢复队列，则查询相同数据时，主副本 (replica) 数据库上数据的更改可能不会反映在辅助数据库中。

尽管更改到达辅助数据库并写入数据库日志文件，但在恢复更改并将其还原到数据库文件之前，不会查询这些更改。 恢复操作使这些更改可读。

有关详细信息，请参阅“Always On可用性组的可用性模式之间的差异”中的辅助副本 (replica) 上的数据延迟部分。

故障转移时间较长或超出 RTO

恢复时间目标 (RTO) 是组织可以处理的最大数据库停机时间。 RTO 还介绍了组织在中断后可以多快地重新获得对数据库的访问权限。 如果发生故障转移时辅助副本 (replica) 存在大量恢复队列，则恢复可能需要更长的时间。 恢复后，数据库将转换为主要角色，并表示故障转移前存在的数据库的状态。 恢复时间较长可能会延迟故障转移后生产恢复的速度。

各种诊断功能报告可用性组恢复队列

在恢复队列中，SQL Server Management Studio (SSMS) 中的Always On 仪表板可能会报告可用性组运行不正常。

如何检查恢复 (重做) 排队

恢复队列是一种按数据库的度量值，可以使用主副本 (replica) 上的Always On 仪表板或使用主要或辅助副本 (replica) 上的sys.dm_hadr_database_replica_states动态管理视图 (DMV) 进行检查。 检查恢复队列和恢复速率性能监视器计数器。 必须对照辅助副本 (replica) 检查这些计数器。

接下来的几个部分提供了主动监视可用性组数据库恢复队列的方法。

查询sys.dm_hadr_database_replica_states

sys.dm_hadr_database_replica_states DMV 为每个可用性组数据库报告一行。 报表中的一列是 redo_queue_size。 此值是恢复队列大小（以 KB 为单位）。 可以设置类似于以下查询的查询，以每隔 30 秒监视恢复队列大小的任何趋势。 查询在主副本 (replica) 上运行。 它使用is_local=0谓词来报告辅助副本 (replica) 的数据，其中 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

输出如下所示。

查询输出的屏幕截图，其中报告了与redo_queue_size和redo_rate相关的辅助副本 (replica) 的数据。

在Always On仪表板中查看恢复队列

若要查看恢复队列，请执行以下步骤：

  1. 在 SSMS 对象资源管理器中右键单击可用性组，打开 SSMS 中的Always On仪表板。

  2. 选择“ 显示仪表板”。

    可用性组数据库最后列出，并且数据库报告了一些数据。 尽管默认情况下未列出 重做队列大小 (KB) 和 恢复速率 (KB/秒) ，但你可以将它们添加到此视图中，如下一步的屏幕截图所示。

  3. 若要添加这些计数器，请右键单击数据库报表上方的标头，然后从可用列列表中选择。

  4. 若要将 重做队列大小 (KB) 和 重做速率 (KB/秒添加到) ，请右键单击以下屏幕截图中以红色突出显示的标头。

    显示添加计数器“重做队列大小 (KB) ”和“恢复速率 (KB/秒”) 的屏幕截图。

    默认情况下，Always On 仪表板自动刷新重做队列大小 (KB) 和恢复速率 (KB/秒) 每 60 秒。

    显示刷新计数器设置为每 60 秒的屏幕截图。

查看 性能监视器 中的恢复队列

恢复队列大小对于每个辅助副本 (replica) 和数据库都是唯一的。 因此，若要查看可用性组数据库的恢复队列，请执行以下步骤：

  1. 在辅助副本 (replica) 上打开性能监视器。

  2. 选择“ 添加 (计数器) 按钮。

  3. 在“ 可用计数器”下，选择“ SQLServer：数据库副本”，然后选择“ 恢复队列 ”和“ 重做字节数/秒 ”计数器。

  4. 在 “实例 ”列表框中，选择要监视恢复队列的可用性组数据库。

  5. 选择 “添加>确定”。

    以下是增加恢复队列的情况。

    显示恢复队列增加的屏幕截图。

解释恢复队列值

本部分介绍如何解释与上一部分确定的恢复队列相关的值。

何时恢复排队是个问题？ 应允许多少恢复队列？

你可能会认为，如果恢复队列报告的值 为 0，这意味着该报告时不会发生恢复队列。 但是，当生产环境繁忙时，应观察到恢复队列经常报告非零值，即使在正常的 AlwaysOn 环境中也是如此。 在典型生产期间，应观察到此值在 0 和非零值之间波动。

如果发现恢复队列随着时间推移而增加，需要进一步调查。 此额外活动表示某些内容已更改。 如果发现恢复队列突然增加，以下度量值可用于故障排除：

  • log Redo rate (KB/sec) ( AlwaysOn 仪表板)
  • DMV sys.dm_hadr_database_replica_states中的Redo_rate

获取重做率的基线费率

在 AlwaysOn 性能正常期间，监视繁忙可用性组数据库的重做速率。 在通常繁忙的工作时间，它们看起来是怎样的？ 在维护期间，当大型事务 (索引重新生成时，ETL 进程) 提高系统上的事务吞吐量时，这些速率是多少？ 观察恢复队列增长时，可以比较这些值，以帮助确定更改的内容。 工作负荷可能比平时大。 如果重做率较低，可能需要进一步调查以确定原因。

工作负荷卷很重要

如果有大型工作负荷 (（例如针对 100 万行的 UPDATE 语句、在 1 TB 的表上重新生成索引），甚至) 插入数百万行的 ETL 批，则应该会看到一些恢复队列增长，无论是立即还是随着时间的推移。 当可用性组数据库中突然进行大量更改时，这是预料之中的。

如何诊断恢复 (重做) 排队

确定特定辅助副本 (replica) 可用性组数据库的恢复队列后，连接到辅助副本 (replica) ，然后查询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

重要

对于有意义的等待类型输出，使用前面介绍的方法之一来监视这种情况时，应观察到恢复队列正在增加。

在此示例中， (报告了一些与 I/O 相关的等待类型 PAGEIOLATCH_UP， PAGEIOATCH_EX) 。 监视以检查这些等待类型是否继续具有最大值wait_times，如下一列中报告的那样。

显示下一列中报告的最大等待时间的屏幕截图。

SQL Server重做等待类型

确定等待类型后，请查看以下文章SQL Server 2016/2017：可用性组辅助副本 (replica) 重做模型和性能 - Microsoft Tech Community作为导致恢复队列的常见等待类型的交叉引用，并获取解决问题的帮助。

辅助报表服务器上阻止的重做线程

如果解决方案针对辅助副本 (replica) 上的可用性组数据库定向报告 (查询) ，则这些只读查询 (Sch-S) 锁获取架构稳定性。 这些 Sch-S 锁可以阻止重做线程获取架构修改 (Sch-M) 锁 (也称为“架构修改锁”，或者 LCK_M_SCH_M) 使任何数据定义语言 (DDL) 更改，例如 ALTER TABLE 或 ALTER INDEX。 被阻止的重做线程在解除阻止之前无法应用日志记录。 这可能会导致恢复排队。

若要检查阻止重做的历史证据，请使用 SSMS 在辅助副本 (replica) 上打开AlwaysOn_health Xevent 跟踪文件。 查找 lock_redo_blocked 事件。

显示检查已阻止重做的历史证据的屏幕截图。

使用性能监视器主动监视受阻的重做对恢复队列的影响。 添加 SQL Server：:D atabase Replica：：Redo blocked/sec 和 SQL Server：:D atabase Replica：：Recovery 队列计数器。 以下屏幕截图显示了一个ALTER TABLE ALTER COLUMN命令，该命令针对主副本 (replica) 同时针对辅助副本 (replica) 上的同一表运行长时间运行的查询。 Redo blocked/sec 计数器指示ALTER TABLE ALTER COLUMN命令已运行。 当长时间运行的查询在辅助副本 (replica) 的同一个表上运行时，主数据库上的任何后续更改都将导致恢复队列增加。

显示架构修改锁等待类型的监视器的屏幕截图。

监视重做线程尝试获取的架构修改锁等待类型。 为此，请使用前面所述的查询来检查针对 的重做操作sys.dm_exec_requests报告的等待类型。 可以在正在进行的重做阻塞中观察到 的 LCK_M_SCH_M 等待时间增加。

显示LCK_M_SCH_M等待时间增加的屏幕截图。

单线程重做

SQL Server Microsoft SQL Server 2016 中引入了辅助副本 (replica) 数据库的并行恢复。 如果在运行 SQL Microsoft Server 2012 或 Microsoft SQL Server 2014 时遇到恢复队列，可以升级到该程序的更高版本，以提高生产环境中的重做性能。

使用并行恢复体系结构的更高级的SQL Server版本中，可能会发生单线程重做。 在这些版本中，SQL Server 实例最多可以使用 100 个线程进行并行重做。 根据处理器和可用性组数据库的数量，并行重做线程最多可分配 100 个线程。 如果达到 100 个线程重做限制，则会为可用性组中的某些数据库分配一个重做线程。

若要确定可用性组数据库是否使用并行恢复，请连接到辅助副本 (replica) ，并使用以下查询来确定对可用性组数据库应用恢复 (线程) 行数。 在以下示例中，如果“agdb”数据库是单个线程，并且其命令为 DB STARTUP，则恢复工作负载可能会受益于并行恢复。

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')

显示如何确定可用性组数据库是否使用并行恢复的屏幕截图。

如果验证数据库是否使用单线程重做，请查看前面所述的算法，以确定SQL Server是否超过专用于并行恢复的 100 个工作线程数。 这种情况可能是“agdb”数据库仅使用单个线程进行恢复的原因。

SQL Server 2022 现在使用新的并行恢复算法，以便根据工作负载分配工作线程进行并行恢复。 这消除了忙碌数据库保留在单线程恢复中的可能性。 有关详细信息，请参阅“Always On可用性组的先决条件、限制和建议”的“按可用性组的线程使用情况”部分。