SQLServer提高事务复制效率优化教程

1、SQL Server提高事务复制效率优化教程总体概述随着公司业务的发展，数据量增长迅速，在解决Scale Out的同时，还要考虑到主从的复制延迟 问题，尽量降到1s以内满足线上业务，如果不调整，SQL Server默认的配置可能平均要3s左 右。生产的复制架构采用的是推送方式进行事务复制，发布服务器下面有4个从节点，两两指向同 一虚拟IP，构成负载均衡，服务于不同的线上业务。对于4个节点，发布库和分法库的压力都很 大，订阅库每秒I/O能达到5M，高峰时能达到数十兆。研究并试验了一些时间，给出一些优化建 议：硬件、数据库设计：使用SSD磁盘，有钱的可以使用PCI-e卡，SSD做RAID10，所有

2、节点部署在内网。更改事务隔离级别为READ_COMMITTED_SNAPSHOT。说实话，SQL Server默认事务 隔离级别是读已提交，但是没有多版本的概念，需要设置为 READ_COMMITTED_SNAPSHOT才是我们理解上的读已提交。如果不设置这个，照样会 读写互相阻塞。在创建订阅之前，先将目标数据库的恢复模式改成简单，能够减少日志ALTER DATABASE 数据库名称SET RECOVERY SIMPLE WITH NO_WAIT发布、订阅设计：仅发布需要的数据，对于将日志写入数据库而且还要同步复制的，DBA可直接拒绝。表中存在大量非聚集索引，发布时可以不勾选复制非聚集索引选项

3、。经过测试，虽然数据库 引擎是在应用完快照最后创建索引的，但创建时间很长，有时甚至会出错。可以在后期手工 建立。我们采用导出脚本方式，然后只保留非聚集索引的脚本在订阅库中建立。如果资源允许，单独建立一个分发服务器。发布和分发服务器在一个服务器上会增加性能损 耗，尤其是分发服务器下面有多个订阅服务器时。分发库默认是distribution库，会有专门的作业对该库进行清理，名字就叫“分发清除： distribution”。但这个进程清除分发库的效率太低了，会导致分发库积累大量数据，需要修 改运行间隔和每次清楚的数据量。详见之前的随笔：distribution库过大的问题合理使用订阅方式，如果选择推

4、送订阅，势必加大分发服务器的压力，但延迟低；如果选择 请求订阅，代理服务是在订阅服务器上，减少了分发服务器压力，但延迟会增加。快照代理的生成。对于生成几百GB数据库的快照，采用SSD盘可能在10分钟左右。但会 造成很高的性能损耗，要在业务非高峰期生成快照。快照代理文件也要放在SSD盘上，如果 资源允许，单独一个驱动盘也可以。如果已经有备份了，那么可以通过备份直接来初始化订阅，省去了生成快照和应用快照的步 骤。新发布新的表对象，对于发布库很大的情况下重新初始化是不实际的，可以增量发布，详见 sqlserver同步后在不重新初始化快照的情况下新增表对于已发布的表有批处理更新，一种方法是将批量更新分

5、成若干小批量执行，减小事务处理 的数据量；另一种方法是通过存储过程执行更改，并将存储过程发布。推荐第一种将项目分布在多个发不上，这样相当于变向的实现了多线程并行发布，当然也可以通过参数 实现并行，下面会讲到参数优化：降低复制代理的详细级别，在初始测试、监视或调试期间除外。减小分发代理或合并代理的 -HistoryVerboseLevel参数和-OutputVerboseLevel参数。这样可以减少为跟踪代理 历史记录和输出而插入的新行数。相反，具有相同状态的以前的历史记录消息将更新为新的 历史记录信息。提高测试、监视和调试的详细级别，这样就可以获得有关代理活动的尽可能 多的信息。在系统开销不是

6、很大的情况下，建议使用默认值1,本身这点对性能消耗可以忽 略。增大快照代理的BcpBatchSize参数，-BcpBatch参数表示一个批事务的行数。将快照代理的-MaxNetworkOptimization设置为1,减少将不必要的信息发送到订阅服务 器上。使用快照代理、分发代理的-MaxBcpThreads参数（指定的线程数不应超过计算机上的 处理器数）。此参数指定创建和应用快照时可以并行执行的大容量复制操作的数目。为分发代理使用 -Subscriptionstreams参数。-Subscriptionstreams参数可以显著 提高聚合复制吞吐量。它使到一台订阅服务器的多个连接可以并行应用

