Stored Procedure Hangs when called from an application

  • A customer using our software experienced a problem that turned out to be caused by a SQL 2005 stored procedure that would start but never complete when called by our application. This happened consistently from within the application, but always completed when run via SSMS.

    Finally I recompiled the stored procedure - problem solved. This stored procedure had run many times before then without incident. Why would it start hanging consistently until recompiled?

    Some of the things that stored procedure does are as follows:

    Creates a couple of # tables

    Declares one table variable

    Insert into one # table using OPENXML

    Insert distinct * into the other # table from the first # table

    Insert into the table variable any values from the second # table that have everything the same except one column

    It's the last step that starts but never ends.

    Any help/suggestions would be greatly appreciated. Thanks.

  • This is usually an indication of a bad execution plan for the parameters being run from the application. Recompiling the procedure and then running with the same parameters generates a better execution plan for those parameters and the code completes successfully.

    There are a couple of workarounds - namely, setting the procedure up for recompile everytime - setting statement recompile and/or reworking the procedure. No way for us to tell for sure without seeing the code, the execution plan with sample data to test and validate.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • It does sound like bad parameter sniffing. In addition to recompile for the procedure, you can try recompile for the problematic statement within the procedure. Recompiles can be expensive, so other options like using local variables in place of parameters or looking into using an OPTIMIZE FOR query hint can also be useful. Based on what you've described, I'd take a look at the OPTIMIZE FOR hint since it sounds like you can get a good plan.

    Also, sometimes this is caused by out of date statistics. Make sure good stats maintenance is being run. It's possible you have an index or a table that needs a more frequent update or even a FULL SCAN stats update. Something else to look into.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Jeffrey and Grant: Thank you very much for taking the time to review and respond to my post. I have a developer looking over your posts and the stored procedure. One thing he found so far is that this particular customer runs this stored procedure against either a really small set of data, or a really large set. FYI, the procedure has 7 input parameters. If I have further questions or information I will post again. Thanks.

  • StripedCat (4/25/2011)


    Jeffrey and Grant: Thank you very much for taking the time to review and respond to my post. I have a developer looking over your posts and the stored procedure. One thing he found so far is that this particular customer runs this stored procedure against either a really small set of data, or a really large set. FYI, the procedure has 7 input parameters. If I have further questions or information I will post again. Thanks.

    With 7 input parameters you also may have a problem with Catch All queries. You may want to look at this excellent article by Gail Shaw on the subject:

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Todd Fifield

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply