Databases Reference
In-Depth Information
Chapter 5: The Optimization Process
In this chapter, I'll go into the internals of the Query Optimizer and introduce the
steps that it performs in the background, and which we don't see. This covers everything,
from the time a query is submitted to SQL Server until an execution plan is generated
and is ready to be executed, and includes steps like parsing, binding, simplification, trivial
plan optimization and full optimization. Important components which are part of the
Query Optimizer architecture, such as transformation rules and the memo structure, are
also introduced.
The purpose of the Query Optimizer, as we're all aware, is to provide an optimum
execution plan and, in order to do so, it generates possible alternative execution plans
through the use of transformation rules. These alternative plans are stored for the
duration of the optimization process in a structure called the memo. Given that finding
the optimum plan for some queries would take an unacceptably long optimization time,
some heuristics are used to limit the number of alternative plans considered, instead of
using the entire search space - remember that the goal is to find
a good enough plan
as
quickly as possible
. Heuristics help the Query Optimizer to cope with the combinatorial
explosion which occurs in the search space as queries get progressively more complex.
However, the use of transformation rules and heuristics does not necessarily reduce
the cost of the available alternatives, so each candidate plan is also costed, and the best
alternative is chosen based on those costs.
Overview
The query optimization and execution process were introduced in
Chapter 1, Introduction
to Query Optimization
, and will be explained in more detail throughout the rest of this
chapter. However, before we get started, I'll very briefly describe the inner workings of the
