排查Always On可用性组中的日志发送队列问题

本文提供与日志发送队列相关的问题的解决方法。

什么是日志发送队列？

对主副本 (replica) (（如 、 UPDATE和 DELETE) ）INSERT上可用性组数据库所做的更改将写入事务日志，并发送到可用性组次要副本。 日志发送队列定义主数据库的日志文件中尚未发送到辅助副本的日志记录数。

日志发送队列的症状和影响

日志发送队列存储所有易受攻击的数据

如果主副本 (replica) 在突然的灾难中丢失，并且故障转移到这些更改尚未到达的辅助副本 (replica) ，则这些更改不会显示在数据库的新主副本 (replica) 副本中。 这将排除在运行完整数据库和日志备份时存储的任何更改。

不断增长的日志发送队列会导致事务日志文件增长

对于可用性组中定义的数据库，Microsoft SQL Server必须保留在主副本 (replica) 事务日志中尚未传递到次要副本的所有事务。 日志发送队列表示主副本 (replica) 记录的更改的数量，这些更改在正常日志截断事件 (例如在数据库日志备份) 期间无法截断。 大型且不断增长的日志发送队列可能会耗尽托管数据库日志文件的驱动器上的可用空间，或者可能超过配置的最大事务日志文件大小。 有关详细信息，请参阅 事务日志较大时的错误 9002。

各种诊断功能报告可用性组日志发送队列

SQL Server Management Studio中的Always On 仪表板报告日志发送队列。 它可能会报告可用性组不正常。

如何检查日志发送队列

日志发送队列是按数据库度量的。 可以使用主副本 (replica) 上的 Always On 仪表板，或使用主要或辅助副本 (replica) 上的sys.dm_hadr_database_replica_states动态管理视图 (DMV) 来检查此值。 性能监视器计数器用于针对辅助副本 (replica) 检查日志发送队列。

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

查询sys.dm_hadr_database_replica_state

sys.dm_hadr_database_replica_states DMV 为每个可用性组数据库报告一行。 该报表中的一列是 log_send_queue_size。 此值是日志发送队列大小（以 KB (KB) 为单位）。 可以设置查询，例如以下查询，以监视日志发送队列大小中的任何趋势。 查询在主副本 (replica) 上运行。 它使用is_local=0谓词来报告辅助副本 (replica) 的数据，其中 log_send_queue_size 和 log_send_rate 是相关的。

WHILE 1=1
BEGIN
  SELECT drcs.database_name, ars.role_desc, drs.log_send_queue_size, drs.log_send_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

输出如下所示。

查看 Always On 仪表板 中的日志发送队列

若要查看日志发送队列，请执行以下步骤：

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

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

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

3. 若要添加这些列，请右键单击可用性组数据库列标题，然后从可用列列表中选择。

4. 若要 添加日志发送队列大小，请右键单击以下屏幕截图中以红色突出显示的标头。

   

   默认情况下，Always On 仪表板每 60 秒自动刷新一次此数据。

   

查看 性能监视器 中的日志发送队列

日志发送队列特定于每个辅助副本 (replica) 数据库。 因此，若要查看可用性组数据库的日志发送队列，请执行以下步骤：

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

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

3. 在“ 可用计数器”下，选择“ SQLServer：数据库副本 ”和“ 日志发送队列 ”计数器。

4. 在“实例”列表框中，选择要为日志发送队列检查的可用性组数据库。

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

   以下是增加日志发送队列的可能外观。

   

解释日志发送队列值

本部分介绍如何解释日志发送队列大小的值。

日志发送队列何时不正确？ 应允许多少日志发送队列？

你可能会认为，如果日志发送队列报告值为 0，则表示该报告时未发生日志发送队列。 但是，当生产环境繁忙时，应观察到日志发送队列经常报告除零以外的值，即使在正常的 AlwaysOn 环境中也是如此。 在典型生产期间，应观察到此值在 0 和非零值之间波动。

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

• (AlwaysOn 仪表板) 日志发送速率 (KB/秒 )
• sys.dm_hadr_database_replica_states (DMV)
• 数据库副本：：镜像事务数/秒 (性能监视器)

获取日志发送速率和镜像事务/秒的基线速率

