context for B is generated. Suppose that during this first execution, the "true" branch of the "if" is
taken. Further, suppose that B was submitted again by another connection during the first execution.
Because the only execution context existing at that moment was in use, a second execution context is
generated and given to the second connection. Suppose that the second execution context takes the
"false" branch of the "if". After both executions complete, B is submitted by a third connection.
Supposing that the third execution of B chooses the "true" branch, the execution will complete slightly
faster if SQL Server chose the first execution context of B for that connection rather than the second
execution context.
Execution contexts of a batch S can be reused even if the calling sequence of S differs. For example,
one calling sequence could be "stored proc 1 --> stored proc 2 --> S", whereas a second calling
sequence could be "stored proc 3 --> S". The execution context for the first execution of S can be
reused for the second execution of S.
If a batch execution generates an error of severity 11 or higher, the execution context is destroyed. If a
batch execution generates a warning (an error with severity 10), the execution context is not
destroyed. Thus, even in the absence of memory pressure — which can cause plan cache to shrink—
the number of execution contexts (for a given query plan) cached in plan cache can go up and down.
Execution contexts for parallel plans are not cached. A necessary condition for SQL Server to compile a
parallel query plan is that the minimum of the number of processors that have survived the processor
affinity mask and the value of the "max degree of parallelism" server-wide option (possibly set using
the "sp_configure" stored procedure) is greater than 1. Even if a parallel query plan is compiled, SQL
Server's "Query Execution" component may generate a serial execution context out of it. Any execution
contexts derived out of a parallel plan — serial or parallel — are not cached. A parallel query plan,
however, is cached.
Query plan caching and various SET options (showplan-related and others)
Various SET options — most of them showplan-related— affect compilation, caching, and reuse of query
plans and execution contexts in complex ways. The following table summarizes the details.
The table should be read as follows. A batch is submitted to SQL Server under a specific mode specified
in the first column. A cached query plan may or may not exist in the plan cache for the submitted
batch. Columns 2 and 3 cover the cases when a cached query plan exists; columns 4 and 5 cover the
cases when a cached query plan does not exist. Within each category, the cases for query plans and
execution contexts are separated. The text explains what happens to a structure (query plan or
execution context): whether it is cached, reused, and used.
Mode name
When a cached
query plan
exists
When a cached
query plan
exists
When a cached
query plan does
not exist
When a cached
query plan does
not exist
Query plan Exec context Query plan Exec context
showplan_text,
showplan_all,
showplan_xml
Reused (no
compilation)
Reused Cached
(compilation)
One exec
context is
generated, not
used, and
cached
statistics profile,
statistics xml,
statistics io,
statistics time
Reused (no
compilation)
Not reused. A
fresh exec
context is
generated,
used, and not
cached
Cached
(compilation)
One exec
context
generated,
used, and not
cached
noexec Reused (no
compilation)
Reused Cached
(compilation)
Execution
context is not
generated
(because of
the "noexec"
mode).
parseonly (e.g.,
pressing "parse"
n/a n/a n/a n/a
Pa
e 6 of 30Batch Compilation, Recompilation, and Plan Cachin
Issues in SQL Server 2005
2009-10-15http://technet.microsoft.com/zh-cn/librar
/cc966425(en-us,printer).aspx