7、批量更改，同时在使 用单线程时保持多个事务特征的存在。如果有一个连接无法执行或提交，则所有连接将中止 当前批处理，而且代理将用单独的流重试失败的批处理。在重试阶段完成之前，订阅服务器 上会存在临时事务不一致。失败的批处理成功提交后，订阅服务器将恢复到事务一致状态。 官方文档有这个参数，但增加这个参数报错。增大分发代理的-CommitBatchSize和CommitBatchThreshold参数的值。提交一组事 务的开销是固定的；通过不经常提交大量事务，就可以将开销分散在大量数据上。但是，增 大此参数的优势因应用更改的开销由其他因素（例如包含日志的最大磁盘I/O）限制而降低。另外，需要考虑以下

8、权衡问题：任何导致分发代理重新开始的失败必须回滚并重新应用 大量事务。对于不可靠的网络，越小的值导致失败的几率越小，如果导致失败，将回滚并重 新应用较少量事务。增大日志读取器代理的-ReadBatchSize参数的值。-ReadBatchSize表示日志读取器代 理和分发代理支持事务读取和提交操作的批大小。批大小的默认值为500个事务。日志读 取器代理从日志中读取特定数量的事务，而不管这些事务是否标记为复制。将大量事务写入 发布数据库，而其中只有一小部分标记为复制时，则应使用-ReadBatchSize参数增加日志 读取器代理的读取批大小。此参数不适用于Oracle发布服务器。.为日志读取器代

9、理使用-MaxCmdsInTran参数。-MaxCmdsInTran参数指定日志读 取器向分发数据库写入命令时组成一个事务的语句的最大数量。使用此参数能够使日志读取 器代理和分发代理在订阅服务器上应用命令时将发布服务器上的大事务（由许多命令组成） 分成若干个较小的事务。指定此参数可以减少分发服务器的争用问题并缩短发布服务器与订 阅服务器之间的滞后时间。由于初始事务是以较小的单元应用的，订阅服务器可以在初始事 务结束之前访问一个较大的逻辑发布服务器事务的行，因而会破坏事务的原子性。默认值为 0，这将保持发布服务器的事务边界。官方文档有该参数，但实际设置里面没有。.减小日志读取器代理和分发代理的-

10、PollingInterval参数的值。-PollingInterval参数指 定为要复制的事务查询已发布数据库的事务日志的频率。默认值为5秒。如果减小此值，日 志的轮询将更加频繁，这会使事务从发布数据库传递到分发数据库的滞后时间较低。但是， 应该对较低滞后时间的需要和因频繁轮询导致的服务器上增加的负荷之间进行平衡。快照初始化优化测试数据表量1500w+，使用初始化默认的快照代理参数，复制的三个过程包括快照初始化，订阅 初始化和数据修改复制，主要对快照代理、分发代理、日志读取代理分别作了参数优化，并给出优 化前后的对照实验测试。初始化快照.在复制监视器中找到快照代理位置，右键，选择代理配置文件

11、：Ft&帝目美的1町功作业冒:丁状态-kii上玄岩司.F、_H厉竖时目上一操作:从未启剥）快瞪代理=1艺胸机津代珪打旧皿二1E:E:坨33:33:39传遍L个半公，凄止1 . . .查看默认配置:初始化快照时间:所有订阅跟踪令牌代理1警告1与此发布相关的代理和作业压）：状态作业上次启动时间持续时间上一操作的：已完成快照代坦2016/10/11 20:22:0200:02:38100%已生成 正在运行日志读刖器代理2016/10/11 20:22:5500:03:06传递了 1个事优化参数测试新建一个快照代理配置文件，命名为“快照代理优化参数”查找这个代理配置文件的prouse msdbSELE

12、CT * FROM msdb.dbo .MSagent_profiles WHERE agent_type = 1或者EXEC sp_help_agent_pro 1I这里的agent_type注解如下代理的类型：1=快照代理2=日志读取器代理3 =分发代理4 =合并代理9 =队列读取器代理. 修改配置文件，这里新增了 MaxBcpThreads，MaxNetworkOptimization参数，修改了 BcpBatchSize,HistoryVerboseLevel参数，对于性能提高最大的参数就是MaxBcpThreads，其他参数调整对性能调高不大，但MaxNetworkOptimizat

13、ion参数一 定要调整到1，可以减少传输到订阅服务器上不必要的操作。USE msdbEXEC sp_add_agent_parameter pro = 17,parameter_name = MaxBcpThreads, parameter_value = 4EXEC sp_add_agent_parameter pro = 17,parameter_name = MaxNetworkOptimization, parameter_value = 1EXEC sp_change_agent_parameter pro = 17,parameter_name = BcpBatchSize, pa

14、rameter_value = 200000EXEC sp_change_agent_parameter pro = 17,parameter_name = HistoryVerboseLevel, parameter_value = 1.调整后的参数：优化后测试结果，时间大大减少。但是如果在有业务负载同时生成快照，那么资源消耗很 大，建议合理调整参数，不要设置太大并发，用默认配置已经有很高的I/O 了。际有订刖制枭漕代理催告I与此是个相美的代理和作丑却：1_上悔作LOOS.已生成i个项|作丑 快照代理|二次局可时间ZO1&/1O/1Z 14 U5|恃读时可00：0D：E5I二次局可时问恃读时

15、可I上博作快照代理LOOS.已生成i个项.ZO1&/1O/1Z 14 05：53订阅初始化优化初始化订阅主要是由分发代理分发和应用快照代理之前生成的快照，所以优化的主体是分发代理。1、初始化订阅首先在本地创建一个订阅，发布服务器、分发服务器和订阅服务器都在同一台服务器上，仅 为了测试生产环境不要这样做触击成以版仃卜刊程性*建讪可。从岌布明务器“虹H HABSLCC1C02创建对发布的订寻证下可订祷抵*嚣上创建；闽m mutsuKicaz*厂阅蝴露jplL”.-勺主互置片忘匪努羔却里I划：连续M行,代理程帏户：两15廿作吐呻1:扇努帐户,乓分发版洛器的连垸摸也也程株户MiT弟阳*器白1谆特 住干

16、晋呆壬七L刁好旧五田分发服务器参数配置ELECT * FROM m3db.Ibc.MSagent_piofiles WHERE aqent_type=3 SELECT * FROM MSagent_parametetb WHERE profile_id=4|rrrpruiilejdprofile_nameagent _typetypedescti ptiondef jnofile110 1V/indsws SyicIronizartiDn Manager profile 3CWindows同步驾坦器明月的生宣划牛“Q2默认代理断置又件3NULL135任细万兄记录代理陞置玄件3用于详期历史记录日

17、志的代理配麦文件。C416用于OLEDB流式处理的分凌断盖文件3使用OLEDB流式处理丈处理UDB数据启用的始发弋坦陞专文件。C514遇到数据一致怙错误E寸缱续，3用于跳过数据一致性诸误傕代理翻置文仁。它只能由SQLSsvef订阅服务器使用。Cprofilejdpararneter_namevalue14-Eks-pDaftahSize2H74ZJ6472-Commit EotdiSizeICC34-Commit EatchTlTeshold1GCD44-HistoryVerbose Level154-KeepAlive bfessagelntaval30064-Login Timeout15

18、74-MaocBcpThrBads1S4-MiaKDeli/efi&dTransctbns94-Polling Irten/al5104-QueiyTimecut18W114-SkipErnora124-Transadions PerHistory10C初始化订阅运行时间际讫W.舌巾&操IFCD操怕貌|换作时i可已在 264E34 觐内将快瞭仙 H unc l?HT-MJJ)SljGG1602_ErCP_REPL_TEST 3016 L 012191552 Un 子女件买中.2D 16/10/12 19:23:07需要注意几点:快照代理实际执行的是BCP程序，生成快照文件:spidpn3gra

19、m_namecument_cmd1384Snapshot BCP thread #3 for publication repl jestselect from dbo. syncobj_k463244353731413D where I ID 1 盼142哥日 nd lID。+0963) order by ID /1482Snapshot BCP thread #1 for publication repl jestselect from dbo.syncobj_(k463244353731413D where (ID 4fl%S)and i：ID =：= S196461) order by

20、ID /1590Snapshot BCP thread #Dfor publication repl jestselect 4from dbo.syncObj_(k46S244S5S7314nD where iID 515461) and i：ID=：=烦网勘 order by ID /1687Snapshot BCP thread #2 for publication repl jestselect from dbo.syncobj_k43244353731413D where (ID is null) or (ID = 1864423 order by ID AS快照代理会在快照生成的初始

