September 28, 2009 at 3:43 pm
I'm looking for suggestions on how to troubleshoot a slow stored procedure call (e.g. exec usp_sp1).
I am stating the problem this way because when the code in the stored procedure is extracted and run directly, it is fast (4 seconds direct vs 20 seconds when called). I have tried calling the stored procedure WITH RECOMPILE but it did not change the execution time.
I would include the SQL code from the stored procedure, but it is big (> 1000 lines). So, I realize that I can only get general suggestions on what to check.
The part that doesn't make sense to me is that calling the stored procedure takes a long time (e.g. 20 seconds), calling it WITH RECOMPILE takes about as long, but executing the stored procedure's SQL code directly (with the same parameter values) is much faster (4 seconds). If the problem was a caching problem, I would have expected the call to be fast (using the cached version) and the direct execution (compiling) to be slow. Or, there would be a difference between calling the stored procedure WITH RECOMPLE and without. There isn't.
September 28, 2009 at 3:49 pm
Wow, there are several of these issues today seemingly! Here's the reply I sent to another person just a little while ago (I'll add a link to that thread in an Edit):
http://qa.sqlservercentral.com/Forums/Topic794772-65-1.aspx
Possibly. Without actually running this in my environment, you may want to look at "parameter sniffing". I had a really large data loading process a few years back that presented the SAME symptoms. In my case it was a difference between 90 minutes in processing time versus 16 hours in what was acting like a runaway query.
Even though it seems silly, try declaring local variables within the sp and reassigning your parameters to them. So, your call looks like:
EXEC dbo.mystoredProc @var1 = 'a', @var2 = 'b' <snip...>
DECLARE
@localVar1 <<datatype>>,
@localVar2 <<datatype>>
SET @localVar1 = @var1
SET @localVar2 = @var2
Make sure to change your variable assignments in the code to look at the "new" local versions of the same thing. See if that solves your problem - it did mine.
HTH:
Mark
September 28, 2009 at 3:50 pm
This is most likely a problem know as "Parameter Sniffing". You can read this[/url] excellent article by Gail Shaw (Gilamonster) who explains it much better than I.
Long story short: the most reliable way to fix it is to not use the proc's parameters directly in a query, but rather save them into local variables and then use those variables in your Query/DML instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 28, 2009 at 3:51 pm
Oops, someone beat me to it. Still, I highly recommend Gail's article which explains what's really going on very well.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 28, 2009 at 3:55 pm
I agree with RBarryYoung, read the article (that's what I am going to do now, in fact). My recommendation came from my specific problem, and although it solved my immediate need what I did was more cargo-cult programming than really understanding the nuts & bolts of why SQL was acting this way.
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply