SQL Server Trace Flags - why and how to use them
Trace flags are very powerful settings that allow you to customize SQL Server behaviour. Most of the trace flags are undocumented in Books Online and there does not exist any official list of them. Some have been mentioned in Knowledge Base articles and presentation from Microsoft employees making them somewhat official, but you use them at your own risk. So why would anyone use them? Because they give you some unique possibilities to do performance tuning, diagnostics and understanding how SQL Server works.
In an earlier blog post I described trace flag 3604, which enables you to explore SQL Server under the hood by providing you with a detailed dump of data pages so that you can see exactly how data is stored. You get a hex dump including an interpretation of the data.
How do you use a trace flag? There are several ways:
You can use the DBCC TRACEON command to enable a trace flag at session level or sever level
You can set the startup parameters for SQL Server through the xxx utility
If you start SQL Server from the command prompt (instead of as a service) you can add them as parameters
Using DBCC TRACEON
The DBCC TRACEON command is documented in Books Online. To enable a trace flag at session level (for instance traceflag 2528) you type:
DBCC TRACEON (2528)
If you instead want to enable a trace flag at server level you add a -1 as a parameter. For example if you wish to enable trace flag 2528 at server level you type:
DBCC TRACEON (2528, -1)
Using startup parameters
Some trace flags work only at session level, some only at server level and some work at either level. Some must be set as startup parameters to SQL Server. That can be done using the SQL Server Configuration Manager. Double-click on the SQL Server service (or right-click and choose Properties).
Switch to the Advanced tab and scroll down to Startup Parameters.
If you for example want to enable trace flags 2528 and 3205 you add the following to Startup Parameters:
Be careful to use an uppercase T. Using lowercase t enables other trace flags which are intended for Microsoft internal use only.
Restart SQL Server for the changes to take effect.
Also other Startup Parameters can be mentioned, such as -x which disables monitoring and performance data collection and can be used for extreme performance tuning. Another option is -E which is useful for data warehouses (where you have mostly sequential I/O access) because it makes SQL Server allocate 64 extents per data file instead of 1 extent before moving to the next file. The -E option is available on 64-bit only.
Starting SQL Server from the command prompt
SQL Server can be started manually from the command prompt. The default location of the exe-file is 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn'. If you change to that directory you can start SQL Server by typing
If you have a named instance you should type
If you want to enable any trace flags, such as 2528 you type
Checking the status of trace flags
You can check the status of your trace flags by using the following command:
The output is a table that tells you if a trace flags is enabled on session and/or global level.
Where to find out about trace flags
I've gathered some sources for trace flags. However as I said earlier there is no complete list anywhere, so finding out about trace flags is a bit like being an archeologist.
Microsoft's Knowledge Base articles are generally a good source of information about trace flags. So which trace flag is my favorite? It certainly is trace flag 3604 which is very useful if you want to learn about SQL Server in depth.