存储过程似乎没有解释就挂了 [英] Stored procedure hangs seemingly without explanation
问题描述
我们有一个存储过程，它在 10 分钟前运行良好，然后在您调用它后挂起.
we have a stored procedure that ran fine until 10 minutes ago and then it just hangs after you call it.
观察:
- 将代码复制到查询窗口，1秒出查询结果
- SP 需要 > 2.5 分钟才能取消
- 活动监视器显示它没有被任何东西阻止，它只是在执行 SELECT.
- 在 SP 上运行 sp_recompile 没有帮助
- 删除并重新创建 SP 无济于事
- 将 LOCK_TIMEOUT 设置为 1 秒无济于事
还能发生什么?
UPDATE:我猜这与参数嗅探有关.我使用 Adam Machanic 的例程来找出挂起的子查询.由于 Martin Smith 的提示，我发现查询计划有问题.我了解了 EXEC ... WITH RECOMPILE、OPTION(RECOMPILE) 用于 SP 内的子查询，以及 OPTION (OPTIMIZE FOR (@parameter = 1)) 以攻击参数嗅探.我仍然不知道在这个特殊情况下出了什么问题，但我从这场战斗中走出来，经验丰富，装备更好.我知道下次该怎么做.重点来了！
UPDATE: I'm guessing it had to do with parameter sniffing. I used Adam Machanic's routine to find out which subquery was hanging. I found things wrong with the query plan thanks to the hint by Martin Smith. I learned about EXEC ... WITH RECOMPILE, OPTION(RECOMPILE) for subqueries within the SP, and OPTION (OPTIMIZE FOR (@parameter = 1)) in order to attack parameter sniffing. I still don't know what was wrong in this particular case but I came out of this battle seasoned and much better armed. I know what to do next time. So here's the points!
推荐答案
在查询运行时运行 Adam Machanic 出色的 sp_WhoIsActive 存储过程.它将为您提供等待信息 - 意思是，存储过程正在等待什么 - 以及诸如执行计划之类的信息:
Run Adam Machanic's excellent sp_WhoIsActive stored proc while your query is running. It'll give you the wait information - meaning, what the stored proc is waiting on - plus things like the execution plan:
http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/
如果您想要外部命令(如调用存储过程的全文)，请使用@get_outer_command = 1 参数.
If you want the outer command (like a calling stored procedure's full text), use the @get_outer_command = 1 parameter as well.
这篇关于存储过程似乎没有解释就挂了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