21、阶段使用锁，虽然时间很短，但是影响范围较大。 在快照生成过程中，不能对表的结构进行更改。2.优化参数测试新建一个分发代理配置文件，命名为“分发代理优化参数” 查找这个代理配置文件的proSELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =3或者EXEC sp_help_agent_pro 3修改配置文件EXEC sp_change_agent_parameter pro = 18,parameter_name = MaxBcpThreads, parameter_value = 4EXEC sp_change_agent_par

22、ameter pro = 18,parameter_name = BcpBatchSize, parameter_value = 100000EXEC sp_change_agent_parameter pro = 18,parameter_name = PollingInterval, parameter_value = 500000优化后订阅库生成测试结果，从测试结果上看并没有明显的优化。MaxBcpThreads并没有 实现多线程的数据导入，后台查询也只有一个会话在执行bulk in操作，BcpBatchSize对 参数影响较小，建议设置的大一些；另外两个参数不影响订阅数据库的生成效率。

23、其实订阅 库生成是整个过程的性能瓶颈所在，如果能使用多线程还是不错的解决方法，希望看到博客 的同学有好方法提供一下。所诜会刊中质握作H）：操作涓息|操1甘寸闩已在 264234 皇秒内将快照从皿602_E7CF_FJEFL_TEST2D V5 子文牛夷中.2016/10/12 19:23:07修改数据同步过程优化1、原理我说的数据修改同步过程指的是在快照生成完毕，分发代理将快照应用于订阅服务器完成订 阅服务器初始化后，发布服务器后续的更改同步到订阅服务器过程，这也就是我们常常关注的延迟。 此过程主要涉及两个代理，分发代理和日志读取代理，因为我们使用的是推送订阅，所以这两个代 理都工作在分发数据

24、库上。首先，日志读取代理读取发布事务日志，并表示任何INSERT. UPDATE 以及DELETE语句，然后将这些事务定时将批量复制到分发服务器的分发数据库中。分发库中存 储着事务和命令，每个事务以多条命令组成。分发代理顺序分发分法库中的事务，以逻辑命令（SQL 语句）方式在订阅库上执行。流程图见下：优化这两个代理主要考虑从两方面考虑，一方面是缩短每个批次的间隔，一方面需要在小间隔 的情况下增加代理处理的性能，这两个方面是相关联的，所以要一起修改才能起作用。2.复制延迟测试查看日志读取代理配置文件的默认参数：SELECT * FROM rnscLb. dbQ.MSag?nt_FTcfiles

25、WHERE ag?nt_type 2SELECT x FROM MS a ge nt parame t er s WHERE prcfile id=2|二I结果消息profiejdpnjfilB_ndmeagent jypetypedescripliondef_pnafile12默认代理甑置文件20NULL1Z 3详细由挝记柔代哩酉置项件史用于详细由史记录目志的代理国置义件。0profiejdparameter_n;amevdue1 2-History 诡 rbose Level122-LDgnUmeQiJ：1532-LogScanThnBshdd42-Poling Interval552Qu

26、eryTimeout饷62-ReadBatchSze5W查看分发代理配置文件的默认参数:SELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =3SELECT * FROM MSagent_parameters WHERE pro= 4SELECT * FROM mscib. dbo. NSagent profiles WHERE aget type=3SELECT * FROM M5snt parameters WHERE profile id=4rrr门结果 J消息pncfilejdprofilejiameagentjypety

27、pedescriptiondef_profie1Windows Synchronzaton Manager profile30Endows问去莒理嚣所用白酒EW曳件。3IS够发代埋伏化萎毓 概认代埋时置义忤3;NULL;45详纸历史记录齿坦甑盖项件20用十详沏历史记我E志的代理况置艾.牛匚516用于OLEDB流式处坦的分发位置又件20使用OLE况洗寸处理为处哩心日数据启用的专发代理断走又件。6U遇到数据一致哇错误时继续。20用于跳过数提一斐性错误的传里呢置文件。它只能由SQLServer订期膀器使隹。pnofilejdparamed:er_namevalue1:-;-BcpBiatchSzeCorrmitBiatGhSiEe-Commit BatchThrediold-HistoryVefbDseLevel-Keep All ve Message Intenrai-Login Timeojt-MaxBcpThneacS-yaocDeliveredTransactions-Polling Internal-QueryThiedut-SkjpEmors-Transactions PerHiistory21-17473647 1OT1300151D5 18W1M通过跟踪令牌方式测试复制延迟时间代理参数优化测试新建日志读取代理参数配置文件，命名为“日志读取代理参数优化参