
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

SQL Server 6.5: Some useful trace flags

Alexander Chigrik
chigrik@mssqlcity.com

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 a single 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 this article for more details:
Update Methods Used in MS SQL 6.5

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.

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc