主页 / dba / 问题 / 95025
Accepted
Worldexe
Asked: 2015-03-12 14:26:59 +0800 CST

SQL Server 2012 合并复制在同步期间阻止其他插入/更新

  • 772

我通过 Pull 方法在两个 SQL Server 2012 数据库之间进行了合并复制设置。

有几个合并复制会影响不同的表集（实际上 3 组 2-5 个表）。没有外键将它们链接到 DB 中的另一个表之间或链接到另一个表。

订阅者和发布者都在更改复制表中的数据。通常，发布者每天 12 小时有超过 800k 的插入，订阅者大约有 300k；几乎没有更新。

此外，我启用了简单的过滤 -bit NULL检查几乎所有合并表的一列是否为 NULL；过滤器中没有joins。主数据库在这些表中有大约 80G 的数据，由于过滤，订阅者有大约 30G。所有过滤列都有索引。表按原样复制，没有选择性列。

大多数表的主键范围增加到 10M（以确保它们不会经常被替换）。

模式复制已启用，但不会发生模式更改。

合并复制保留期设置为 2 天（导致 3 天的存储数据 -MSmerge_contents表中约 300 万行）。

计划每 15 分钟启动一次的复制作业。

问题是 - 有时，当同步作业启动时，它会阻止此数据库中所有合并复制中所有表的所有插入/更新。它似乎只在复制过程结束时发生，无论同步的行数如何（对于某些运行，可能只有 1-2 个更改/添加的行），并且可以持续 5-10 分钟，这是不可接受的。

我使用 SQL Server Profiler 跟踪锁定时正在执行的程序并捕获阻塞报告并手动启动它（这似乎是安全的）：

<blocked-process-report monitorLoop="201058">
 <blocked-process>
  <process id="process38d8dd0c8" taskpriority="0" logused="1268" waitresource="OBJECT: 5:690456609:0 " waittime="5002" ownerId="4004024182" transactionname="UPDATE" lasttranstarted="2015-03-11T16:08:39.890" XDES="0x1b253649e8" lockMode="IX" schedulerid="1" kpid="5132" status="suspended" spid="84" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-03-11T16:08:39.880" lastbatchcompleted="2015-03-11T16:08:39.880" lastattention="2015-03-11T15:29:13.240" hostname="COMP-177" hostpid="2976" loginname="user" isolationlevel="read committed (2)" xactid="4004024182" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame line="46" stmtstart="4398" stmtend="4936" sqlhandle="0x030005008910305b49b9150057a4000000000000000000000000000000000000000000000000000000000000"/>
    <frame line="1" stmtstart="30" sqlhandle="0x020000003f32c4168d189e55398799d8e66489e031b7fa8b0000000000000000000000000000000000000000"/>
    <frame line="1" stmtstart="30" sqlhandle="0x02000000538a8c182d9ff435ad6897538e470878728dfd940000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
set nocount on;update [replicated_table] set CHECK_CI=&apos;1456&apos;,DATE_CHECK_CI=getdate(),DATE_MODIFY=getdate(),where R_ID=&apos;284598973&apos;
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="running" spid="150" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2015-03-11T16:08:39.347" lastbatchcompleted="2015-03-11T16:08:21.833" lastattention="1900-01-01T00:00:00.833" clientapp="Microsoft SQL Server Management Studio - Query" hostname="COMP-120" hostpid="9460" loginname="userp" isolationlevel="read committed (2)" xactid="4004019824" currentdb="5" lockTimeout="4294967295" clientoption1="673319008" clientoption2="390168">
   <executionStack>
    <frame line="366" stmtstart="32078" stmtend="32458" sqlhandle="0x0300ff7fa885d0f933812f012ba3000001000000000000000000000000000000000000000000000000000000"/>
    <frame line="1" sqlhandle="0x010005002698da17601c849b1d00000000000000000000000000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
exec sp_MSmakegeneration   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

sys.sp_MSmakegeneration执行会阻止所有其他活动。据我所知 - 此过程正在生成要复制的行列表。我可以理解它可以运行一段时间 - 但为什么它会阻止所有启用合并的表上的其他插入/更新？同步哪个发布并不重要 - 所有启用合并的表（即使来自此数据库中的另一个发布）也停止工作。

等待对象waitresource="OBJECT: 5:690456609:0 "是MSmerge_genhistory表。所以，它在这张桌子上以某种方式阻塞，但为什么呢？

我试图捕获该过程的实际执行计划——我得到了 14k 行 XML，但在那里没有发现任何可疑之处（没有明显的表扫描或其他繁重的活动）。

复制设置大部分是默认设置，除了generation_leveling_threshold在问题调查期间选项增加到 10000。它没有帮助。

SQL Server 2012 v 11.0.5058.0

默认隔离级别设置为 READ COMMITTED SNAPSHOT；这些插入/更新只是普通的插入/更新，不会更改隔离级别或启动显式事务。

我可以在工作日后成功同步服务器（当没有人接触数据库时），它在 20-30 分钟内运行正常（一次传输全天数据），但我需要持续同步（虽然延迟 15-20 分钟是可以的）。

我用谷歌搜索了这个问题 - 由于大量更改的数据（从 1M 行开始），有很多人的合并复制卡住了，但与我的问题没有任何相似之处。

sql-server-2012 merge-replication

1 个回答

  1. Best Answer

    我个人不建议在这种情况下设置合并复制。但是，由于我没有办法（除了让这项工作或开发我自己的复制解决方案），我花了两周的时间进行实验并找到了解决方案。

    1. 禁用发布属性 -> 订阅选项 -> 过滤窗口上的预计算分区选项。这使得sp_MSmakegeneration只锁定它当前正在处理的特定表，而不是所有受合并复制影响的表。如果您有任何行过滤，此选项将自动启用。但实际上，它只是为了加速参数化过滤器（不是我的情况）。注意：关闭此功能将使订阅无效；您必须使用新快照重新初始化它们。
    2. 使用exec dbo.sp_MSmakegeneration命令创建简单的作业，并每 1 分钟安排一次。通常，它作为同步过程的一部分调用以枚举本地更改。但是，如果您跳过调用它 10-15 分钟（在我的情况下这意味着 20-30k 更改/插入的行），下一次调用它的同步将阻止所有客户端插入/更新到复制表中；此外，您可能会在执行过程中遇到死锁，导致在服务器负载减少之前无法成功运行它。当然，这取决于您的特定表索引和使用情况。
    3. 安排您的同步作业频繁运行。我个人将其设置为每 3 分钟运行一次。这将同步延迟减少到 5-10 分钟。严格来说，这不是强制性的，但同步大量更改本身就是一个相当大的负载，因此服务器更容易同步较小的部分。

    使用它，我能够成功同步 3 台服务器，每个工作日每分钟插入/更新 3-10k 次，没有重大延迟。

    • 1

相关问题