Archive for July 3rd, 2010

SQL Server – RECOMPILE doesn’t happen ? July 3rd, 2010

Vinod Kumar

This is a wonderful topic and very close to my heart in some way because it is part of  Performance optimization techniques. If you had a chance to read this whitepaper on TechNet on “Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005” then you will understand where I am really coming from. That whitepaper has a lot of interesting internal information hidden between the lines. This post is not to mimic or copy parts from there by any chance but take a slightly different route of when the RECOMPILE doesn’t happen. The whitepaper exhaustively outlines when it happens in detail but there are some conditions when it can get differed by the optimizer. Let me call out some of them here –

There are specific conditions when the recompilation gets short circuited.

– When the plan is a “trivial plan.” A trivial plan results when the query optimizer determines that given the tables referenced in the query and the indexes existing on them, only one plan is possible. Obviously, a recompilation would be futile in such a case. A query that has generated a trivial plan may not always generate a trivial plan, of course. For example, new indexes might be created on the underlying tables and so multiple access paths become available to the query optimizer.

– When a query contains “KEEPFIXED PLAN” hint, its plan is not recompiled for plan optimality-related reasons.

– When all of the tables referenced in the query plan are read-only, the plan is not recompiled.

So some of these are really interesting conditions in understanding the RECOMPILE architecture esp on heavy workloads. So one of the main readings I see on any perf-tuning exercise is the recompile perfmon counter. In SQL Server 2008 onwards we introduced the Stmt level recompile and it will surely form another long post some other day.

On the Performance Tuning topic – I also recommend reading this whitepaper. Though a bit dated, they are very relevant even today with SQL Server 2008 versions.

Continue reading...