Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 114

➤

Phase 1 — Explore more rules, and alternate join ordering. If the best (cheapest) plan
costs less than 1.0, then use this plan. If not, then if MAXDOP > 0 and this is an SMP
system, and the min cost > cost threshold for parallelism, then use a parallel plan. Compare
the cost of the parallel plan with the best serial plan, and pass the cheaper of the two to
phase 2.

➤

Phase 2 — Explore all options, and opt for the cheapest plan after a limited number of
explorations.

The output of the preceding steps is an executable plan that can be placed in the cache. This plan is
then scheduled for execution, which is explored later in this chapter.

You can view the inner workings of the optimization process via the DMV sys.dm_exec_query_
optimizer_info . This DMV contains a set of optimization attributes, each with an occurrence and
a value. Refer to SQL Books Online (BOL) for full details. Here are a few that relate to some of the
steps just described:

select *
from sys.dm_exec_query_optimizer_info
where counter in (
'optimizations'
, 'trivial plan'
, 'search 0'
, 'search 1'
, 'search 2'
)
order by [counter]

The preceding will return the same number of rows as follows, but the counters and values will be
different. Note that the value for optimizations matches the sum of the trivial plan, search 0, search
1, and search 2 counters (2328 8559 3 17484 28374):

+ + + =

Counter occurrencevalue
Optimizations 28374 1
search 0 2328 1
search 1 8559 1
search 2 3 1
trivial plan 17484 1

Parallel Plans
A parallel plan is any plan for which the Optimizer has chosen to split an applicable operator into
multiple threads that are run in parallel.

Not all operators are suitable to be used in a parallel plan. The Optimizer will only choose a parallel
plan if:

➤

the server has multiple processors,

➤

the maximum degree of parallelism setting allows parallel plans, and