February 9, 2012 at 9:47 am
I've got an SP which I pass three paramaters, two datetime and one varchar,
If I run the code on it's own declaring variables and setting them it takes around 2 seconds to complete. If I execute the sp with the same variables it's taking between 8-10 seconds.
This is scaled up more so on our live environment vs test.
I've tried:
Dropping and recreating the SP
Creating the SP with a different name
Running sp_recompile
Setting local variables within the SP - e.g. DECLARE @DateminLocal datetime, SET @DateminLocal = @Datemin to try and get around parameter sniffing
All to no avail!
I'm not really sure what to try next, I've not come across this before!
Any ideas guys?!
February 9, 2012 at 9:58 am
Have you tried using the OPTIMIZE FOR hint with either a specific value of for UNKNOWN?
February 9, 2012 at 10:09 am
Post your proc code!
Try:
SET ANSI_NULLS ON
GO
Before creating your proc.
February 9, 2012 at 10:14 am
21 seconds down to 4 seconds on the live environment? I'll take a bit of that!! Thanks for the suggestion, it's made a huge difference.
February 9, 2012 at 10:16 am
Not sure posting the code would make a massive difference, the code alone (within the proc) is fine and runs quick enough, it's just the act of running it as a proc that makes it go a bit pear shaped!
The optimise for unknown seemed to do the job though, along with having a fantastic name!
February 9, 2012 at 10:23 am
It was just a guess which worked, however it could be down to the code inside of proc. It is always good to post code when asking performance question!
Also, playing directly in a live environment? That is cool! I am with you on this, why bother to spend money on dev and test ones, prod is the best playground! 😀
February 9, 2012 at 10:25 am
Wasn't necessarily 'playing' in the live environment! The query and hence proc were created in a dev environment and tested, sadly it's not that representative of the live environment. Taking around 8 seconds on dev vs 21 or so on a live environment.
I could tell that the 8 seconds was bad but 21 was far worse!
I used to just play on live though! - Recently started to be good and actually use a dev environment! haha
February 9, 2012 at 10:32 am
why bother to spend money on dev and test ones, prod is the best playground! 😀
It also just happens to be the most expensive 🙂
February 9, 2012 at 12:16 pm
At a guess it's parameter sniffing and coming up with a generic plan that's 'bad'.
Check out this blog by Gail Shaw for more information:
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply