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..
我有一个web服务，它从MS-SQL2005 DB调用存储过程。我的web服务在调用我的一个存储过程时超时(这个过程已经在生产中几个月了，没有超时)，所以我尝试在query Analyzer中运行查询，查询分析器也超时了。我决定删除并重新创建存储过程，不修改代码，然后它又开始执行。
Questions:
问题:
Would this typically be an error in the TSQL of my Stored Procedure?
这通常是存储过程的TSQL中的错误吗?
-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
- SQL糟糕的存储过程执行计划性能-参数嗅探
- Parameter Sniffing (or Spoofing) in SQL Server
- SQL服务器中的参数嗅探(或欺骗)
6 个解决方案
#1
3
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.
您是否一直在更新数据库的统计数据?这听起来像是原始的SP使用过时的查询计划。sp_recompile可能会有所帮助，而不是删除/重新创建它。
#2
1
There are a few things you can do to fix/diagnose this.
您可以做一些事情来修复/诊断这个问题。
1) Update your statistics on a regular/daily basis. SQL generates query plans (think optimizes) bases on your statistics. If they get "stale" your stored procedure might not perform as well as it used to. (especially as your database changes/grows)
1)定期/每天更新你的统计数据。SQL根据统计数据生成查询计划(请考虑优化)。如果它们变得“陈旧”，那么存储过程的性能可能不如以前。(特别是当您的数据库更改/增长时)
2) Look a your stored procedure. Are you using temp tables? Do those temp tables have indexes on them? Most of the time you can find the culprit by looking at the stored procedure (or the tables it uses)
2)查看存储过程。您使用临时表吗?这些临时表上有索引吗?大多数情况下，通过查看存储过程(或它使用的表)可以找到罪魁祸首
3) Analyze your procedure while it is "hanging" take a look at your query plan. Are there any missing indexes that would help keep your procedure's query plan from going nuts. (Look for things like table scans, and your other most expensive queries)
3)在“挂起”时分析您的过程，查看查询计划。是否有遗漏的索引可以帮助您的过程的查询计划保持正常。(查找表扫描和其他最昂贵的查询)
It is like finding a name in a phone book, sure reading every name is quick if your phone book only consists of 20 or 30 names. Try doing that with a million names, it is not so fast.
这就像在电话簿里找到一个名字一样，如果你的电话簿只有20或30个名字，你就可以快速地读到每个名字。试着用一百万个名字来做，它不是那么快。
#3
1
This happend to me after moving a few stored procs from development into production, It didn't happen right away, it happened after the production data grew over a couple months time. We had been using Functions to create columns. In some cases there were several function calls for each row. When the data grew so did the function call time.
The original approach was good in a testing environment but failed under a heavy load. Check if there are any Function calls in the proc.
这发生在我将一些存储的procs从开发转移到生产之后，它并没有立即发生，而是发生在生产数据增长了几个月之后。我们一直在使用函数来创建列。在某些情况下，每一行有几个函数调用。当数据增长时，函数调用时间也随之增长。最初的方法在测试环境中很好，但在重负下失败了。检查proc中是否有任何函数调用。
#4
0
I think the table the SP is trying to use is locked by some process. Use "exec sp_who" and "exec sp_lock" to find out what is going on to your tables.
我认为SP试图使用的表被某个进程锁定了。使用“exec sp_who”和“exec sp_lock”来查找表上发生了什么。
#5
0
If it was working quickly, is (with the passage of several months) no longer working quickly, and the code has not been changed, then it seems likely that the underlying data has changed.
如果它工作得很快，is(随着几个月的过去)不再工作得很快，而且代码也没有被修改，那么底层数据很可能已经发生了变化。
- My first guess would be data growth--so much data has been added over the past few months (or past few hours, ya never know) that the query is now bogging down.
- 我的第一个猜测是数据的增长——过去几个月(或者过去几个小时，你永远不知道)增加了这么多的数据，而现在的查询已经开始减少了。
- Alternatively, as CodeByMoonlight implies, the data may have changed so much over time that the original query plan built for the procedure is no longer good (though this assumes that the query plan has not cleared and recompiled at all over a long period of time).
- 另外，正如CodeByMoonlight所暗示的那样，随着时间的推移，数据可能发生了很大的变化，以至于为过程构建的原始查询计划不再有效(尽管这假定查询计划在很长一段时间内没有被清除和重新编译)。
- Similarly Index/Database statistics may also be out of date. Do you have AutoUpdateSatistics on or off for the database?
- 类似的索引/数据库统计数据也可能过时。对于数据库，是否有自动数据处理系统?
Again, this might only help if nothing but the data has changed over time.
同样，只有当数据随时间发生变化时，这才可能有所帮助。
#6
0
Parameter sniffing.
参数嗅探。
Answered a day or 3 ago: "strange SQL server report performance problem related with update statistics"
一两天前回答:“奇怪的SQL server报告与更新统计有关的性能问题”
#1
3
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.
您是否一直在更新数据库的统计数据?这听起来像是原始的SP使用过时的查询计划。sp_recompile可能会有所帮助，而不是删除/重新创建它。
#2
1
There are a few things you can do to fix/diagnose this.
您可以做一些事情来修复/诊断这个问题。
1) Update your statistics on a regular/daily basis. SQL generates query plans (think optimizes) bases on your statistics. If they get "stale" your stored procedure might not perform as well as it used to. (especially as your database changes/grows)
1)定期/每天更新你的统计数据。SQL根据统计数据生成查询计划(请考虑优化)。如果它们变得“陈旧”，那么存储过程的性能可能不如以前。(特别是当您的数据库更改/增长时)
2) Look a your stored procedure. Are you using temp tables? Do those temp tables have indexes on them? Most of the time you can find the culprit by looking at the stored procedure (or the tables it uses)
2)查看存储过程。您使用临时表吗?这些临时表上有索引吗?大多数情况下，通过查看存储过程(或它使用的表)可以找到罪魁祸首
3) Analyze your procedure while it is "hanging" take a look at your query plan. Are there any missing indexes that would help keep your procedure's query plan from going nuts. (Look for things like table scans, and your other most expensive queries)
3)在“挂起”时分析您的过程，查看查询计划。是否有遗漏的索引可以帮助您的过程的查询计划保持正常。(查找表扫描和其他最昂贵的查询)
It is like finding a name in a phone book, sure reading every name is quick if your phone book only consists of 20 or 30 names. Try doing that with a million names, it is not so fast.
这就像在电话簿里找到一个名字一样，如果你的电话簿只有20或30个名字，你就可以快速地读到每个名字。试着用一百万个名字来做，它不是那么快。
#3
1
This happend to me after moving a few stored procs from development into production, It didn't happen right away, it happened after the production data grew over a couple months time. We had been using Functions to create columns. In some cases there were several function calls for each row. When the data grew so did the function call time.
The original approach was good in a testing environment but failed under a heavy load. Check if there are any Function calls in the proc.
这发生在我将一些存储的procs从开发转移到生产之后，它并没有立即发生，而是发生在生产数据增长了几个月之后。我们一直在使用函数来创建列。在某些情况下，每一行有几个函数调用。当数据增长时，函数调用时间也随之增长。最初的方法在测试环境中很好，但在重负下失败了。检查proc中是否有任何函数调用。
#4
0
I think the table the SP is trying to use is locked by some process. Use "exec sp_who" and "exec sp_lock" to find out what is going on to your tables.
我认为SP试图使用的表被某个进程锁定了。使用“exec sp_who”和“exec sp_lock”来查找表上发生了什么。
#5
0
If it was working quickly, is (with the passage of several months) no longer working quickly, and the code has not been changed, then it seems likely that the underlying data has changed.
如果它工作得很快，is(随着几个月的过去)不再工作得很快，而且代码也没有被修改，那么底层数据很可能已经发生了变化。
- My first guess would be data growth--so much data has been added over the past few months (or past few hours, ya never know) that the query is now bogging down.
- 我的第一个猜测是数据的增长——过去几个月(或者过去几个小时，你永远不知道)增加了这么多的数据，而现在的查询已经开始减少了。
- Alternatively, as CodeByMoonlight implies, the data may have changed so much over time that the original query plan built for the procedure is no longer good (though this assumes that the query plan has not cleared and recompiled at all over a long period of time).
- 另外，正如CodeByMoonlight所暗示的那样，随着时间的推移，数据可能发生了很大的变化，以至于为过程构建的原始查询计划不再有效(尽管这假定查询计划在很长一段时间内没有被清除和重新编译)。
- Similarly Index/Database statistics may also be out of date. Do you have AutoUpdateSatistics on or off for the database?
- 类似的索引/数据库统计数据也可能过时。对于数据库，是否有自动数据处理系统?
Again, this might only help if nothing but the data has changed over time.
同样，只有当数据随时间发生变化时，这才可能有所帮助。
#6
0
Parameter sniffing.
参数嗅探。
Answered a day or 3 ago: "strange SQL server report performance problem related with update statistics"
一两天前回答:“奇怪的SQL server报告与更新统计有关的性能问题”