Drawing Blanks

Premature Optimization is a Prerequisite for Success

SQL Server plan caching and “with recompile”

leave a comment »

The other day I had to troubleshoot yet another stored proc performance issue that was due to a stale execution plan. What I typically try as a quick fix is ALTER PROC … WITH RECOMPILE… And what I typically notice is that it doesn’t work… Until you do dbcc freeproccache or get rid of the cached plan by some other means. Note that my observations apply to SQL Server 2000 and SQL Server 2005 in the 2000 compatibility mode, both Enterprise and Standard editions.

So I strongly suspect that declaring a proc WITH RECOMPILE does not actually (always?) cause recompilation. All it does is instructs the server not to cache the plan. However if a previously cached plan still exists, it will be used, even though the proc is declared ‘with recompile’.  (Even drop and then create with recompile won’t help).

Now I feel I should say some words on plan caching in general, since I know there are many misconceptions around this subject, and many people would ask “why on earth would one get rid of cached plans”.

So I’m about to tell you some truths. Truths in the scientific sense, i.e. something that is supported by many observations and has never been falsified by an experiment. 🙂 Of course that means those truths are not ultimate. I’m sure that any SQL Server DBA could refine and extend them, e.g. tell us if they apply to SQL 2008 🙂

Anyway. “With recompile” is an anagram of “polemic writhe”…

  • Plan caching is good for queries that take less than 1 second to execute and are called frequently. For all other queries the benefit of plan caching needs to be carefully weighed.
  • A stale execution plan may cause query performance degrade by 1 – 2 orders of magnitude.
  • Plans that are generated using “parameter sniffing” are typically suboptimal. This is especially true when parameters specify a time range on a timestamped table. This is the most frequent stale plan scenario that I’ve encountered.
  • If you use “inline SQL” from ADO.Net rather than stored procs, the plans are still cached. Because that sql is passed to sp_executesql. 
  • Some SET options affect plan reuse. E.g. if a proc was compiled with SET Arithabort OFF and then called from a session that set Arithabort ON – it won’t use the cached plan.
  • If a query is fast in Query Analyzer, but slow in the app – this is most likely a stale plan issue.
  • Cached plans take up memory that could otherwise be used for caching and processing actual data. This rarely becomes an issue, though.

So what do we do about all this?

  • Read this http://technet.microsoft.com/en-us/library/cc966425.aspx This paper offers more sane advice and useful details than many other documents on this subject.
  • Read about parameter sniffing and the “optimize for” hints.
  • Write simple queries. Denormalize data.
  • Consider using WITH RECOMPILE on your monthly financial reports and such.

Written by bbzippo

09/16/2010 at 4:31 am

Posted in programming

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

%d bloggers like this: