SQL Server Service Broker的陷阱

目录

介绍

背景

SQLDependency 要求

案例介绍

什么是构建

进一步调查

提醒

看法

---

介绍

在C# SQLDependency对象中使用查询并将该查询设置为数据库中的事件时，很容易误用SQL Server中的服务代理。当查询从多个表请求数据时，数据库最终可能会陷入数据锁定状态。当您遇到此问题时，您将从BING和Google获得0个答案来解决这个问题。

在微软官方文档中，为查询设置了要求。只要保持查询简单，就很容易在数据库中造成灾难而不知情。特别是对于懒惰的开发人员来说，这是一个学习曲线。此外，对于微软来说，它对我们的情况做出了快速反应，但在两周后似乎失去了兴趣，因为我表示我的发现并非不可想象。如何与服务代理合作而不会遇到麻烦是一个感知问题。

还有更多关于服务代理和问题的文章，但没有一篇指出原因：

很好的例子：

• 祸不单行
• 神秘的通知

在本文中，给出了一个修复程序。

背景

在我们的生产环境中，我们有时会出现很大的性能下降。去年，它已成为日常问题。简单的更新查询最多需要2分钟。该更新的查询计划显示了一个充满并行性的巨大计划。查询如下所示：

UPDATE table SET FIELD = VALUE Where ID = IdValue;

因此，并行性是完全出乎意料的行为。重新启动数据库后，正常的查询计划再次出现。一天之内，问题又回来了。

SQLDependency 要求

Microsoft在此处提供了你在SQLDependency中使用的查询的下一个要求。

在支持的SELECT语句的标题下，未提及最重要的规则：语句不得包含JOIN。我将解释为什么我认为您不应该在查询中使用超过1个表。

案例介绍

我们的数据库（近100个表）有6个代表工作流程的重要表格。这些表可以通过主键ID字段联接。这6个表包含47个最需要的列，以便能够了解客户项目中涉及的内容和完成的工作。测量完成后，机组人员必须拒绝或接受测量。该复选框是服务代理应响应的事件。在该事件之后，其他流程将在我们的工作流程中启动。

什么是构建

在设置SQLDependency的服务中，开发人员认为在查询中询问所有这47个字段是明智的。由于不禁止在这些查询中使用JOIN，并且查询非常简单，因此没有给出任何错误。用于测量的应用程序解决了所有问题。更好的是，我的电话响个不停。

我们最初认为测量系统有问题，因为我们在那里看到了问题。额外的问题：我们永远无法分辨6个表中的哪一个必须处理update查询中的并行性问题。它总是更新。给定时间后，我们决定停止具有依赖项的服务，每个人都可以再次工作。

那是我们调查查询的那一刻。我们真正需要从活动中了解什么？

我们的查询如下所示：

SELECT tbl1.Field1, tbl1.Field2, tbl1.Field3, _
       tbl1.Field4, tbl1.Field5, tbl1.Field6, tbl1.Field7,
tbl2.Field1, tbl2.Field2, tbl2.Field3, tbl2.Field4, _
             tbl2.Field5, tbl2.Field6, tbl2.Field7,
tbl3.Field1, tbl3.Field2, tbl3.Field3, tbl3.Field4, _
             tbl3.Field5, tbl3.Field6, tbl3.Field7,
tbl4.Field1, tbl4.Field2, tbl4.Field3, tbl4.Field4, _
             tbl4.Field5, tbl4.Field6, tbl4.Field7,
tbl5.Field1, tbl5.Field2, tbl5.Field3, tbl5.Field4, _
             tbl5.Field5, tbl5.Field6, tbl5.Field7,
tbl6.Field1, tbl6.Field2, tbl6.Field3, tbl6.Field4, _
             tbl6.Field5, tbl6.Field6, tbl6.Field7, tbl6.lastModified
FROM dbo.Table1 as tbl1
Join dbo.Table2 as tbl2 on tbl2.table1Id = tbl1.Id
Join dbo.Table3 as tbl3 on tbl3.table2Id = tbl2.Id
Join dbo.Table4 as tbl4 on tbl4.table3Id = tbl3.Id
Join dbo.Table5 as tbl5 on tbl5.table4Id = tbl4.Id
Join dbo.Table6 as tbl6 on tbl6.table5Id = tbl5.Id
Where tbl6.lastModified > @lastModified
AND tbl6.IsAccepted IS NOT NULL

根据微软的说法，这是对SqlDependency对象的有效查询。

