译：SQLServer 事务复制延迟诊断

原文地址：https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/

问题

我安装了几个SQL Server 2012实例的集群，并且在复制延迟方面遇到了问题。该环境为发布者分配了一个专门的SQL Server实例，一个实例具有发布者数据库，另一个实例具有订阅者数据库。据报道，大多数情况下复制中的延迟很高。我还注意到，在分发服务器上有很多阻塞，并且CPU出现很大的峰值。

 

解决方案

解决延迟问题不是一个简单的流程，您需要收集大量的数据，分析数据，依次进行更改，然后监视，以查看您是否已解决问题。这是一个连续的过程，直到您获得可接受的延迟。

理解事务复制中的数据流

在我们开始之前，理解SQL Server事务复制的数据流程将会有所帮助。SQLServer的事务复制主要有三个组件：

发布者（Publisher）　　- 需要复制数据的数据库/服务器
分发者（Distributor）　　- 存储临时复制数据的数据库/服务器
订阅者（Subscriber）　　- 消费复制数据的目标数据库/服务器

通常，在高使用的OLTP系统中，每个组件都是一个专用的SQL Server，以支持高可用性。

图1显示了事务复制的体系结构。

　　　Figure 1 - Replication Architecture (BOL: http://msdn.microsoft.com/en-us/library/ms151176.aspx)

监控SQLServer 事务复制

有必要实施延迟告警，以监控并提示延迟是否高于您定义的某个阈值。根据您的环境以及与用户的业务服务水平协议(SLA)，它可能是5分钟、10分钟甚至几秒钟。在解决延迟问题时，这一点非常重要。延迟报告应包含有关总延迟、发布者和分发者之间的延迟、分发者和订阅者之间的延迟的信息，因此您可以确切地知道复制的哪个部分存在问题。

跟踪令牌通常用于测量延迟。您可以使用复制监视器（RM）为每个发布插入跟踪令牌。或者，您也可以使用T-SQL命令。

有关跟踪令牌的更多详细信息，请参阅此BOL：http://technet.microsoft.com/en-us/library/ms151846(v=sql.105).aspx

如何获取复制延迟

 以下是sp_replcounters在性能良好的环境中的输出。

Table 1 - sp_replcounters输出结果示例

• Database - 发布数据库
• Replicated transactions -日志中等待传递到分发数据库的事务数
• Replication rate trans/sec - 平均每秒向分发数据库传递的事务数
• Replication latency - 事务在分发之前记录在日志中的平均时间(以秒为单位)
• Replbeginlsn - 日志中当前截断点的LSN (Log sequence number)
• Replendlsn - 等待发送到分发数据库的下一个提交记录的LSN

使用上述信息，您可以确定总体复制延迟有多好。“"replication transactions rate/sec”的值越高，复制的数据传输速度就越好。“复制延迟(秒)”列的数值也很低。

性能较差的复制系统的输出示例如表2所示。

Table 1 - sp_replcounters输出复制延迟较差的示例

在这种情况下，您可以看到延迟超过2.5小时(请参阅复制延迟列9232秒)。同时可以看到数据传输速率相当不错(1612.123)。那么问题出在哪里呢?查看复制的事务，它超过1100万个，这意味着有超过1100万个命令等待传递到分发数据库。换句话说，它们仍然在发布者数据库的事务日志(T-Log)中。所以在这种特殊情况下，延迟主要发生在发布者和分发者之间。如果您配置了延迟报告，它将显示发布者和分发者之间的高延迟值。

如果你看到像上面(表2)这样奇怪的高数字，这可能是由于以下原因:

• 在发布者数据库中发生了大型事务
• 性能较慢的网络
• 性能较慢的存储器

如果您在输出中看到数百万个等待命令，并且您认为这不是由于网络慢、存储慢或发布者的意外OLTP操作造成的，那么问题可能是与发布者数据库的T-Log配置有关。

记住，复制是SQL Server中基于日志的操作之一。因此，发布者数据库的t-log配置与复制的性能密切相关。名为Log Reader的程序扫描t-log以识别要复制的命令(参见图1)。因此，在这种情况下，您需要注意t-log的大小，根据发布者的事务数量、t-log的vlf数量和vlf的大小是否合适。对于复制，所有这些参数都很重要。就vlf的数量而言，确定t-log的“最佳点”是相当具有挑战性的。下面的链接可能会有所帮助。

• https://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/
• http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

SQLServer日志读取代理

日志读取器是一个在分发服务器上执行的扫描发布数据库T-Log的可执行程序。有两个线程执行这项工作：

读取器线程 - 通过存储过程sp_replcmds读取T-Log。这扫描T-Log并识别要复制的命令，跳过不需要复制的命令。
写入器线程 - 通过sp_MSadd_replcmds将读取器线程识别的事务日志写入分发数据库。
这两个存储过程都是在配置事务复制时创建的系统存储过程。日志读取器代理配置文件有一些参数，您可以使用这些参数更改日志读取器的行为，从而可以更改复制行为。仔细查看日志读取器的参数值是排除复制问题（包括延迟）的重要部分。

Fore more details: BOL: http://msdn.microsoft.com/en-us/library/ms146878.aspx

如何查看日志读取代理配置文件

在SSMS中，连接到分发服务器。右键单击Replication，然后单击Properties。(参阅图2及图3)

 Figure 2 - Get distributor properties

 Figure 3 - Distributor properties

单击Figure 3所示的分发服务器属性窗口中的Profile Defaults。将显示Agent Profiles窗口，如图4所示。

 Figure 4 - Agent Profiles

“代理配置文件”窗口的右窗格包含所有复制代理配置文件。从列表中选择日志读取器代理，您将看到日志读取器的配置文件。选中的是当前正在使用的，您可以单击…来获取Log Reader Agent Profile的配置值，如下面的图5所示。

Figure 5 - Profile Parameters

注意:当您更改日志阅读器属性时，它们将不会生效，直到您重新启动SQL Server代理。

日志读取器代理配置文件的重要参数

在事务性复制系统的微调过程中，需要调整某些参数。

• continuous -指定代理是否尝试连续轮询复制的事务。如果指定，即使没有挂起的事务，代理也会以轮询间隔轮询源的复制事务。
• historyverboselevel[0| 1| 2] -指定在日志读取器操作期间记录的历史数量。您可以通过选择1来最小化历史日志记录的性能影响。
• maxcmdsintran -指定日志阅读器向分布数据库写入命令时，分组到事务中的最大语句数。使用此参数允许日志读取器代理和分发代理在订阅服务器上应用时，将发布服务器上的大事务(由许多命令组成)划分为几个较小的事务。指定此参数可以减少分发服务器上的争用，并减少发布服务器和订阅服务器之间的延迟。由于原始事务以较小的单元应用，因此订阅服务器可以在原始事务结束之前访问大型逻辑发布者事务的行，从而打破严格的事务原子性。默认值为0，它保留发布服务器的事务边界。
•  pollinginterval—查询复制事务日志的频率(以秒为单位)。缺省值是5秒（译者注：轮训的频率是5秒钟，这意味着SQLServer的事务复制的最大延迟会超过5秒，因为日志读取器读取到日志之后要写入distribute库，再由日志写入器写入分发数据库，然后再推送（拉取）的方式写入订阅者数据库，这个过程）。
• readbatchsize -每个处理周期从发布数据库的事务日志中读出的最大事务数，默认为500。代理将继续分批读取事务，直到从日志中读取所有事务。Oracle publisher不支持此参数。
• readbatchthreshold—分发代理发送到订阅服务器之前要从事务日志中读取的复制命令的数量。默认值为0。如果未指定此参数，则日志读取代理将读取到日志的末尾或-ReadBatchSize(事务数)中指定的数量。

如何确定日志读取器代理配置文件设置

您可以查询分布数据库中的MSLogreader_history表，以查看日志读取器的统计信息。通过分析这些数据，您可以确定日志读取器的性能。你可以使用下面的查询;

USE distribution 
GO 

SELECT time, 
CAST(comments AS XML) AS comments, 
runstatus, 
duration, 
xact_seqno, 
delivered_transactions, 
delivered_commands, 
average_commands, 
delivery_time, 
delivery_rate, 
delivery_latency / ( 1000 * 60 ) AS delivery_latency_Min 
FROM mslogreader_history WITH (nolock) 
WHERE time > '2014-10-28 16:00:00.130' 
ORDER BY time DESC 

很难附加一个样本输出，因为输出非常宽。然而，我想强调其中的一些字段。

查看下面Comments栏中的值。它包含有价值信息的xml片段。Comments列提供了关于日志阅读器执行情况的信息。下表显示了复制环境中实际数据的六个不同样例记录。看看第2、3和6行。它通过状态1、2和3消息显示更多信息。

如果您看到很多类似于“在第4条中扫描了大约250万条日志记录，其中有0条被标记为复制”的消息，这意味着日志读取器代理找到了0条要复制的记录。这实际上意味着在发布服务器中进行的许多操作没有被标记为复制。在这种情况下，增加-ReadBatchSize参数将是有益的。该参数的默认值是500，但是您可以将该值增加几千来扫描更多的t-log记录，因为大多数情况下您找不到太多需要复制的数据。

 下面是这些status值的含义:

• status 1 -正常活动。没什么好担心的
• status 2 -读线程必须等待写线程。有一些问题
• status 3 -写线程必须等待读线程。有一些问题

使用这些消息，您可以将日志读取器代理性能分析确定为读取器或写入器线程问题。您需要知道的另一个重要数据列是“xact_seqno”，它是最后处理的事务序列号。看看这个值，你会发现它经常变化。如果是这样，则复制命令的处理速度很快。有时，您可能会在xact_seqno列中长时间看到相同的值，甚至可能持续几个小时。这表明发布者数据库中发生了一个大型事务，导致了大型DML活动。您可以使用下面的代码片段识别事务的实际命令。

USE distribution 
go 
EXEC Sp_browsereplcmds 
@xact_seqno_start = '0x0008BF0F008A6D7F00AA', 
@xact_seqno_end = '0x0008BF0F008A6D7F00AA', 
@publisher_database_id = 10 

@publisher_database_id可能与发布者服务器的数据库id不同。在执行上面的代码之前，您需要首先知道这一点。使用下面的代码找到publisher_database_id。

USE distribution
GO

SELECT * FROM dbo.MSpublisher_databases

--或者

USE distribution 
go 
SELECT TOP 1 publisher_database_id 
FROM msrepl_commands 
WHERE xact_seqno = '0x0008BF0F008A6D7F00AA' 

注意:此发布者database  id与发布数据库中的sys.databases中的database id不同。

请参考sp_browsereplcmd查询的命令列，查看实际执行的命令。通过这种方式，您可以更好地了解在复制缓慢时正在发生的情况。

如果事务有数百万个DML活动，则运行sp_browsereplcmd查询需要时间。此外，您可以使用@article_id或@command_id或两者过滤记录，如下所示;

USE distribution 
go 
EXEC Sp_browsereplcmds 
@xact_seqno_start = '0x0008BF0F008A6D7F00AA', 
@xact_seqno_end = '0x0008BF0F008A6D7F00AA', 
@publisher_database_id = 10, 
@article_id = 1335, 
@command_id= '1000000' 

特定复制表有多大

分发数据库有许多表来支持SQL Server复制。知道它们有多大是很重要的。至少是最重要的之一。这应该是您的故障排除工作的一部分。我通常使用下面的查询来查看事务复制中最中心的表的记录计数。

USE distribution 
GO 
SELECT Getdate() AS CaptureTime, 
Object_name(t.object_id) AS TableName, 
st.row_count, 
s.NAME 
FROM sys.dm_db_partition_stats st WITH (nolock) 
INNER JOIN sys.tables t WITH (nolock) 
ON st.object_id = t.object_id 
INNER JOIN sys.schemas s WITH (nolock) 
ON t.schema_id = s.schema_id 
WHERE index_id < 2 
AND Object_name(t.object_id) 
IN ('MSsubscriptions', 
'MSdistribution_history', 
'MSrepl_commands', 
'MSrepl_transactions'
) 
ORDER BY st.row_count DESC 

mssubscriptions　　为订阅中的每个已发布的文章包含一行

msdistribution_history　　包含与本地分发器相关联的分发代理的历史记录行

msrepl_commands　　包含复制的命令行

msrepl_transactions　　为每个复制的事务包含一行

如果您看到较高的行数(可能超过100万或200万)，这意味着复制存在一些问题。这可能是以下原因之一:

1，清理作业(在分发服务器中)未运行

2，向订阅者发送命令要花很多时间

3，由于 clean-up job（正在执行），可能造成分发服务器出现阻塞

（译者注：
对于清理作业，该代理每隔 10 分钟(默认值) 清除分发数据库中的代理历史记录。根据分发的大小不同，可改变该代理的执行频率。 删除复制过程中publication和replication自身产生的历史数据，默认删除72小时之外的数据，参考如下截图

）

使用下面的查询来确定当前在分发服务器中正在发生什么。(您可以在任何服务器上使用这个查询)

SELECT r.session_id, 
s.program_name, 
s.login_name, 
r.start_time, 
r.status, 
r.command, 
Object_name(sqltxt.objectid, sqltxt.dbid) AS ObjectName, 
Substring(sqltxt.text, ( r.statement_start_offset / 2 ) + 1, ( ( 
CASE r.statement_end_offset 
WHEN -1 THEN 
datalength(sqltxt.text) 
ELSE r.statement_end_offset 
END 
- r.statement_start_offset ) / 2 ) + 1) AS active_statement,
r.percent_complete, 
Db_name(r.database_id) AS DatabaseName, 
r.blocking_session_id, 
r.wait_time, 
r.wait_type, 
r.wait_resource, 
r.open_transaction_count, 
r.cpu_time,-- in milli sec 
r.reads, 
r.writes, 
r.logical_reads, 
r.row_count, 
r.prev_error, 
r.granted_query_memory, 
Cast(sqlplan.query_plan AS XML) AS QueryPlan, 
CASE r.transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncomitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' 
END AS Issolation_Level, 
r.sql_handle, 
r.plan_handle 
FROM sys.dm_exec_requests r WITH (nolock) 
INNER JOIN sys.dm_exec_sessions s WITH (nolock) 
ON r.session_id = s.session_id 
CROSS apply sys.Dm_exec_sql_text(r.sql_handle) sqltxt 
CROSS apply 
sys.Dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) sqlplan
WHERE r.status <> 'background' 
ORDER BY r.session_id 
go 