在 AlwaysOn 性能正常期间，监视繁忙可用性组数据库的 日志发送速率 和 镜像事务/秒 值。 在通常繁忙的工作时间，它们看起来是怎样的？ 在维护期间，当大型事务在系统上推动更高的事务吞吐量时，它们会是什么样子的？ 观察日志发送队列增长情况时，可以比较这些值，以帮助确定更改的内容。 工作负荷可能比平时大。 如果日志发送速率低于平常，可能需要进一步调查以确定原因。

工作负荷卷很重要

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

如何诊断日志发送队列

确定特定可用性组数据库的日志发送队列后，应检查问题的几种可能的根本原因，如以下部分所述。

重要

对于有意义的等待类型输出，检查在监视以下条件时使用前面部分所述的方法之一增加日志发送队列。

系统太忙

检查主副本 (replica) 上的工作负荷是否重载了系统的 CPU。 如果日志发送队列增加，请 sys.dm_os_schedulers 查询 DMV 并监视 high runnable_tasks_count。 此计数指示当时运行的未完成任务。

SELECT scheduler_address, scheduler_id, cpu_id, status, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count
FROM sys.dm_os_schedulers

下表是结果示例。 如果值增加， runnable_tasks_count 则表示大量任务正在等待 CPU 时间。

scheduler_address	scheduler_id	cpu_id	status	current_tasks_count	runnable_tasks_count	current_workers_count	active_workers_count
0x000002778D 200040	0	0	可见脱机	1	0	2	1
0x000002778D 220040	1	1	VISIBLE ONLINE	108	12	115	107
0x000002778D 240040	2	2	VISIBLE ONLINE	113	2	123	113
0x000002778D 260040	3	3	VISIBLE ONLINE	105	11	116	105
0x000002778D 480040	4	4	VISIBLE ONLINE	108	15	117	108
0x000002778D 4A0040	5	5	VISIBLE ONLINE	100	25	110	99
0x000002778D 4C0040	6	6	VISIBLE ONLINE	105	23	113	105
0x000002778D 4E0040	7	7	可见	109	25	116	109
0x000002778D 700040	8	8	VISIBLE ONLINE	98	10	112	98
0x000002778D 720040	9	9	VISIBLE ONLINE	114	1	130	114
0x000002778D 740040	10	10	VISIBLE ONLINE	110	25	120	110
0x000002778D 760040	11	11	VISIBLE ONLINE	83	8	93	83
0x000002778D A00040	12	12	VISIBLE ONLINE	104	4	117	104
0x000002778D A20040	13	13	VISIBLE ONLINE	108	32	118	108
0x000002778D A40040	14	14	VISIBLE ONLINE	102	12	113	102
0x000002778D A60040	15	15	VISIBLE ONLINE	104	16	116	103

解决方案：如果检测到高 runnable_task_count，请减少系统上的工作负荷或增加可供系统的 CPU 数。

网络延迟

如果辅助副本 (replica) 在物理上远离主副本 (replica) ，则这种情况尤其常见。 多站点可用性组允许客户跨多个站点部署业务数据的副本，以便进行灾难恢复和报告。 这使远程位置的生产数据副本能够进行准实时的更改。

如果辅助副本 (replica) 托管在远离主副本 (replica) ，则日志发送队列可能是由于网络延迟，并且无法像在主副本 (replica) 数据库中生成更改一样快地将更改发送到远程辅助数据库。

重要

SQL Server使用单个连接来同步从主副本到次要副本的更改。 因此，如果辅助副本 (replica) 是远程的，管道的宽度不会影响SQL Server可以发送的数据量。 相反，此量更依赖于管道中的网络延迟 (连接速度) 。

测试网络延迟

• 检查流控制设置是否会导致网络延迟

  Microsoft SQL Server可用性组使用流控制门来避免过度消耗所有可用性副本上的网络资源、内存和其他资源。 这些流控制入口不会影响可用性副本的同步运行状况状态。 但是，它们可能会影响可用性数据库（包括 RPO）的整体性能。

  更高版本的SQL Server更改进入流控制的阈值。 这有助于缓解流控制对日志发送队列等症状的影响。 有关流控制和流控制阈值更改历史记录的详细信息，请参阅 流控制入口。

  可以使用性能监视器来监视流控制，以捕获主副本 (replica) 上的数据。 若要监视数据库流控制，请添加 SQLServer：数据库副本 计数器，并选择 “数据库流控制延迟 ”和“ 数据库流控制/秒 ”计数器。 在“实例”对话框中，选择要检查用于数据库流控制的可用性组数据库。 若要检测和监视流控制副本 (replica) 可用性，请添加 SQLServer：Availability Replica 计数器，并选择流控制时间 (毫秒/秒) 和流控制/秒计数器。

