Databases Reference
In-Depth Information
ProjectToComputeScalar
2
SelIdxToRng
6
SELonJN
1
Table 5-6.
Now, (as I will explain in more detail in
Chapter 7, Hints)
hints may disable some of these
transformation rules for the duration of a query in order to obtain a specific desired
behavior. As a way of experimenting with the effects of these rules, you can also use
the undocumented statements
DBCC
RULEON
and
DBCC
RULEOFF
to enable or disable
transformation rules, and thereby get additional insight into how the Query Optimizer
works. However, before you do that, first be warned that, since these statements impact
the entire optimization process performed by the Query Optimizer, they should be used
only in a test system for experimentation purposes.
To demonstrate the effects of these statements, the query in Listing 5-9 shows the plan
seen below in Figure 5-2.
Figure 5-2:
Original execution plan.
Here you can see, among other things, that SQL Server is pushing an aggregate below
the join (a Stream Aggregate before the Merge Join). The Query Optimizer can push
aggregations that significantly reduce cardinality estimation as early in the plan as
possible. This is performed by the transformation rule
GbAggBeforeJoin
(or
Group
By Aggregate Before Join
), which is included in the output of Table 5-6. This specific
