Databases Reference
In-Depth Information
Yet, despite being an enormous influence in the field of query optimization research,
it suffered a major drawback: its framework could not be easily extended to include
additional transformations. This led to the development of more extensible optimiza-
tion architectures, which facilitated the gradual addition of new functionality to query
optimizers. The trailblazers in this field were the Exodus Optimizer Generator, defined by
Goetz Graefe and David DeWitt and, later, the Volcano Optimizer Generator, defined by
Goetz Graefe and William McKenna. Goetz Graefe then went on to define the Cascades
Framework, resolving errors which were present in his previous two endeavors.
While this is interesting, what's most relevant for you and me is that SQL Server
implemented its own cost-based Query Optimizer, based on the Cascades Framework,
in 1999, when its database engine was re-architected for the release of SQL Server 7.0.
The extensible architecture of the Cascades Framework has made it much easier for
new functionality, such as new transformation rules or physical operators, to be
implemented in the Query Optimizer. We will discuss transformation rules in
Chapter 5
,
The Optimization Process
, and physical operators will be discussed in
Chapter 2
,
The Execution Engine
.
Execution Plans
Now that we've got a foundation in the Query Optimizer and how it works its magic, it's
time to consider how we, as users, can interact with it. The primary way we'll interact
with the Query Optimizer is through execution plans which, as I mentioned earlier, are
ultimately trees consisting of a number of physical operators which, in turn, contain
the algorithms to produce the required results from the database. Given that I will make
extensive use of execution plans throughout the topic, and because it's very useful to be
familiar with them in any case, in this section I'll show you how to display and read them.
You can request either an actual or an estimated execution plan for a given query, and
either of these two types can be displayed as a graphic, text, or XML plan. The only
difference between these three formats is the level of detail of information displayed.
