Query tanking longer when created as SP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Query tanking longer when created as SP

Query tanking longer when created as SP

Rate Topic

Display Mode

Topic Options

Author

Message

f_ernestog

Posted Monday, January 28, 2013 8:12 PM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 12:35 PM
Points: 748, Visits: 135

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

dwain.c

Posted Tuesday, January 29, 2013 12:49 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:59 PM
Points: 2,369, Visits: 3,248

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."

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1412790

Grant Fritchey

Posted Tuesday, January 29, 2013 3:59 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1412880

f_ernestog

Posted Tuesday, January 29, 2013 10:09 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 12:35 PM
Points: 748, Visits: 135

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

Grant Fritchey

Posted Tuesday, January 29, 2013 11:36 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1413217

f_ernestog

Posted Tuesday, January 29, 2013 11:44 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 12:35 PM
Points: 748, Visits: 135

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 »

Permissions