Databases Reference
In-Depth Information
In our case, it will show that only one rule has been disabled:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Rules that are off globally:
GbAggBeforeJoin
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Listing 5-14.
To continue with our example of the effects of the transformation rules, we can disable
the use of a Merge Join by disabling the rule
JNtoSM
(Join to Sort Merge Join) by running
the following code:
DBCC
RULEOFF
(
'JNtoSM'
)
Listing 5-15.
If you have followed the example, this time
DBCC
RULEOFF
will show some output
indicating that the rule is off for some specific
SPID
. Running the sample query again will
give us this totally new plan, using both a Hash Join and a Hash Aggregate, as shown in
Figure 5-4.
Figure 5-4:
Plan with
JNtoSM
rule disabled.
In
Chapter 7, Hints
, you will learn how to obtain this same behavior in your queries using
(unsurprisingly) hints.
