SQL Server提高事务复制效率优化(二)快照初始化优化

测试数据表量1500w+,使用初始化默认的快照代理参数,复制的三个过程包括快照初始化,订阅初始化和数据修改复制,主要对快照代理、分发代理、日志读取代理分别作了参数优化,并给出优化前后的对照实验测试。

1.初始化快照
  • 在复制监视器中找到快照代理位置,右键,选择代理配置文件:
 SQL Server提高事务复制效率优化(二)快照初始化优化
  • 查看默认配置:
SQL Server提高事务复制效率优化(二)快照初始化优化  SQL Server提高事务复制效率优化(二)快照初始化优化
  • 初始化快照时间:
SQL Server提高事务复制效率优化(二)快照初始化优化 SQL Server提高事务复制效率优化(二)快照初始化优化

2.优化参数测试

  • 新建一个快照代理配置文件,命名为“快照代理优化参数 ”
  • 查找这个代理配置文件的profile_id
use msdb
SELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =1
或者
EXEC sp_help_agent_profile @agent_type= 1

SQL Server提高事务复制效率优化(二)快照初始化优化

这里的agent_type注解如下

代理的类型:
1 = 快照代理
2 = 日志读取器代理
3 = 分发代理
4 = 合并代理
9 = 队列读取器代理 
  • 修改配置文件,这里新增了MaxBcpThreads,MaxNetworkOptimization参数,修改了BcpBatchSize,HistoryVerboseLevel参数,对于性能提高最大的参数就是MaxBcpThreads,其他参数调整对性能调高不大,但MaxNetworkOptimization参数一定要调整到1,可以减少传输到订阅服务器上不必要的操作。
USE msdb
EXEC sp_add_agent_parameter @profile_id = 17,
@parameter_name = 'MaxBcpThreads', @parameter_value = 4 EXEC sp_add_agent_parameter @profile_id = 17,
@parameter_name = 'MaxNetworkOptimization', @parameter_value = 1 EXEC sp_change_agent_parameter @profile_id = 17,
@parameter_name = 'BcpBatchSize', @parameter_value = 200000 EXEC sp_change_agent_parameter @profile_id = 17,
@parameter_name = 'HistoryVerboseLevel', @parameter_value = 1
  • 调整后的参数:
SQL Server提高事务复制效率优化(二)快照初始化优化 SQL Server提高事务复制效率优化(二)快照初始化优化
  • 优化后测试结果,时间大大减少。但是如果在有业务负载同时生成快照,那么资源消耗很大,建议合理调整参数,不要设置太大并发,用默认配置已经有很高的I/O了。
SQL Server提高事务复制效率优化(二)快照初始化优化 SQL Server提高事务复制效率优化(二)快照初始化优化
上一篇:SQL Server中锁与事务隔离级别


下一篇:MySQL查询优化注意下面的四个细节