Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 10, 2000

SQL Server 6.5: Some Useful Trace Flags

By Alexander Chigrik


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" 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

1. SQL Server Books Online.

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




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM