June 21, 2008 at 7:24 pm
Hi All,
I have a stored procedure that executes long takes almost 35 secs. I tried to execute the TSQL statements within the procedure and that took less than a second. I tried recompile and update stats nothing helps. Your help will be highly appreciated. thanks
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 21, 2008 at 7:37 pm
Can you show us the procedure and the execution plans?
[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]
June 21, 2008 at 10:38 pm
smells like a parameter sniffing problem.
get it? 🙂
---------------------------------------
elsasoft.org
June 23, 2008 at 8:53 am
1) are you sure there wasn't blocking going on when the sproc was executing?
2) Like others asked, we need to see the code, query plans and IO information (set statistics IO on)
3) for the tsql run, did you use variables or hard code the values?
4) Parameter sniffing isn't likely the cause here because the OP used recompile on the sproc and did update stats, both of which will flush the existing plans from cache. Most likely just 'unfortunate' estimates by the optimizer due to data value distribution inequalities.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply