Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query tanking longer when created as SP Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 8:12 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:15 AM
Points: 753, Visits: 173
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.
Post #1412731
Posted Tuesday, January 29, 2013 12:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:53 PM
Points: 3,438, Visits: 5,390
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1412790
Posted Tuesday, January 29, 2013 3:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1412880
Posted Tuesday, January 29, 2013 10:09 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:15 AM
Points: 753, Visits: 173
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.
Post #1413173
Posted Tuesday, January 29, 2013 11:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1413217
Posted Tuesday, January 29, 2013 11:44 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:15 AM
Points: 753, Visits: 173
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.
Post #1413222
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse