In this article I want to tell you what you should know about
trace flags, and how you can use some useful trace flags in
SQL Server 6.5 for administering and monitoring.
Trace flags are used to temporarily set specific server characteristics
or to switch off a particular behavior. You can set trace flags with
DBCC TRACEON command or with the -T option with the sqlservr command-
line executable. After activated, trace flag will be in effect until
you restart server, or until you deactivate trace flag with
DBCC TRACEOFF command.
1. Trace flag -1
This trace flag sets trace flags for all client connections, rather
than for a single client connection. Is used only when setting trace
flags using DBCC TRACEON and DBCC TRACEOFF.
2. Trace flag 105 (undocumented)
In SQL Server 6.5 you can use maximum 16 tables or subqueries in
one select statement. There is no documented way, to avoid this
restriction, but you can use undocumented trace flag 105 for this
This is the example:
3. Trace flag 302.
Very useful trace flag, if you want to see index selection information,
estimated the physical and logical I/O for the index. This trace flag
should be used with trace flag 310 to show the actual join ordering.
4. Trace flag 310
Trace flag 310 prints information about join order.
5. Trace flag 323 (undocumented)
Trace flag 323 is undocumented trace flag. You can use it if
you want to see detail description of update methods.
See my article “Update Methods Used in MS SQL 6.5”
for more details.
6. Trace flag 345 (undocumented)
This undocumented trace flag is used to increase the accuracy of choice
of optimum order when you join 6 or more tables and will be described
in my next article about “SQL Server 6.5: Nested-Loop Joins”.
7. Trace flag 1204
This trace flag returns more detailed information on the command being
executed at the time of a deadlock. Trace flag 1204 prints out the
deadlock chains and victim.
8. Trace flag 3604.
Trace flag 3604 sends trace output to the client. This trace
flag is used only when setting trace flags with DBCC TRACEON
and DBCC TRACEOFF.
1. SQL Server Books Online.
2. “Update Methods Used in MS SQL 6.5”