locked
Stored procedure times out sporadically RRS feed

  • Question

  • Hi,

    I have a stored procedure that times out sporadically when called with ADO.NET. Running the same stored procedure call in Query Analyzer takes about 10-15 sec to execute.

     

    Response time up to 30 sec is totally acceptable in our case as we are working with large amount of data.  I have however tried setting the Command.Timeout property to 3 minutes but the call still hangs.

     

    Debugging the application the first brake point is hit in the stored procedure where it hangs. When the sp is recompiled and the application run again it is executed as expected but that is totally sporadic.  

     

    We are using ASP.NET 3.5,

    SQL Server 2005/2008  (same behaviour on both versions)

    Windows 2003 server

     

    Any ideas?


    /Ershad

    Saturday, November 14, 2009 11:45 AM

Answers

All replies

  • Most likely you get different execution plans from your application and from SSMS (and the plan that your application is using is not efficient for some parameters). See if any of the following methods will help:
    http://pratchev.blogspot.com/2007/08/parameter-sniffing.html
    Plamen Ratchev
    • Marked as answer by ershad Thursday, November 19, 2009 9:24 AM
    Saturday, November 14, 2009 1:15 PM
  • Also, in addition to what Plamen have suggested problems like this can be identified using SQL Profiler or Server Side Trace Use RPC Completed and SQL BatchCompleted event and look for duration, read and write information.

    Saturday, November 14, 2009 1:19 PM
  • Thanks seem very interesting. I’ll try the suggested solutions on Monday when I’m back at work- If any of them works I’ll post an update on this thread.

    /Ershad

    Saturday, November 14, 2009 1:50 PM
  • You also might want to look at the problem from another angle.  Typically application timeouts occur because other processes block the application process beyond the timeout threshold.  You can use sp_who2 or query sys.sysprocesses to get the locking information.  The problem is either a bad query plan, as Plamen suggested, or a blocking process.
    http://jahaines.blogspot.com/
    Sunday, November 15, 2009 4:43 AM
  • I have tried all different sorts of things to solve this problem. The solution was however according to one of the methods stated on Plamen Patchevs’s weblog: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html.

     

    "
    Query hint RECOMPILE


    SQL Server 2005 offers the new query hint RECOMPILE which will force recompilation of the individual query. This method is better that the prior method because recompilation will affect only one statement and all other queries in the stored procedure will not be recompiled.

    CREATE PROCEDURE GetCustomerOrders

     @customerid NCHAR(5)

    AS

    BEGIN

     

        SELECT orderid, customerid, orderdate, shippeddate

        FROM Orders

        WHERE customerid = @customerid

        OPTION (RECOMPILE);

     

    END

    "
     

    I’m not sure why this solved the problem but it did. In my world recompiling the statement on each call should increase the response time?

     

    Thursday, November 19, 2009 9:36 AM
  • Thanks for a excellent blog post but can you explain why using the OPTION (RECOMPILE); gives a quicker response time? Shouldn’t the respons time be quicker when an existing cached query plan is used?

    Thursday, November 19, 2009 9:43 AM
  • If you read the beginning of the blog post it explains how parameter sniffing works and the cases that may affect you. In essence the first invocation of the procedure generates a plan based on the parameters used. That may be very efficient plan for most cases if the first set of parameters is very typical. But if you use the same execution plan with another set of parameters it may result in very poor performance (for example, parameters that result in 1 row result set can benefit from one plan, parameters that result in 100,000 row result set can benefit from completely different plan).

    When you use OPTION (RECOMPILE) it guarantees that a fresh plan that is optimal for the current set of parameters is generated and used. That comes with the cost of recompilation, but that can be nothing compared to the cost of using inefficient plan.
    Plamen Ratchev
    Thursday, November 19, 2009 6:00 PM