首页 / MSSQL / SQL Server 事务复制分发到订阅同步慢

SQL Server 事务复制分发到订阅同步慢

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server 事务复制分发到订阅同步慢，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6117字，纯文字阅读大概需要9分钟。

内容图文

在发布中，复制是使用日志读取器读（sp_replcmds）取发布数据库中的事务日志的，日志读取器是按事务顺序读取的，所以每个数据库只能有一个（若有CDC也使用同一个日志读取器）。

首选，在事务日志中，到底有多少是需要复制的？使用以下命令，可以确定事务日志中被标志为复制的命令有多少。

USE <publisher_database>
GO
SELECT count(*) FROM ::fn_dblog(NULL, NULL) WHERE Description=‘REPLICATE‘
GO

如果事务日志中标志为复制的命令很多，常见的种情况有：

1. 有一个较大的事务还没读取到；

2. 日志读取器出现问题；

正常来说，日志读取器扫描日志还是比较快的，不会有什么问题，不会累计较多待读取的日志。可以使用性能计数器监控日志读取器读取情况：

"\SQLServer:Replication Logreader\Logreader:Delivered Cmds/sec"

"\SQLServer:Replication Logreader\Logreader:Delivered Trans/sec"

日期读取器读取事务日志后，会通过存储过程 sp_msadd_commands 写入到分发服务器上的分发数据库 distribution 。但是写入是否正常呢？从上面计数器跟踪的情况，可以估计每分钟有读取了多少命令。需要分发的事务和命令，系统存储过程分别写入了分发库上的表 MSrepl_transactions和 MSrepl_commands ，这两个表记录了需要分发的命令（注意：其中的命令可能已经分发到订阅了，只是还没清除）。将两表关联按分钟统计，可以确定每分钟写入到分发库上的命令有多少了。

SELECT
    DATEPART(mm, [entry_time]) ‘month‘,
    DATEPART(dd, [entry_time]) ‘day‘,
    DATEPART(hh, [entry_time]) ‘hour‘,
	DATEPART(MI, [entry_time]) ‘Minute‘,
    COUNT(C.[xact_seqno]) ‘count of commands‘
FROM [dbo].[MSrepl_transactions](nolock) T
INNER JOIN [dbo].[MSrepl_commands](nolock) C
    ON T.[xact_seqno] = C.[xact_seqno] 
	AND T.publisher_database_id=c.publisher_database_id
WHERE [entry_time] >=‘2017-05-04 12:00:00‘
GROUP BY    DATEPART(mm, [entry_time]),
            DATEPART(dd, [entry_time]),
            DATEPART(hh, [entry_time]),
			DATEPART(MI, [entry_time])
ORDER BY 1, 2, 3,4
GO

在发布到分发中，事务命令的读取和写入可做对比，确定读或写是哪段出现问题。

分发到订阅：

分发到订阅，首选确定有多少命令是需要分发的，若直接求和 MSrepl_transactions 和 MSrepl_commands是不准确的，因为有的已经分发了。可以打开复制监视器查看某个发布中未分发的命令，若用脚本查看，有两个方法：

/************************方法一****************************/
EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds 
  @publisher = N‘发布服务器名称‘
, @publisher_db = N‘发布数据库名称‘
, @publication = N‘发布名称‘
, @subscriber = N‘订阅服务器名称‘
, @subscriber_db = N‘订阅数据库名称‘
, @subscription_type =1
go

/************************方法二****************************/
--未分发的事务数
With MaxXact (ServerName, PublisherDBID, XactSeqNo)
As (Select S.name, DA.publisher_database_id, max(H.xact_seqno)
    From distribution.dbo.MSdistribution_history H with(nolock)
    Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id
    Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id
    Group By S.name, DA.publisher_database_id)
Select MX.ServerName, MX.PublisherDBID, COUNT(*) As TransactionsNotReplicated
From distribution.dbo.msrepl_transactions T with(nolock)
Right Join MaxXact MX On MX.XactSeqNo < T.xact_seqno And MX.PublisherDBID = T.publisher_database_id
Group By MX.ServerName, MX.PublisherDBID;
GO
--未分发的命令数
With MaxXact (ServerName, PublisherDBID, XactSeqNo)
As (Select S.name, DA.publisher_database_id, max(H.xact_seqno)
    From distribution.dbo.MSdistribution_history H with(nolock)
    Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id
    Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id
    Group By S.name, DA.publisher_database_id)
Select MX.ServerName, MX.PublisherDBID, COUNT(*) As CommandsNotReplicated
From distribution.dbo.MSrepl_commands C with(nolock)
Right Join MaxXact MX On MX.XactSeqNo < C.xact_seqno And MX.PublisherDBID = C.publisher_database_id
Group By MX.ServerName, MX.PublisherDBID;
GO

总体监控分发到订阅的情况，可以使用计数器跟踪：

"\SQLServer:Replication Dist\Dist:Delivered Cmds/sec"
"\SQLServer:Replication Dist\Dist:Delivered Trans/sec"

若了解更详细情况，可在分发代理中添加以下参数，重启分发作业。

-Output [output_path_and_file_name]
-OutputVerboseLevel [0|1|2]]

接下来回归主题：SQL Server 事务复制分发到订阅同步慢

当前未分发的命令已经堵了六百多万条了，而分发差不多两分钟才分发一次。在分发数据库中查看当前执行了什么命令，经常看到在执行存储过程 sp_MSget_repl_commands ，该存储过程是从 MSrepl_transactions 和 MSrepl_commands 读取未分发的命令，将这些命令应用到订阅中。但是读取出现了等待类型ASYNC_NETWORK_IO ，ASYNC_NETWORK_IO 意思是数据已经读取了，但是客户的还没有完全把数据拿走。按正常理解，这些这个等待类型，很可能是网络问题。