如果您看到LCK_M_S等待阻塞，这可能是由于清理工作。此作业（clean-up job）每10分钟运行一次，它会清除已经复制的命令。安全的做法是停止并禁用该作业几个小时以清除阻塞。

我经常注意到阻塞源头程序是sp_MSsubscription_cleanup(这是来自sp_MSdistribution_cleanup的嵌套存储过程调用，sp_MSdistribution_cleanup是“分发清理”作业)。您还可以注意到上述CXPACKET等待类型的存储过程，它阻塞了以下语句。

UPDATE msdistribution_history 
SET runstatus = @runstatus, 
time = @current_time, 
duration = @duration, 
comments = @comments, 
xact_seqno = @xact_seqno, 
updateable_row = @this_row_updateable, 
error_id = CASE @error_id 
WHEN 0 THEN error_id 
ELSE @error_id 
END 
WHERE agent_id = @agent_id 
AND timestamp = @lastrow_timestamp 
AND ( runstatus = @runstatus 
OR ( @update_existing_row = 1 
AND runstatus IN ( @idle, @inprogress ) 
AND @runstatus IN ( @idle, @inprogress ) ) )

上面语句的等待类型是LCK_M_X，等待资源是MSdistribution_history表。这个表在阻塞源存储过程中使用，并且它已经获得了大多数行的共享锁。我觉得MS需要对这段代码进行一些优化。当我比较2008和2012版本的SQL Server之间的清理工作存储过程时，我注意到它在2012版本中增加了一倍的代码行。

