Databases Reference
In-Depth Information
Optimization Phases
The Query Optimizer has several optimization phases designed to try to optimize queries
as quickly and simply as possible, and to not use more expensive and sophisticated
options unless absolutely necessary. These phases are called the
simplification
,
trivial
plan optimization
and
full optimization
stages. In the same way, the full optimization
phase itself consists of three stages simply called
search 0
,
search 1
and
search 2
.
Plans can be produced in any of these phases except for the simplification one, which I'll
discuss in a moment. In this section, I'll use the
sys.dm_exec_query_optimizer_
info
DMV, introduced earlier in this chapter, to show additional information about
these optimization phases.
Simpliication
Query rewrites or, more exactly, tree rewrites are performed on this stage to reduce the
query tree into a simpler form in order to make the optimization process easier. Some of
these simplifications include those below.
• Subqueries are converted into joins, but since a subquery does not always translate
directly to an inner join, outer join and group by operations may be added as necessary.
• Redundant inner and outer joins may be removed. A typical example is the Foreign Key
Join elimination which occurs when SQL Server can detect that some joins may not
be needed, as foreign key constraints are available and only columns of the referencing
table are requested. An example of Foreign Key Join elimination is shown later.
• Filters in WHERE clauses are pushed down in the query tree in order to enable early
data filtering, and potentially better matching of indexes and computed columns later
in the optimization process (this simplification is known as predicate pushdown).
