I frequently see requests on public forums and in private email from SQL Server developers and administrators asking for information about trace flags. Sometimes the request is specific, asking for details about using a particular trace flag number; other times, the plea is broader. I've even received email asking, "Can you send me a list of all the trace flags in SQL Server?" Trace flags are used to generate internal information regarding SQL Server's activities, primarily so that Microsoft developers can troubleshoot their code. You can also use trace flags to temporarily set specific SQL Server characteristics or to switch off a particular SQL Server behavior. However, to protect users from accidentally changing system behavior in undesirable ways, Microsoft doesn't document the existence of most trace flags and discourages their use.
If you're looking for a list of undocumented trace flags, I'm sorry to disappoint you. This article is about trace flags in general, although I mention some specific trace flags, both documented and undocumented. I also tell you how to enable and disable trace flags and provide a few cautions about their use. If you want a list of flags, you can refer to books by SQL Server expert Ken Henderson—in particular The Guru's Guide to Transact-SQL and The Guru's Guide to SQL Server Stored Procedures, XML and HTML, both published by Addison-Wesley. However, Henderson's books have little explanation of why you'd use the flags he's listed. Many undocumented trace flags are mentioned in newsgroups and other help forums by SQL Server users who've been instructed by Microsoft support engineers to use them to help track down a problem. But the people posting don't always understand all the repercussions of using the flags, so use extreme caution when enabling any trace flag.
Microsoft has also created dozens of undocumented Database Consistency Checker (DBCC) commands, many of which (like trace flags) provide useful internal information about SQL Server. However, unlike trace flags, the DBCC commands primarily report information only about the status of SQL Server at the time you run the command. Conversely, you must enable a trace flag, and it stays enabled until you explicitly disable it. Many trace flags change SQL Server's behavior, possibly causing unintended side effects or performance degradation. So make sure you know exactly what you're doing when you enable a trace flag.
Having given all the warnings, now I can get to the good stuff. Several trace flags are very useful; I use some of them regularly. Some are most valuable when you use them from one connection during testing, and others are best used when enabled serverwide.
You can enable trace flags in four ways, only one of which lets you turn a flag on for just one SQL Server connection. And as I explain shortly, it might be harder than you expect to keep a single-connection trace flag from affecting other connections as well. As you can imagine, only administrators can enable trace flags.
Using DBCC TRACEON
Suppose your developers have used index hints in a large number of queries and even in stored procedures. You suspect that many of the hints might be slowing queries down rather than improving their performance. Trace flag 8602 instructs SQL Server to ignore all index hints. Note that when you use 8602 as the only parameter to DBCC TRACEON, it applies only to queries running on the same connection you ran the DBCC TRACEON command on. Here's the command to turn on the trace flag:
DBCC TRACEON(8602)
You can turn on multiple trace flags in the same command. For example, trace flag 8755 disables all locking hints, and trace flag 8722 disables all other types of hints (primarily hints used in the OPTION clause). To disable all hints, you could enable all three flags with the command
DBCC TRACEON(8602, 8755, 8722)
If one of the arguments is -1 instead of a trace flag number, SQL Server applies the listed trace flags to all connections to that SQL Server, including open connections.
To demonstrate the use of trace flag 8602, here's an example that uses the Northwind database. In the query plan for the following query, SQL Server uses an index on the ProductID column:
SELECT * FROM \[order details\] WHERE ProductID = 6
However, if you change the ProductID value you're looking for to 59 and look at the query plan, you'll see that SQL Server performs a clustered index scan—that is, it searches the whole table. If you think SQL Server should use a specific index instead of searching the whole table, you can add an index hint to the query:
SELECT * FROM \[order details\] (index = ProductID) WHERE ProductID = 59
However, this hint makes the query perform worse. A developer who didn't test the query's performance thoroughly after adding the hint might not realize that fact. Or possibly, the hint improved performance when the code was first written, but after more data was added to the table, this hinted index is no longer the best choice.
If you're concerned that too many index hints are hurting the application's performance, you can test the performance of your crucial queries and procedures by using trace flag 8602 to enable and disable the hints without having to change any code. Enable trace flag 8602, then run this query again:
SELECT * FROM \[order details\] (index = ProductID) WHERE ProductID = 59
Now, the query plan shows that SQL Server doesn't use the nonclustered index on ProductID, and the query performs better than it did without the trace flag.
This trace flag is useful because it lets you determine what would happen if you changed the code and removed the hints, without you actually having to change the code. Only if you determine that the index hints are causing worse performance should you change the code to remove them.
Three Other Ways
The second method for enabling one or more trace flags is to use Enterprise Manager. Right-click your server's name and choose Properties, then on the General tab, click the Startup parameters button. Type Txxxx in the parameter box, where xxxx is the trace flag number you're enabling, and click Add. You must then stop and restart SQL Server before the trace flag takes effect. After you've entered a trace flag number into the startup parameters list, SQL Server will apply it at every startup until you return to the Properties dialog box and remove it. You can access this same dialog box and all the Enterprise Manager icons from the left pane through Windows 2000's Computer Management console.
To demonstrate the third method of enabling trace flags, let's use one of the few documented trace flags, 1204, which is commonly used serverwide. Every time a deadlock occurs, this flag writes information to the SQL Server error log, including the processes, objects, and SQL queries involved in the deadlock. If you notice deadlocks occurring, enabling this flag serverwide is one of the first troubleshooting steps to take. For information about interpreting this trace flag's output, see the SQL Server Books Online (BOL) article "Troubleshooting Deadlocks."
To use the third method of enabling trace flags, you need to start SQL Server from a command prompt by invoking the SQL Server executable. This method is almost always used to enable the trace flag only for short-term testing. Here's an example of how to start a default SQL Server 2000 instance with the deadlock trace flag and trace flag 3605 enabled. Trace flag 3605 directs SQL Server to send the other trace flag's output to the error log (the output is usually written to the 'console' command prompt):
C:\Program Files\Microsoft SQL ServerMSSQL\binn\sqlservr T1204 T3605
Note that you have to change the directory if your SQL Server isn't a default instance or wasn't installed in the default directory.
The fourth method is rarely used because it requires a lot more effort than the others and then isn't maintained after you stop and restart the SQL Server service. From the Services icon in Control Panel if you're using Windows NT 4.0, or from the Computer Management application in Win2K, you can find the entry for the SQL Server service. For SQL Server 7.0 and the default instance of SQL Server 2000, the service name is MSSQLSERVER. Double-clicking this service name brings up a dialog box that lets you supply a parameter, such as a trace flag, as long as the service is stopped. When you restart the service, the flag takes effect, but once you close the dialog box, the parameter value is lost.
Tracking Trace Flags
The last three methods of enabling trace flags require a parameter of -T to indicate that a trace flag is being set. SQL Server accepts a lowercase t (-t); however, -t sets other internal trace flags (besides the ones you intend to set) that SQL Server support engineers need for troubleshooting, so don't use it. The last three methods also apply the trace flags you specify serverwide.
The first method, DBCC TRACEON, can apply a trace flag serverwide if you add the parameter -1. But as I mentioned, it's difficult to have a single-session trace flag. SQL Server maintains a big bitmap to track whether each possible trace flag has been set, and for each trace flag, there's only one bit for the whole server. The bitmap also contains a bit for each connection, indicating whether any trace flags have been set for the connection. If one connection's bit is set, any flags that have been set for any other connection are available to the first connection. So you can't have just one trace flag enabled in one session and a different trace flag in another.
To see this behavior for yourself, you can use the DBCC command TRACESTATUS. With a parameter of -1, this command shows you any trace flags in effect for the connection you run it in:
DBCC TRACESTATUS(-1)
If you've enabled the deadlock trace flag 1204 serverwide, TRACESTATUS will show that 1204 is enabled for whatever connection you're using. Here's an example. Make sure that trace flag 1204 isn't enabled. Open Query Analyzer and execute DBCC TRACESTATUS(-1) to verify that no trace flags are enabled. You should see this message:
Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
If you find that trace flag 1204 is on, you can disable it by using the command DBCC TRACEOFF(1204). Now enable 8602, then run TRACESTATUS again:
DBCC TRACEON(8602) GO DBCC TRACESTATUS(-1)
You should see this output:
TraceFlag Status ----- ----- 8602 1
In a second window (a new connection), verify that no trace flags are enabled. Then, enable 8722 and run TRACESTATUS again:
DBCC TRACEON(8722) GO DBCC TRACESTATUS(-1)
This time, you should see that both 8722 and 8602 are enabled:
TraceFlag Status ----- ----- 8602 1 8722 1
If you return to the first connection and run DBCC TRACESTATUS(-1), you'll see that now that session has both flags enabled also. As soon as a connection has one flag enabled, all flags enabled for any other connections also apply. This behavior can be particularly overwhelming when you've globally enabled a flag such as 1204. In that case, every connection always has a flag enabled, so any additional trace flag that anybody turns on for any connection on the server will apply to all connections. I recommend that you always assume that any trace flag you enable might end up applying to every connection. If you don't want that behavior, don't use trace flags.
Trace flags can be useful for certain well-defined scenarios, mainly involving troubleshooting; they should never be something you play with just to see what they do. And always make sure you disable any trace flags when you're finished with your testing.