同时，您也可能会注意到分发服务器中的高CPU，这是由于上述阻塞器源造成的许多阻塞。除了停止和禁用clean-up job一段时间外，您真的无能为力。您也可以尝试将发行版服务器中的MAXDOP设置为1，以降低CPU使用率。

改善分发者和订阅者之间的延迟

 再次感谢延迟报告。如果您确定复制延迟是在分发服务器和订阅服务器之间，那么值得考虑以下几点。

Publishing Stored Procedure Execution

这在对发布者执行大型批处理操作(例如:DELETE)的情况下特别有用。我曾见过这样的情况，由于大规模的批量删除，数百万行受到影响，并且在它们发生的那一刻，它开始将命令传递给分发服务器，然后是订阅者。这会减慢复制速度，并且您会注意到延迟增加。使用此方法，可以在订阅服务器上执行相同的大型批处理操作，而不是通过分发服务器传递单个命令。但是，在实现此解决方案之前，您需要花时间进行一些研究，并评估这对于您的环境的可行性。有很多因素需要你注意。

了解更多详情，请访问 http://msdn.microsoft.com/en-us/library/ms152754.aspx

Enable Multiple Streams for Subscriber

为订阅者启用多个流可以通过并行应用订阅者更改来极大地提高聚合事务性复制吞吐量。在将其投入生产之前，您仍然需要考虑许多因素，并且需要做一些功课。

了解更多详情，请访问http://technet.microsoft.com/en-us/library/ms151762(v=sql.105).aspx

Maintain Indexes and Statistics in Distribution Database

分发数据库在SSMS中属于系统数据库。然而，需要某种程度的DBA干预来保持分布数据库的良好状态。分布数据库与普通用户数据库一样具有表、索引和统计信息。我们知道，索引需要维护(重建/重组)，并且需要在用户数据库中运行更新统计信息，那么为什么不在分布数据库中进行相同的操作呢?清理存储过程有自己的统计数据更新语句，以使统计数据保持最新，但不是所有统计数据都是最新的。将索引和统计更新作业部署到分布数据库，并安排它们在非高峰时间运行，就像在用户数据库中所做的那样，这是完全可以的。按照MS的建议，我已经在生产环境中这样做了。

Distribution Agent 性能

您可以查询MSdistribution_history表来查看Distribution Agent的执行情况。

USE distribution 
go 
SELECT TOP 100 time, 
Cast(comments AS XML) AS comments, 
runstatus, 
duration, 
xact_seqno, 
delivered_commands, 
average_commands, 
current_delivery_rate, 
delivered_transactions, 
error_id, 
delivery_latency 
FROM msdistribution_history WITH (nolock) 
ORDER BY time DESC 

