- Introduction
- Trace flags
- Literature
Introduction
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.
Trace flags
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
purpose.
This is the example:
USE pubs
GO
DBCC TRACEON (105)
GO
SELECT
au_id,
(SELECT au_fname FROM authors WHERE au_id = q_1.au_id) AS q_2,
(SELECT au_lname FROM authors WHERE au_id = q_1.au_id) AS q_3,
(SELECT count(au_id) FROM authors) AS q_4,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_5,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_6,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_7,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_8,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_9,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_10,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_11,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_12,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_13,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_14,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_15,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_16,
(SELECT au_id FROM authors WHERE au_id = q_1.au_id) AS q_17
FROM authors q_1
GO
DBCC TRACEOFF (105)
GO
|
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”
https://www.databasejournal.com/features/mssql/article.php/1442311
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.
Literature
1. SQL Server Books Online.
2. “Update Methods Used in MS SQL 6.5”
https://www.databasejournal.com/features/mssql/article.php/1442311
»
See All Articles by Columnist Alexander Chigrik
