Databases Reference
In-Depth Information
An example of an implementation rule would be selecting a physical algorithm for a
logical join, such as a Merge Join or a Hash Join.
So the Query Optimizer is using sets of transformation rules to generate and examine
possible alternative execution plans. However, it's important to remember that applying
transformations does not necessarily reduce the cost of the generated alternatives, and
the costing component still needs to estimate their costs. Although both logical and
physical alternatives are kept in the memo structure, only the physical alternatives are
costed. It's important, then, to bear in mind that, although these alternatives may be
equivalent and return the same results, their physical implementations may have very
different costs. The final selection, as is hopefully clear now, will be the best (or, if you
like, the "cheapest") physical alternative stored in the memo.
For example, implementing
A join B
may have different costs depending on whether
a Nested Loops Join or a Hash Join is selected. In addition, for the same physical join,
implementing the
A join B
expression may have different performance from
B join A
.
As explained in
Chapter 2, The Execution Engine
, the performance of a join is different
depending on which table is chosen as the inner or outer table in a Nested Loops Join,
or the build and the probe inputs in a Hash Join. If you want to find out why the Query
Optimizer might not choose a specific join algorithm, you can use a hint to force a
specific physical join and compare the cost of both the hinted and the original plans.
Those are the foundation principles of transformation rules and, as we saw briefly earlier
in this chapter, according to the
sys.dm_exec_query_transformation_stats
DMV, SQL Server currently has 377 transformation rules, and more can be added in
future versions of the product. Just as a point of reference, a quick look at the first
Community Technology Preview (CTP) of the next version of SQL Server, code-named
"Denali," already shows 382 transformation rules included in this DMV!
So, let's go back to the
sys.dm_exec_query_transformation_stats
DMV defined
earlier and see a few examples of transformation rules used by the query processor.
Include the following query into the code in Listing 5-5 to explore the transformation
rules it uses:
