Database Reference
In-Depth Information
Another option is to simply look at the plans in your cache and then make an estimate, based on the queries
there and the type of workload they represent to arrive at a specific number. You can separate your OLTP queries from
your reporting queries and then focus on the reporting queries most likely to benefit from parallel execution. Take an
average of those costs and set your cost threshold to that number.
■
While i do refer to these values as being measured in seconds, that is just a construct used by the optimizer.
they are not literal measures.
Note
The DML action queries (
INSERT
,
UPDATE
, and
DELETE
) are executed serially. However, the
SELECT
portion of an
INSERT
statement and the
WHERE
clause of an
UPDATE
or a
DELETE
statement can be executed in parallel. The actual
data changes are applied serially to the database. Also, if the optimizer determines that the estimated cost is too
low, it does not introduce parallel operators.
Note that, even at execution time, SQL Server determines whether the current system workload and
configuration information allow for parallel query execution. If parallel query execution is allowed, SQL Server
determines the optimal number of threads and spreads the execution of the query across those threads. When a query
starts a parallel execution, it uses the same number of threads until completion. SQL Server reexamines the optimal
number of threads before executing the parallel query the next time.
Once the processing strategy is finalized by using either a serial plan or a parallel plan, the optimizer generates
the execution plan for the query. The execution plan contains the detailed processing strategy decided by the
optimizer to execute the query. This includes steps such as data retrieval, result set joins, result set ordering, and
so on. A detailed explanation of how to analyze the processing steps included in an execution plan is presented in
Chapter 4. The execution plan generated for the query is saved in the plan cache for future reuse.
Execution Plan Caching
The execution plan of a query generated by the optimizer is saved in a special part of SQL Server's memory pool
called the
plan cache
or
procedure cache
. (The procedure cache is part of the SQL Server buffer cache and is explained
in Chapter 2.) Saving the plan in a cache allows SQL Server to avoid running through the whole query optimization
process again when the same query is resubmitted. SQL Server supports different techniques such as
plan cache aging
and
plan cache types
to increase the reusability of the cached plans. It also stores two binary values called the
query
hash
and the
query plan hash
.
■
i discuss the techniques supported by SQl Server for improving the effectiveness of execution plan reuse in
this Chapter 15.
Note
Components of the Execution Plan
The execution plan generated by the optimizer contains two components.
•
Query plan
: This represents the commands that specify all the physical operations required to
execute a query.
•
Execution context
: This maintains the variable parts of a query within the context of a given user.
I will cover these components in more detail in the next sections.
