The Optimization Process - Inside the SQL Server Query Optimizer - page 155

DBCC TRACEON ( 3604 )
DBCC SHOWONRULES
Listing 5-11.
We start this exercise with the DBCC TRACEON (3604) command, which enables trace
flag 3604, and instructs SQL Server to send the results to the client, in this case, your
Management Studio session. That means that, in this exercise, the output of the DBCC
SHOWONRULES , and later DBCC SHOWOFFRULES , DBCC RULEON and DBCC RULEOFF
statements will be conveniently available to us. An output similar to Listing 5-12 will be
displayed in this first instance (only 12 rules out of 376 rules are shown here, to preserve
space). The previously disabled rule will not be shown in this output.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Rules that are on globally:
JNtoNL
LOJNtoNL
LSJNtoNL
LASJNtoNL
JNtoSM
FOJNtoSM
LOJNtoSM
ROJNtoSM
LSJNtoSM
RSJNtoSM
LASJNtoSM
RASJNtoSM
…
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Listing 5-12.
In the same way, the following code will show the rules that are disabled:
DBCC SHOWOFFRULES
Listing 5-13.