MS SQL Server 2005 - 存储过程“自发中断" [英] MS SQL Server 2005 - Stored Procedure "Spontaneously Breaks"

问题描述

客户端在执行存储过程时报告了重复的非常奇怪的行为实例.

A client has reported repeated instances of Very strange behaviour when executing a stored procedure.

他们的代码可以运行易失性数据集的缓存转置.如果出现以下情况，将写入存储过程以按需重新处理数据集:
1. 数据集自上次重新处理后发生了变化
2.数据集已经5分钟没有变化

They have code which runs off a cached transposition of a volatile dataset. A stored proc was written to reprocess the dataset on demand if:
1. The dataset had changed since the last reprocessing
2. The datset has been unchanged for 5 minutes

(第二个条件在变化期间停止大量重复计算.)

(The second condition stops massive repeated recalculation during times of change.)

这在几周内运行良好，SP 需要 1-2 秒才能完成重新处理，并且仅在需要时才这样做.然后……

This worked fine for a couple of weeks, the SP was taking 1-2 seconds to complete the re-processing, and it only did it when required. Then...

• SP 突然停止工作"(它一直在运行，再也没有回来)
• 我们以一种微妙的方式更改了 SP，它再次起作用了
• 几天后它再次停止工作
• 然后有人说我们以前见过这个，只需重新编译 SP"
• 在没有更改代码的情况下，我们重新编译了 SP，并且成功了
• 几天后它再次停止工作

这已经重复了很多很多次.SP 突然停止工作"，永不返回，客户端超时.(我们尝试通过管理工作室运行它并在 15 分钟后取消查询.)

This has now repeated many, many times. The SP suddenly "stops working", never returning and the client times out. (We tried running it through management studio and cancelled the query after 15 minutes.)

然而，每次我们重新编译 SP 时，它都会突然再次起作用.

Yet every time we recompile the SP, it suddenly works again.

我还没有在适当的 EXEC 语句上尝试过 WITH RECOMPILE，但我并不特别想这样做.它每小时被调用数百次，通常什么都不做(它每天只重新处理数据几次).如果可能的话，我想避免重新编译相对复杂的 SP 的开销，只是为了避免不应该"发生的事情......

I haven't yet tried WITH RECOMPILE on the appropriate EXEC statments, but I don't particularly want to do that any way. It gets called hundred of times an hour and normally does Nothing (It only reprocesses the data a few times a day). If possible I want to avoid the overhead of recompiling what is a relatively complicated SP "just to avoid something which "shouldn't" happen...

• 以前有人遇到过这种情况吗?
• 有人对如何克服它有任何建议吗?

干杯，
民主党.

Cheers,
Dems.

伪代码如下:

• 从 table_x 中读取a"
• 从 table_x 读取b"
• 如果 (a
• 开始交易
• 删除 table_y
• INSERT INTO table_y <3 选择联合在一起>
• 更新 table_x
• 提交交易

选择不漂亮"，但是当内嵌执行时，它们很快就会执行.包括当 SP 拒绝完成时.并且探查器显示它是 SP停止"的 INSERT

The selects are "not pretty", but when executed in-line they execute in no time. Including when the SP refuses to complete. And the profiler shows it is the INSERT at which the SP "stalls"

SP 没有参数，sp_lock 显示没有任何阻塞进程.

There are no parameters to the SP, and sp_lock shows nothing blocking the process.

推荐答案

正如其他人所说，数据或源表统计信息的变化方式导致缓存查询计划失效.

As others have said, something about the way the data or the source table statistics are changing is causing the cached query plan to go stale.

WITH RECOMPILE 可能是最快的解决方法 - 使用 SET STATISTICS TIME ON 找出重新编译的实际成本，然后再将其解散.

WITH RECOMPILE will probably be the quickest fix - use SET STATISTICS TIME ON to find out what the recompilation cost actually is before dismissing it out of hand.

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

If that's still not an acceptable solution, the best option is probably to try to refactor the insert statement.

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

You don't say whether you're using UNION or UNION ALL in your insert statement. I've seen INSERT INTO with UNION produce some bizarre query plans, particularly on pre-SP2 versions of SQL 2005.

• Raj 建议放弃和重新创建目标表SELECT INTO 是一种方法.

您也可以尝试选择每个三个源查询成自己的临时表，然后 UNION 那些临时表一起插入.

You could also try selecting each of the three source queries into their own temporary table, then UNION those temp tables together in the insert.

或者，你可以尝试一个结合这些建议——将联合的结果放入一个带有 SELECT INTO 的临时表，然后从中插入目标表.

Alternatively, you could try a combination of these suggestions - put the results of the union into a temporary table with SELECT INTO, then insert from that into the target table.

我已经看到所有这些方法都解决了类似场景中的性能问题；测试将揭示使用您拥有的数据哪个能提供最佳结果.

I've seen all of these approaches resolve performance problems in similar scenarios; testing will reveal which gives the best results with the data you have.

这篇关于MS SQL Server 2005 - 存储过程“自发中断"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！