Databases Reference
In-Depth Information
Since some queries may have a huge number of possible query plans, it's sometimes not
feasible to explore their entire search space, as it would take too long. So, in addition to
applying transformation rules, a number of heuristics are used by the Query Optimizer
to control the search strategy and to limit the number of alternatives generated, in order
to quickly find a good plan. The Query Optimizer needs to balance the optimization time
and the quality of the selected plan. For example, as explained in
Chapter 1, Introduction
to Query Optimization
, optimizing join orders can create a huge number of possible
alternatives. So, a common heuristic used by SQL Server to reduce the size of the search
space is to avoid considering bushy trees.
In addition, the optimization process can immediately finish if a good enough plan
(relative to the Query Optimizer's internal thresholds) is found at the end of any of these
three phases. However if, at the end of any given phase, the best plan is still very expen-
sive, then the Query Optimizer will run the next phase, which will run an additional set
of (usually more complex) transformation rules. These phases are shown as search 0,
search 1 and search 2 on the
sys.dm_exec_query_optimizer_info
DMV.
Search 0
Similar to the concept of the trivial plan, the first phase, search 0, will aim to find a
plan as quickly as possible without trying sophisticated transformations. Search 0,
called the transaction processing phase, is ideal for the small queries typically found on
transaction processing systems and it is used for queries with at least three tables. Before
the full optimization process is started, the Query Optimizer generates the initial set of
join orders based on heuristics. These heuristics begin by first joining the smallest tables
or the tables that achieve the largest filtering based on their selectivity. Those are the
only join orders considered on search 0. At the end of this phase, the Query Optimizer
compares the cost of the best generated plan to an internal cost threshold and, if the plan
is still very expensive, SQL Server will run the next phase.