• 检查拥塞 Windows 重启是否会导致网络延迟

  可以通过将 “拥塞 Windows 重启 TCP ”设置设置为 True 来触发导致日志发送排队的网络性能问题。 这是 Windows Server 2016 中的默认设置。 请确保在托管日志发送队列的日志发送队列的可用性组副本的 Windows 服务器上将 “拥塞窗口重启 ”设置为 False 。

  PS C:\WINDOWS\system32> Get-NetTCPSetting | Select SettingName, CwndRestart

  

  有关如何将 TCP 拥塞 Windows 重启 属性设置为 False 的详细信息，请参阅 Set-NetTCPSetting (NetTCPIP) 。

  另请参阅监视Always On可用性组的性能，了解有关同步过程的信息。 本文还介绍如何计算一些关键指标，并提供指向一些常见性能故障排除方案的链接。

• 使用 ping 获取延迟示例

  在 node1 (主副本 (replica) ) 的命令行上，ping node2 (辅助副本 (replica) ) ：

  C:\Users\customer>ping node2
  Pinging node2.customer.corp.company.com [<ip address>] with 32 bytes of data:
  Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=94ms
  Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=97ms
  Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=94ms
  Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=119ms
  
  Ping statistics for 2<ip address>:
  Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
  Approximate round trip times in milli-seconds:
  Minimum = 94ms, Maximum = 119ms, Average = 101ms
  

• 使用独立工具测试从主数据库到辅助数据库的网络吞吐量

  使用 NTttcp 等工具通过单个连接独立检测主要副本和辅助副本之间的网络吞吐量。 网络延迟是日志发送排队的常见原因。 以下步骤演示如何使用 NTttcp 等独立工具来测量网络吞吐量。

  重要

  SQL Server使用单个连接将主副本 (replica) 的更改发送到辅助副本 (replica) 。 在下一部分中，我们将配置并运行 NTttcp 以与 SQL Server) 相同的方式使用单个连接 (来准确比较吞吐量。

  可以从 Github - microsoft/ntttcp 下载 NTttcp。

  若要运行 NTttcp，请执行以下步骤：

  1. 下载该工具并将其复制到基于SQL Server的主服务器和辅助服务器。

  2. 在辅助副本 (replica) 服务器上，打开提升的命令提示符窗口，将目录更改为 NTttcp 工具文件夹，然后运行以下命令：

     ntttcp.exe -r -m 1,0,<secondaryipaddress>-a 16 -t 60

     注意

     在此命令中， <secondaryipaddress> 是辅助副本 (replica) 服务器的实际 IP 地址的占位符。

  3. 在主副本 (replica) 服务器上，打开提升的命令提示符窗口，将目录更改为 NTttcp 工具文件夹，然后通过再次指定辅助副本 (replica) 服务器的实际 IP 地址来运行以下命令：

     ntttcp.exe -s -m 1,0,<secondaryipaddress>-a 16 -t 60

     以下屏幕截图显示了在辅助副本和主要副本上运行的 NTttcp。 由于网络延迟，该工具只能发送 739 KB/秒的数据。 这是你期望SQL Server能够发送的内容。

     辅助副本上的 NTttcp

     

     主副本上的 NTttcp

     

查看性能监视器计数器

验证 NTttcp 报告的内容。 大型事务在主副本 (replica) 的 SQL Server 中运行。 在主副本 (replica) 上启动性能监视器后，添加网络接口：：Bytes Sent/sec 计数器。 此计数器确认主副本 (replica) 可以发送大约 777 KB/秒的数据。 这类似于 NTttcp 测试报告的 739 KB/秒的值。

将主副本 (replica) 上的 SQL Server：:D atabases：：Log Bytes Flushed/sec 值与辅助副本 (replica) 上同一数据库的 SQL Server：:D atabase Replica：：Log Bytes Received/sec 也很有用。 平均而言，我们观察到大约 20 MB/秒的更改是在“agdb”数据库中创建的。 但是，辅助副本 (replica) 平均只收到 5.4 MB 的更改。 这将导致数据库事务日志中尚未发送到辅助副本 (replica) 的未完成更改的主副本 (replica) 日志发送排队。

“agdb”数据库的主副本日志字节数刷新/秒

数据库 agdb 的辅助副本日志字节数/秒