上述查询的输出类似于Log Reader历史表的输出。查看Comments列的值。如果您看到状态为1的消息，则表示分发代理正常运行。使用xact_seqno可以识别复制的命令。如果您注意到xact_seqno的相同值持续了很长时间，这意味着它正在复制一个大事务。

Distribution Agent 配置文件

与日志读取器代理配置文件一样，分发服务器上也有分发代理配置文件。如果您从右窗格打开Agent Profiles窗口(参见图4)，您可以选择Distribution Agents来查看配置文件。您可以调整代理的参数值来更改复制行为。您可以在发布级别执行此操作，也可以应用于所有发布。它需要在分发服务器中重新启动SQL Server Agent才能生效。

以下是一些你可以考虑调整的参数:

• - commitbatchsize -在发出COMMIT语句之前要发送给订阅者的事务数。默认值是100。
• - commitbatchthreshold—在发出COMMIT语句之前要向订阅服务器发出的复制命令的数量。默认值是1000。
• - historyverbosellevel[0 | 1 | 2 | 3] -指定在分发操作期间记录的历史数量。您可以通过选择1来最小化历史日志记录的性能影响。
• - maxdeliveredtransactions -在一次同步中应用于订阅者的推或拉事务的最大数量。值为0表示最大事务数为无限大。订阅服务器可以使用其他值来缩短从发布服务器提取同步的持续时间。
• - pollinginterval—以秒为单位，查询发布数据库中复制事务的频率。缺省值是5秒。
• -SubscriptionStreams[0 | 1 | 2 |…[64] -每个分发代理允许的连接数，以并行地向订阅服务器应用批量更改，同时保持使用单个线程时存在的许多事务特征。SQL Server Publisher支持的取值范围是1 ~ 64。仅当发布服务器和分发服务器运行在SQL Server 2005或更高版本时，才支持此参数。对于非sql Server订阅用户或点对点订阅，该参数不支持，必须为0。

更多详细信息:BOL: http://msdn.microsoft.com/en-us/library/ms147328.aspx

Next Steps

• 如果您仍然看到分发服务器的高CPU、分发服务器的持续阻塞、甚至在完成本文中建议的更改后延迟增加等问题，您可能需要进行更多的分析，并且可能需要向微软提出申诉。
• 有关进一步分析，请参阅此链接。它有指令/脚本来收集更多的数据。
• 查看所有SQL Server复制技巧。