SQL Server 6.5: Some Useful Trace Flags

Introduction

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" http://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

2. "Update Methods Used in MS SQL 6.5"
http://www.databasejournal.com/features/mssql/article.php/1442311

» See All Articles by Columnist Alexander Chigrik