OPTION RECOMPILE means not cached

Our production server had a problem with very large cached plans that were only run once.  So they were hogging memory for no good reason.   I didn’t know how to avoid storing these in the plan cache until I found the answer today.

Just use the OPTION(RECOMPILE) or WITH RECOMPILE hints.

I thought this was an excellent post, where the writer says that WITH RECOMPILE could be better named WITH COMPILE_NO_CACHE.

http://blogs.msdn.com/b/sqlmeditation/archive/2013/01/01/is-it-really-quot-recompile-quot-or-a-quot-private-temporary-compile-quot.aspx

Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

2 Responses to OPTION RECOMPILE means not cached

  1. Chris Dorch says:

    While this does exactly as you describe, keep in mind that you are adding CPU overhead/time to your execution by having your server compile the procedure every time it’s executed. If you are positive that it will never run more than once, this option works. Otherwise, if you know it will be executed more than once, but you want to clear it out (or any low use objects) as a daily process, look into using DBCC FREEPROCCACHE ‘plan_handle’|’sql_handle’

  2. Thanks Chris. That is a good thing to point out. The case I was referring to is a nightly process that takes up over 4 GB in the cache. And the next night, the query will be completely different, but just as large. Every night, it pushes something useful out of memory that could have stayed there. The method in the blog seems better than using DBCC FREEPROCCACHE to clean up the cache every morning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s