SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The RECOMPILE Options


The RECOMPILE Options

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148078 Visits: 19444
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
My Blog: www.voiceofthedba.com
M&M
M&M
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6117 Visits: 3913
Thank you Suresh, this was a useful article, quite simple to follow with neat examples.

M&M
Suresh Kumar Maganti
Suresh Kumar Maganti
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 125
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

shanonbailey
shanonbailey
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 127
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.



Suresh Kumar Maganti
Suresh Kumar Maganti
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 125
"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

Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6616 Visits: 2398
Thanks for the links Steve!
@SixStringSQL
@SixStringSQL
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 389
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!
Harveysburger
Harveysburger
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 430
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
rene.schulz@implico.com
rene.schulz@implico.com
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 84
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 ;-)
Harveysburger
Harveysburger
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 430
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search