进一步调查

上述查询的最大问题是没有人设置字段IsAccepted。当一个拥有400人的组织向所有这6个表添加数据行时，元组的集合会变得很大，因为您永远等待IsAccepted设置为false或true。因此，重新启动SQL服务器后，大约需要一个小时才能再次出现相同的问题。

提醒

首先，您必须知道SQLDependency在服务代理中设置为最多2分钟。如果事件发生较早，则该事件会通知应用程序它已发生。如果没有，则服务代理将再次设置事件2分钟。

服务代理将您的查询设置为数据库中的索引视图，模式系统！这是微软的某个人可以告诉我的。在这里，当连接SQLDependency中的多个表时，问题就开始了。我们使用了6个表中的47个字段，而我们只需要知道测量被接受或拒绝后测量表的涉及Id。

因此，当在索引视图中创建 47 个字段（优先级较低 - 仍然是优先级）时，它会对您的数据库产生各种影响。我们在查询中发现的并行性显示了一些奇怪的信息。UPDATE

<OutputList>
    <ColumnReference Database="[TDMS-TEST]" Schema="[sys]" 
    Table="[query_notification_1626853258]" Column="id" />

您应该在数据库中将结尾的数字读取为object_id。通过下一个查询，您可以找到以下索引：

SELECT IndexName = i.Name, ColName = c.Name,  *
FROM sys.indexes i
     INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id
                                        AND ic.index_id = i.index_id
     INNER JOIN sys.columns c ON c.object_id = ic.object_id
                                 AND c.column_id = ic.column_id
WHERE 1 = 1
AND I.OBJECT_ID = 1626853258
ORDER BY i.Name;
You cannot drop these indexes. The service broker can!

但是，当要求将6个表中的47个字段作为索引视图进行监视时，您的数据库需要每2分钟创建和删除索引。继续检查6个表，其中1个表是否有更改。

看法

我们真正需要从服务代理那里得到什么？在一个表中，一个位域相对于上一次修改的值从null变为非null的事件。那么为什么我们需要其他45个字段呢？只是在生产数据库中性能下降。

在我们的例子中，我们希望每个人都提供大量数据。最好将查询分为两部分。

第1部分是依赖项查询，它反映了要通知的事件。第2部分是满足数据需求所需的查询。

例：

DependencyQuery就像这样：

SELECT tbl6.Id, tbl6.lastModified
FROM dbo.Table6 as tbl6
WHERE tbl6.LastModified > @LastModified
AND tbl.IsAccepted IS NOT NULL

工作流的查询为：

SELECT tbl1.Field1, tbl1.Field2, tbl1.Field3, tbl1.Field4, _
       tbl1.Field5, tbl1.Field6, tbl1.Field7,
tbl2.Field1, tbl2.Field2, tbl2.Field3, tbl2.Field4, _
             tbl2.Field5, tbl2.Field6, tbl2.Field7,
tbl3.Field1, tbl3.Field2, tbl3.Field3, tbl3.Field4, _
             tbl3.Field5, tbl3.Field6, tbl3.Field7,
tbl4.Field1, tbl4.Field2, tbl4.Field3, tbl4.Field4, _
             tbl4.Field5, tbl4.Field6, tbl4.Field7,
tbl5.Field1, tbl5.Field2, tbl5.Field3, tbl5.Field4, _
             tbl5.Field5, tbl5.Field6, tbl5.Field7,
tbl6.Field1, tbl6.Field2, tbl6.Field3, tbl6.Field4, _
             tbl6.Field5, tbl6.Field6, tbl6.Field7
FROM dbo.Table1 as tbl1
Join dbo.Table2 as tbl2 on tbl2.table1Id = tbl1.Id
Join dbo.Table3 as tbl3 on tbl3.table2Id = tbl2.Id
Join dbo.Table4 as tbl4 on tbl4.table3Id = tbl3.Id
Join dbo.Table5 as tbl5 on tbl5.table4Id = tbl4.Id
Join dbo.Table6 as tbl6 on tbl6.table5Id = tbl5.Id
Where tbl6.Id = @Id

因此，在C#类中，我们维护在服务代理中设置为事件的表的lastmodified值（以防止内存中有大量记录）。

结果（可能超过1行记录，具体取决于数据库的使用范围）后，我们将Id值设置为第二个查询的参数，该查询直接调用数据库。

https://www.codeproject.com/Articles/5344594/Pitfalls-of-the-SQL-Server-Service-Broker