January 28, 2013 at 8:12 pm
Hello Everyone,
Currently I am having an issue, I did my SQL Script and it takes a decent amount of time to run in SSMS (8 seconds), but at the moment I create it as a stored procedure, it takes 36 seconds to perform the very same operation, I inspected both execution plans and they are identical.
The query takes data from a linked server (48% of the batch cost) and does some summaries (48% of the batch) and then shows up a couple lines, despite the exec plans are the same for both running as a script in SSMS or as a SP, the time it takes to complete is what makes the difference.
Have someone faced a similar situation before? What strategy may I use in order to go back to the 8 secs exec time?
Thanks for any help you may provide!!!!
Frank.
January 29, 2013 at 12:49 am
Are you passing into the SP any parameters that are then used in the WHERE clause of the query?
If your answer is YES, then you may want to Google "SQL parameter sniffing."
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 29, 2013 at 3:59 am
If the query plans are truly identical, it doesn't sound like bad parameter sniffing. Are you sure they're absolutely identical or do they just have the same operators? Look at the properties to be sure.
Other than that, for the same query against the same data using the same filtering criteria to return identical data sets and the only difference is a procedure versus a query... First thought is bad parameter sniffing. After that, I'm short on suggestions. Check the ANSI connection settings from both connections (you can see them in the query plans) to see if they match. Possibly that could cause issue, but you'd probably see differences in the execution plans. Are you sure you're returning exactly the same amount of data in each case?
"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
January 29, 2013 at 10:09 am
I added parameter sniffing to the SP (no need to look it up, just didn't remember it as an strategy to improve SPs...) and now it came back quicker than the script running in SSMS, which is great.
Thanks for your help.
Frank.
January 29, 2013 at 11:36 am
f_ernestog (1/29/2013)
I added parameter sniffing to the SP
Totally confused. Parameter sniffing is on by default for all stored procedures and parameterized queries. It requires a traceflag to turn it off, and does so for the entire server. So... I'm happy that you've solved the problem, but I'm utterly confused about what you did.
"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
January 29, 2013 at 11:44 am
Well, the server was set up and maintained by a guy who thinks we should migrate everything to MySQL, and every time I try to change stuff for good, he switches it back and tells me to stay away .... so, this might be the issue here.
Thanks for all.
Frank.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply