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 «««1234»»

The RECOMPILE Options Expand / Collapse
Author
Message
Posted Thursday, July 07, 2011 10:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:11 PM
Points: 32,810, Visits: 14,959
We have two e-books here that can help you learn more about this topic:

Inside the SQL Server Query Optimizer - http://www.sqlservercentral.com/articles/books/71564
SQL Server Execution Plans - http://www.sqlservercentral.com/articles/books/65831







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1138300
Posted Thursday, July 07, 2011 10:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:49 PM
Points: 2,263, Visits: 3,758
Thank you Suresh, this was a useful article, quite simple to follow with neat examples.

Mohammed Moinudheen
Post #1138306
Posted Thursday, July 07, 2011 11:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:51 AM
Points: 141, Visits: 113
Dear reader,
What I meant by thae statement is the following:

Usually developers tend to use TSQL from their application instead of SP's. Then their plan cache fills up faster that usual. Then they use OPTION (recompile) as an answer to reduce plan cache contention. And continue to use adhoc TSQL code from their application. This kills the many benefits of SP's completely.



Kindest Regards,

M Suresh Kumar

Post #1138327
Posted Thursday, July 07, 2011 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 12:17 PM
Points: 1, Visits: 49
I too am curious about having to fully qualify the object w/ db name. Can someone please elaborate? I haven't heard that before.

Thanks.



Post #1138353
Posted Thursday, July 07, 2011 11:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:51 AM
Points: 141, Visits: 113
"Is it really necessary to include the database name in all object references? I've always included schema.object but not database name as it's impossible to deploy the same code base on many databases if you hardcode the db name..."

Hi,
Although SQLBOL says use DBname.SchemaName.ObjectName, I have found in my practical experience that SchemaName.ObjectName itself suffices. I have not found any unnecessary recompilations due to usage of SchemaName.ObjectName. But I have when using just ObjectName.





Kindest Regards,

M Suresh Kumar

Post #1138369
Posted Thursday, July 07, 2011 11:43 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: Wednesday, April 16, 2014 9:46 AM
Points: 3,150, Visits: 1,900
Thanks for the links Steve!
Post #1138370
Posted Thursday, July 07, 2011 2:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:22 AM
Points: 70, Visits: 281
Thanks for the links, Steve!

As an aside, Gail is teaching an entire class on Execution Plans at this year's PASS conference: [url=http://www.sqlpass.org/summit/2011/Speakers/CallForSpeakers/SessionDetail.aspx?sid=1245][/url]

Seven delicious hours!! See you there!
Post #1138531
Posted Thursday, July 07, 2011 5:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 4:03 PM
Points: 173, Visits: 422
WITH RECOMPILE has been a new friend of mine for few months only now,
we have a few SSRS reports running against our data warehouse and some of them have many parameters, including a date range
and basically without WITH RECOMPILE SQL Server will simply cache the plan used for the first execution after the CREATE statement runs, then use it for all subsequent executions... (which so far is exactly what we would want most of the time)...

but the plan the optimizer will use and cache for a request that had a very wide date range and say 'null' in all other input params will not be optimal for let's say the next request which may have a much narrower date range and a few other input parameters set to different values

WITH RECOMPILE shouldn't be used blindly but in some cases (vast minority...) it turns out that the overhead of recalculating the plan each time is less then the overhead of having a less than optimal plan running.

On an OLTP system in the past I personally never found WITH RECOMPILE useful.



Claude
Post #1138616
Posted Friday, July 08, 2011 1:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 06, 2012 7:37 AM
Points: 37, Visits: 77
cfradenburg (7/7/2011)
OPTION (RECOMPILE) is to be only used as a temporary measure while a process for converting adhoc T-SQL code to Stored Procedures is underway.


I'm not sure I agree with this. I've seen cases where there's a statement that doesn't take a lot of resources to compile and depending on the parameters passed in different execution plans can be generated. Statements like that are good candidates for recompiles. Granted, it may be more efficient to compile it with a specific parameter but that depends on the situation. Using option recompile certainly isn't the first thing I would look at but there are times it may end up being the best tool.

Yes, e.g. in SAP environments all selects to the DB from the business application are going to the DB interface via parameterized SPs. But e.g. for the iDOCs the status will be changed after each processing of it and the status flag has a very high inequality in the distribution of values per status. And also the autoupdate of statistics don't runs often enough by the 20% thrashould value. We so run daily an update statistics manually for the status field and recompile all executions with an hint in the ABAP-code which going to the IDOC status table with very good performance results per execution. In this case, the distribution of date is unequally, the auto update statistics runs not often enough; the parameters are very different and the selects runs seldom in relation to other selects. Here the recompile is a very good option.

Suresh Kumar Maganti (7/7/2011)
Dear reader,
What I meant by thae statement is the following:

Usually developers tend to use TSQL from their application instead of SP's. Then their plan cache fills up faster that usual. Then they use OPTION (recompile) as an answer to reduce plan cache contention. And continue to use adhoc TSQL code from their application. This kills the many benefits of SP's completely.

Thats absolutly understandable.

Harveysburger (7/7/2011)
...
On an OLTP system in the past I personally never found WITH RECOMPILE useful.
Claude

Never say never
Post #1138698
Posted Friday, July 08, 2011 6:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 4:03 PM
Points: 173, Visits: 422
hehe yes my mistake,
I didn't mean to say that it should never happen, just that from my own past experiences I haven't seen a good use for it, but I only worked on a handful of OLTP systems so who knows :)

Post #1139296
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse