存储过程超时.. 删除，然后创建并再次启动? [英] Stored Procedure Timing out.. Drop, then Create and it's up again?
问题描述
我有一个从 MS-SQL2005 数据库调用存储过程的网络服务.我的 Web 服务在调用我拥有的其中一个存储过程时超时(这已经在生产中使用了几个月没有超时)，所以我尝试在查询分析器中运行查询，但它也超时了.我决定删除并重新创建存储过程而不更改代码，然后它再次开始执行..
问题:
这通常是我的存储过程的 TSQL 中的错误吗?
-或-
有没有人看到这个，发现是存储过程的编译出了问题?
当然，也欢迎对此提出任何其他见解.
类似:
您是否一直在更新有关数据库的统计信息?这听起来像是原始 SP 使用的是过时的查询计划.sp_重新编译 可能有所帮助，而不是删除/重新创建它.>
I have a web-service that calls a stored procedure from a MS-SQL2005 DB. My Web-Service was timing out on a call to one of the stored procedures I have (this has been in production for a couple of months with no timeouts), so I tried running the query in Query Analyzer which also timed out. I decided to drop and recreate the stored procedure with no changes to the code and it started performing again..
Questions:
Would this typically be an error in the TSQL of my Stored Procedure?
-Or-
Has anyone seen this and found that it is caused by some problem with the compilation of the Stored Procedure?
Also, of course, any other insights on this are welcome as well.
Similar:
- SQL poor stored procedure execution plan performance - parameter sniffing
- Parameter Sniffing (or Spoofing) in SQL Server
Have you been updating your statistics on the database? This sounds like the original SP was using an out-of-date query plan. sp_recompile might have helped rather than dropping/recreating it.
这篇关于存储过程超时.. 删除，然后创建并再次启动?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
