存储过程仅在从应用程序运行时导致超时

We have runned into a problem with an sp.

我们遇到了sp的问题。

We have a pretty simple sp containing a declared table and a couple of outer joins that in the end returns between 20 and 100 rows.

我们有一个非常简单的sp包含一个声明的表和几个外连接，最后返回20到100行。

Since querying this sp has been giving us poor performance both in production and in testenvironment we recently rewrote it to be more efficient and has tested thouroughly with great performance in our testenvironment.

因为查询这个sp一直给我们在生产和测试环境中的不良表现，我们最近重新编写它以提高效率并且在我们的测试环境中进行了彻底的测试。

We released it to production just to find out that it is still very slow and are causing our .NET 2.0 application to timeout when it is called.

我们将它发布到生产中只是为了发现它仍然很慢并且导致我们的.NET 2.0应用程序在被调用时超时。

We understood nothing and went into Management Studio on the production database and ran the sp there, it executes under 1 sec.

我们什么都不懂，在生产数据库上进入Management Studio并在那里运行sp，它在1秒内执行。

That is, when ran from our application it is extremly slow and causes timeouts, when ran from Management Studio it is very quick and never takes more then a second.

也就是说，当从我们的应用程序运行时，它极其缓慢并导致超时，当从Management Studio运行时它非常快并且从不需要超过一秒钟。

Anyone with good knowledge of SQL Server 2005 that can give us a hint regarding this?

任何对SQL Server 2005有深入了解的人都能给我们一个暗示吗？

5 个解决方案

#1

I think that your problem might be "Parameter sniffing". It is a process when SQL Server's execution environment "sniffs" the sp's parameter values during compilation or recompile to generate faster execution plans. But sometimes it gets a combination of parameters which together with the current data the sp will return makes a really slow sp.

我认为你的问题可能是“参数嗅探”。当SQL Server的执行环境在编译或重新编译期间“嗅探”sp的参数值以生成更快的执行计划时，这是一个过程。但有时它会得到参数的组合，这些参数与sp将返回的当前数据一起构成一个非常慢的sp。

There are a couple of good explanations out there. Search on *. This is one is good: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

那里有几个很好的解释。在*上搜索。这是一个很好的：http：//omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

One possible solution is to create local variables in the sp and set the incoming parameters values to them. Then use only the local variables in the sp.

一种可能的解决方案是在sp中创建局部变量并将传入参数值设置为它们。然后只使用sp中的局部变量。

CREATE PROCEDURE [dbo].spTest
  @FromDate as DATETIME
AS
BEGIN
  DECLARE @FromDate_local as DATETIME
  SET @FromDate_local = '2009-01-01'

  SET @FromDate_local = @FromDate
  ...
  SELECT * FROM TestTbl WHERE FromDate >= @FromDate_local
END

#2

Recompile is a blunt instrument. It's most likely parameter sniffing

重新编译是一种钝器。这很可能是参数嗅探

See this question: Stored Procedure failing on a specific user

请参阅此问题：特定用户的存储过程失败

#3

Thanx for the replies guys, seems as running sp_recompile solved the problem, at least everything has been running smothly since I executed it yesterday afternoon, will keep watching it and see if it stays quick.

对于回复人员来说，似乎正在运行sp_recompile解决了这个问题，至少自从我昨天下午执行它以后，一切都一直在运行，它会继续观察它，看它是否保持快速。

Don't however understand that recompile wasn't made when I changed the content inside the sp?

但是，当我更改sp内部的内容时，不要理解是不是重新编译了吗？

#4

Make sure your production database has up-to-date stats and the indexes are in good condition (if possible consider rebuilding the indexes involved).

确保您的生产数据库具有最新的统计信息，并且索引状况良好（如果可能，请考虑重建所涉及的索引）。

#5

Can you be sure that there is not a deadlock situation occurring? The run from management studio would be isolated where as from the application this might be part of a bigger transaction.

你能确定没有发生死锁的情况吗？管理工作室的运行将被隔离，从应用程序可能是更大的交易的一部分。

#1