Compilation, Recompilation, and Plan Caching in SQL Server 2005
Author →
Asif
→ in
MS-SQL
→ on
Nov 18 2008 8:08PM
Tags:
Compilation
Plan Caching
Compilation
SQL Server will manage the objects in its cache in a few main ways:
freeing up buffers or aging execution plans. Before a query, batch,
stored procedure, trigger, prepared statement,
or dynamic SQL statement begins execution on a
SQL Server, the batch gets compiled into a plan. The plan is then
executed for its effects or to produce results. Plans are cached for
possible reuse opportunities. If a query plan is
not even cached, its reuse opportunity is zero. Such a plan will be
compiled every time it is executed, resulting in poor performance. In
rare cases, non-caching is a desirable, and this paper will point out
such cases later on.
A batch can contain one or more SELECT, INSERT, UPDATE, and DELETE
statements; and stored procedure calls possibly interleaved by T-SQL
"glue" or control structures such as SET, IF, WHILE, DECLARE; DDL
statements such as CREATE, DROP; and permission-related statements such
as GRANT, DENY, and REVOKE. A batch can include definition and use of
CLR constructs such as user-defined types, functions, procedures, and
aggregates. Compiled plans are stored into a part of SQL Server's memory called plan cache.
Plan cache is searched for possible plan reuse opportunities. If a plan
reuse for a batch happens, its compilation costs are avoided. Note that
in the SQL Server literature, the word "procedure cache" has been used
to describe what is called "plan cache" in this paper. "Plan cache" is
more accurate because the plan cache stores query plans of not just the
stored procedures.
SQL Server can cache query plans for many types of batches. An
enumeration of different types follows. With each type, we describe the
necessary conditions for plan reuse. Note that these conditions may not be sufficient. The reader will get a complete picture later on in this paper.
Reusing an execution plan saves
the time spent on the stored procedure compilation, but in many queries,
especially complex joins on large tables, the compilation time is significantly
less than the time needed for execution. Therefore, you may need to recompile
the stored procedure execution plan to increase the chance that the best plan
be used. There are three ways to cause SQL Server to recompile the stored procedure
execution plan:
-
Including a WITH RECOMPILE clause in a CREATE PROCEDURE
statement. When you include a WITH RECOMPILE clause in a CREATE PROCEDURE
statement, SQL Server will not cache a plan for this procedure and the
procedure will be recompiled every time it runs. Since the stored procedure
execution plan will never be cached, you should use the RECOMPILE option in a
CREATE PROCEDURE statement very carefully.
-
Including a WITH RECOMPILE clause in a EXECUTE statement.
When you include a WITH RECOMPILE clause in a EXECUTE statement, the stored
procedure execution plan will be recompiled when you run this EXECUTE
statement. You can use this option if the parameters you are supplying are
atypical or if the data has significantly changed.
-
Using the sp_recompile system stored procedure causes
stored procedures to be recompiled the next time they are run. To cause stored
procedures to be recompiled the next time they are run, you can use the sp_recompile
system stored procedure. You can use the sp_recompile system stored procedure
when you want your stored procedure to reflect changes in indexes or data
values.
Because SQL Server 2000 can
recompile stored procedures and execution plans automatically, in most cases it
is not necessary to use the sp_recompile system stored procedure or a WITH
RECOMPILE clause, and you can rely on SQL Server decisions to recompile the
execution plan.
When a cache-able batch is submitted to SQL Server 2005 for execution, it is compiled and a query plan
for it is put in the plan cache. Query plan is a read-only reentrant
structure that is shared by multiple users. There are at most two
instances of a query plan at any time in plan cache: one for all of the
serial executions and one for all of the parallel executions. The copy
for parallel executions is common for all of the degrees of parallelism
With every query plan and execution context, a cost is stored. The cost
partially controls how long the plan or context will live in the plan
cache. In SQL Server 2000 and SQL Server 2005, the costs are calculated
and manipulated differently.
In SQL Server 2005, the plan cache is distinct from the data cache. In
addition, there are other functionality-specific caches. The
lazy-writer process does not decrement costs in SQL Server 2005.
Instead, as soon as the size of the plan cache reaches 50% of the
buffer pool size, the next plan cache access decrements the ticks of
all of the plans by 1 each. Notice that because this decrement is
piggybacked on a thread that accesses the plan cache for plan lookup
purpose, the decrement can be considered to occur in a lazy fashion. If
the sum of the sizes of all of the caches in SQL Server 2005 reaches or
exceeds 75% of the buffer pool size, a dedicated resource monitor
thread gets activated, and it decrements tick counts of all of the
objects in all of the caches. (So this thread's behavior approximates
that of the lazy-writer thread in SQL Server 2000.) A query plan reuse
causes the query plan cost to be reset to its initial value.
No Comments Yet
axiom.c
