April 22, 2011 at 2:43 pm
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.
April 22, 2011 at 3:36 pm
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
April 25, 2011 at 4:58 am
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
April 25, 2011 at 8:17 am
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.
April 26, 2011 at 11:52 am
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