Databases Reference
In-Depth Information
transformation rule is used only if certain requirements are met; for example, when
the
GROUP
BY
clause includes the joining columns, which is the case in our example.
Run the following statement to temporarily disable the use of the
GbAggBeforeJoin
transformation rule for the current session:
DBCC
RULEOFF
(
'GbAggBeforeJoin'
)
Listing 5-10.
After disabling this transformation rule and running the query again, the plan, shown
in Figure 5-3, will now show the aggregate after the join, which, according to the Query
Optimizer, is a more expensive plan. You can verify this by looking at their estimated
costs: 0.285331 and 0.312394, respectively. (These are not shown on the figures, but you
can see them by hovering the mouse over the SELECT icon and examining the Estimated
Subtree Cost value, as explained before.) Note that, for this exercise, an optimization may
need to be forced to see the new plan, perhaps using the
OPTION
(RECOMPILE)
hint or
one of the methods which we've discussed to remove the plan from the cache, like
DBCC
FREEPROCCACHE
.
Figure 5-3:
Plan with
GbAggBeforeJoin
rule disabled.
In addition, there are a couple of additional undocumented statements to show which
transformation rules are enabled and disabled, and these are
DBCC
SHOWONRULES
and
DBCC
SHOWOFFRULES
. By default,
DBCC
SHOWONRULES
will list all the 377 transformation
rules listed by the
sys.dm_exec_query_transformation_stats
DMV. To test it, try
running the code in Listing 5-11.
