SQL Server提高事务复制效率优化（四）修改数据同步过程优化

1、原理

      我说的数据修改同步过程指的是在快照生成完毕，分发代理将快照应用于订阅服务器完成订阅服务器初始化后，发布服务器后续的更改同步到订阅服务器过程，这也就是我们常常关注的延迟。此过程主要涉及两个代理，分发代理和日志读取代理，因为我们使用的是推送订阅，所以这两个代理都工作在分发数据库上。首先，日志读取代理读取发布事务日志，并表示任何INSERT、UPDATE以及DELETE语句，然后将这些事务定时将批量复制到分发服务器的分发数据库中。分发库中存储着事务和命令，每个事务以多条命令组成。分发代理顺序分发分法库中的事务，以逻辑命令（SQL语句）方式在订阅库上执行。流程图见下：

     优化这两个代理主要考虑从两方面考虑，一方面是缩短每个批次的间隔，一方面需要在小间隔的情况下增加代理处理的性能，这两个方面是相关联的，所以要一起修改才能起作用。

2.复制延迟测试

• 查看日志读取代理配置文件的默认参数：

• 查看分发代理配置文件的默认参数：

SELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =3
SELECT * FROM MSagent_parameters WHERE profile_id= 4

• 通过跟踪令牌方式测试复制延迟时间

3.代理参数优化测试

• 新建日志读取代理参数配置文件，命名为“日志读取代理参数优化参数 ”
• 日志读取代理参数优化

-- 查询该代理的profile_id   
SELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =2
  
EXEC sp_change_agent_parameter @profile_id = 20,
    @parameter_name = 'ReadBatchSize', @parameter_value = 2000    
   
EXEC sp_change_agent_parameter @profile_id = 20,
    @parameter_name = 'PollingInterval', @parameter_value = 1
新建分发参数配置文件，命名为“分发代理参数优化参数”
EXEC sp_change_agent_parameter @profile_id = 18,
    @parameter_name = 'PollingInterval', @parameter_value = 1
   
EXEC sp_change_agent_parameter @profile_id = 18,
    @parameter_name = 'CommitBatchSize', @parameter_value = 1000
   
EXEC sp_change_agent_parameter @profile_id = 18,
    @parameter_name = 'CommitBatchThreshold', @parameter_value = 10000 

• 优化参数后测试结果（记得更改代理配置文件后要重启代理生效），效果立竿见影，但是不是峰值情况。我们在生产上应用的效果很好，高峰值时也能保证很低的延迟。对于CommitBatchSize和CommitBatchThreshold的参数设置评估方法，CommitBatchThreshold取DML操作数/s上限作为设置，CommitBatch就是CommitBatchThreshold/10~CommitBatchThreshold/5。