Database Reference
In-Depth Information
Query Plan
The query plan is a reentrant, read-only data structure, with commands that specify all the physical operations
required to execute the query. The reentrant property allows the query plan to be accessed concurrently by multiple
connections. The physical operations include specifications on which tables and indexes to access, how and in what
order they should be accessed, the type of join operations to be performed between multiple tables, and so forth.
No user context is stored in the query plan.
Execution Context
The execution context is another data structure that maintains the variable part of the query. Although the server
keeps track of the execution plans in the procedure cache, these plans are context neutral. Therefore, each user
executing the query will have a separate execution context that holds data specific to their execution, such as
parameter values and connection details.
Aging of the Execution Plan
The procedure cache is part of SQL Server's buffer cache, which also holds data pages. As new execution plans are
added to the procedure cache, the size of the procedure cache keeps growing, affecting the retention of useful data
pages in memory. To avoid this, SQL Server dynamically controls the retention of the execution plans in the procedure
cache, retaining the frequently used execution plans and discarding plans that are not used for a certain period of time.
SQL Server keeps track of the frequency of an execution plan's reuse by associating an age field to it. When an
execution plan is generated, the age field is populated with the cost of generating the plan. A complex query requiring
extensive optimization will have an age field value higher than that for a simpler query.
At regular intervals, the current cost of all the execution plans in the procedure cache is examined by SQL
Server's lazy writer process (which manages most of the background processes in SQL Server). If an execution plan
is not reused for a long time, then the current cost will eventually be reduced to 0. The cheaper the execution plan
was to generate, the sooner its cost will be reduced to 0. Once an execution plan's cost reaches 0, the plan becomes
a candidate for removal from memory. SQL Server removes all plans with a cost of 0 from the procedure cache when
memory pressure increases to such an extent that there is no longer enough free memory to serve new requests.
However, if a system has enough memory and free memory pages are available to serve new requests, execution plans
with a cost of 0 can remain in the procedure cache for a long time so that they can be reused later, if required.
As well as changing the costs downward, execution plans can also find their costs increased to the max cost of
generating the plan every time the plan is reused (or to the current cost of the plan for ad hoc plans). For example,
suppose you have two execution plans with generation costs equal to 100 and 10. Their starting cost values will
therefore be 100 and 10, respectively. If both execution plans are reused immediately, their age fields will be set back
to that maximum cost. With these cost values, the lazy writer will bring down the cost of the second plan to 0 much
earlier than that of the first one, unless the second plan is reused more often. Therefore, even if a costly plan is reused
less frequently than a cheaper plan, because of the effect of the initial cost, the costly plan can remain at a nonzero
cost value for a longer period of time.
Summary
SQL Server's cost-based query optimizer decides upon an effective execution plan based not on the exact syntax of
the query but by evaluating the cost of executing the query using different processing strategies. The cost evaluation
of using different processing strategies is done in multiple optimization phases to avoid spending too much time
optimizing a query. Then, the execution plans are cached to save the cost of execution plan generation when the same
queries are reexecuted.
In the next chapter, I will discuss how the plans get reused from the cache in different ways depending on how
they're called.
