InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 04:50:03 GMT20http://www.sqlservercentral.com/Forums/Topic1412731-392-1.aspxWell, 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.Tue, 29 Jan 2013 11:44:51 GMTf_ernestoghttp://www.sqlservercentral.com/Forums/Topic1412731-392-1.aspx[quote][b]f_ernestog (1/29/2013)[/b][hr]I added parameter sniffing to the SP [/quote]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.Tue, 29 Jan 2013 11:36:14 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1412731-392-1.aspxI 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.Tue, 29 Jan 2013 10:09:02 GMTf_ernestoghttp://www.sqlservercentral.com/Forums/Topic1412731-392-1.aspxIf 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?Tue, 29 Jan 2013 03:59:59 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1412731-392-1.aspxAre 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."Tue, 29 Jan 2013 00:49:57 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1412731-392-1.aspxHello 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!!!!Mon, 28 Jan 2013 20:12:18 GMTf_ernestog