Archive for September 2010
SQL Server plan caching and “with recompile”
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.
Busy beavers defeat Infinity
This simple fact is a striking revelation for me:
http://en.wikipedia.org/wiki/Busy_beaver#Applications
http://www.umcs.maine.edu/~chaitin/bellcom.pdf
Basically, any decidable property of the natural numbers (a PI_1 formula) can be proved by a computer, by brute force. The program wouldn’t need to check all the numbers. If it runs longer than the busy beaver while looking for a counterexample, it will never find one.
This includes, by the way, proving consistency of formal systems: Con(F) is a PI_1 formula; we can write a program that checks each natural number for being the proof number for “1=0”.
UPDATE (6 years later):
they built an actual Turing machine that won’t halt only if ZF is consistent.
As a side benefit, machines that can prove Goldbach’s Conjecture and the Riemann Hypothesis by searching a _finite_ segment of N for counterexamples.
UPDATE 2:
Chaitin’s “Computing the Busy Beaver Function” is now available at this URL:
or google for pdf containing the following:
Drawing Blanks 