Stored procedure causing timeout only when run from application

Question

We have runned into a problem with an 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.

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.

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.

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

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.

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

Answer 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.

There are a couple of good explanations out there. Search on Stackoverflow. This is one is good: 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.

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

Answer 2

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

See this question: Stored Procedure failing on a specific user