MS SQL Server 2005 - 存储过程“自动中断”

Question

一个客户报告说执行存储过程时出现了反复发生的非常奇怪的行为。他们有一段代码，从易变数据集的缓存转置中运行。编写了一个存储过程，在需要重新处理数据集时重新处理它，条件为：

1. 自上次重新处理以来数据集已更改。 2. 数据集未更改超过5分钟。

（第二个条件可防止在更改期间大量重复计算。）

这个方法运行了几周，SP完成重新处理的时间为1-2秒，并且只在需要时执行。然后......

1. 存储过程突然“停止工作”（仅保持运行而不返回）。 2. 我们以微妙的方式更改了存储过程，然后它又可以工作了。 3. 几天后它再次停止工作。 4. 然后有人说：“我们以前见过这种情况，只需重新编译SP。” 5. 在没有更改代码的情况下，我们重新编译了SP，然后它就可以工作了。 6. 几天后它再次停止工作。

这种情况已经重复很多次了。存储过程突然“停止工作”，永远不会返回，客户端超时。（我们尝试通过管理工具运行它，并在15分钟后取消查询。）

但是每次重新编译SP，它都会突然重新工作。

我还没有尝试在适当的EXEC语句上使用WITH RECOMPILE，但是我不想这样做。它每小时被调用数百次，通常什么也不做（它只重新处理数据几次每天）。如果可能，我希望避免重新编译相对复杂的SP的开销，以“避免不应该发生的事情......”

有人以前经历过这种情况吗？有人有任何克服它的建议吗？

编辑：

伪代码如下：

1. 从table_x读取"a" 2. 从table_x读取"b" 3. 如果(a

选择不太好看，但是在内联执行时，它们立即执行。包括当SP拒绝完成时。分析器显示它是SP“停滞”的插入操作。

SP没有参数，并且sp_lock未显示任何阻止进程的内容。

- MatBailie

---

听起来像是您有一个未被提交或回滚的事务。没有查看代码很难确定。 - Juliet

我们的客户已将所有IT外包给IBM。他们只在感觉需要时打补丁。 - MatBailie

太巧合了，“重新运行ALTER语句后立即就可以正常工作”，而且sp_lock没有显示任何相关内容。（好吧，sp_lock3是从某个网站复制的） - MatBailie

它肯定缓存了一个糟糕的执行计划。 - Cade Roux

能否提供包含三个联合查询的 INSERT 语句，这将非常有帮助。 - RBarryYoung

Answer 1

这是参数嗅探的痕迹。第一步是尝试重新编译，尽管在2005年它并不总是按你想要的方式工作。

更新： 无论如何，我会尝试使用语句级别的重新编译INSERT，因为这可能是一个统计问题（哦，是的，请检查自动统计更新是否打开）。

如果这似乎不符合参数嗅探，则比较从正常运行到永久运行时的实际查询计划（如果无法获取实际计划，则使用预估计划，但实际计划更好）。您要查看计划是否发生变化。

- RBarryYoung

---

很遗憾，没有参数可用。唯一的变量是正在处理的源数据集的内容。 - MatBailie

Answer 2

我完全同意参数嗅探诊断的观点。如果您在存储过程中有输入参数是变化的（甚至如果它们没有变化）-请确保使用本地变量对其进行屏蔽，并在存储过程中使用该本地变量。

如果数据集正在更改但查询计划不再有效，则可以使用WITH RECOMPILE。

在SQL Server 2008中，您可以使用OPTIMIZE FOR UNKNOWN功能。

此外，如果您的处理过程涉及填充表格，然后在另一个操作中使用该表格，建议将该处理过程拆分为单独的存储过程，并逐个调用它们WITH RECOMPILE。当您填充表格，然后使用该表格的结果执行操作时，我认为在处理过程开始时生成的计划有时非常差（以至于无法完成）。因为在初始计划的时间点上，该表格与初始插入时大不相同。

- Cade Roux

---

没有参数，数据集中的更改是微妙的，不显著。执行计划根本不需要改变。这让人感到困惑。就好像执行计划在不应该改变的时候发生了变化，就像是损坏的统计数据一样。但我们也检查过了！叹气 - MatBailie

我已经添加了一些关于使用多个表进行中间结果的长时间处理的注释。 - Cade Roux

在这种情况下，没有中间步骤。只需进行DELETE操作，然后进行INSERT操作，伪代码中提到的UPDATE仅将GetDate()输入元数据控制表中。 - MatBailie

Answer 3

正如其他人所说，数据或源表统计信息的更改方式会导致缓存的查询计划过期。

WITH RECOMPILE 可能是最快的解决方法 - 在轻率地排除它之前，请使用 SET STATISTICS TIME ON 查找重新编译成本。

如果仍然不是可接受的解决方案，则最好的选择可能是尝试重构插入语句。

您没有说明在插入语句中是否使用了 UNION 或 UNION ALL。我曾经看到过使用 UNION 的 INSERT INTO 产生一些奇怪的查询计划，特别是在 SQL 2005 的 SP2 版本之前。

• Raj建议使用SELECT INTO删除和重新创建目标表是一种方法。

• 您还可以尝试将三个源查询中的每一个选择到它们自己的临时表中，然后在插入时将这些临时表UNION在一起。

• 或者，您可以尝试这些建议的组合 - 使用SELECT INTO将联合结果放入临时表中，然后从那里插入目标表。

我已经看到所有这些方法解决了类似情况下的性能问题；测试将揭示哪种方法对您拥有的数据提供最佳结果。

- Ed Harper

Answer 4

如果你正在执行这些步骤：

DELETE table_y
INSERT INTO table_y <3 selects unioned together>

你可能想尝试这个

DROP TABLE table_y
SELECT INTO table_y <3 selects unioned together>

- Raj

---

我认为你的意思是使用TRUNCATE TABLE而不是DROP TABLE。此外，调用存储过程的安全上下文无法TRUNCATE表，只能使用DELETE。另外，问题出在INSERT操作上，而不是清空数据。 - MatBailie

然而，数据在批处理过程中发生变化的事实意味着，最初选择的执行计划可能是不合适的，因此我提到了逐步获得更好的执行计划的观点。 - Cade Roux

Answer 5

正如其他人所说，这很可能是一个未提交的事务。

我的最佳猜测：

您需要确保table_y可以完全且快速地被删除。

如果有其他存储过程或外部代码在此表上持有事务，您可能会永远等待。（它们可能会出错并永远不关闭事务）

另一个注意点：如果可能，请尝试使用truncate。它比没有where子句的delete使用更少的资源：

truncate table table_y

此外，一旦在您自己的事务中发生错误，它将导致所有后续调用（显然每5分钟一次）“挂起”，除非您处理错误：

begin tran
begin try
 -- do normal stuff
end try
begin catch
 rollback
end catch
commit

第一个错误会给你关于实际错误的信息。在后续测试中看到它挂起只是次要影响。

- Jeff Meatball Yang

Answer 6

显然，通过重新编译存储过程来改变导致锁定的情况。

尝试按照此处或此处所述记录您的SP的进度。

- devio

Answer 7

我同意上面在评论中给出的答案，这似乎是一个未关闭的事务，特别是如果您仍然可以从查询分析器运行选择语句。

这听起来非常像有一个待删除的table_y的打开事务，此时插入无法发生。

当你的SP锁定时，你能够将数据插入到table_y中吗？

- Paddy

---

唯一写入该表的代码是所讨论的存储过程。由于代码是BEGIN TRANSACTION，DELETE <table>，INSERT INTO <table>，UPDATE <meta_data table>，COMMIT TRANSACTION，我看不出可能出现所描述的情况。特别是因为sp_lock没有显示任何锁争用。 - MatBailie

只是出于兴趣 - 如果在第一个过程仍在执行时再次运行此过程会发生什么？ - Paddy

Answer 8

你有索引维护工作吗？

你的统计信息是否最新？一种方法是检查大变化的估计和实际查询计划。

- Mitch Wheat

---

IBM拥有我们所有客户的SQL SERVER实例的DBA角色。索引是在每晚的过程中维护的。当它正在工作时，我将不得不检查实际计划与估计计划，然后在它“破裂”时检查估计计划。当它破裂时，我无法获取实际计划，因为它似乎永远不会返回。而且，在现场系统上，我们不能让一个锁定的表等待数小时才能返回 :) - MatBailie