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.
Prev. page
[1]
2
next page 
