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:
-T2528;-T3205
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
sqlservr.exe
If you have a named instance you should type
sqlservr.exe -sInstancename
If you want to enable any trace flags, such as 2528 you type
sqlservr.exe -T2528
Checking the status of trace flags
You can check the status of your trace flags by using the following command:
DBCC TRACESTATUS
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.
The Swedish national championship for SQL Server developers, SQLug.se challenge 2010, is now finished. The competition has been running on the Swedish SQL Server User Group's website, but I publish the competition problem also on my blog in case you want to test your skills on it!
The problem comes from a real world scenario that I've faced as a Business Intelligence consultant, but the data is randomly generated. The goal of the competition was to make the fastest solution. All the entries and performance statistics are published on our user group's website.
Consider a table of meter reading from a number of vehicles. The meter readings are a running total and measure fuel consumption (in centiliters).
| ID |
Tid |
VehicleName |
MeterValue |
| 1 |
2010-02-01 08:00:00.000 |
LIFT1 |
10000 |
| 2 |
2010-02-01 08:05:00.000 |
LIFT1 |
10025 |
| 3 |
2010-02-01 08:15:00.000 |
LIFT1 |
10065 |
| 4 |
2010-02-01 08:15:00.000 |
LIFT2 |
20000 |
| 5 |
2010-02-01 08:30:00.000 |
LIFT2 |
20150 |
| 6 |
2010-02-01 09:05:00.000 |
LIFT1 |
10315 |
| 7 |
2010-02-01 09:10:00.000 |
LIFT2 |
20510 |
| 8 |
2010-02-01 09:55:00.000 |
LIFT2 |
20810 |
| 9 |
2010-02-01 10:00:00.000 |
LIFT1 |
10480 |
| 10 |
2010-02-01 10:00:00.000 |
LIFT2 |
20885 |
| 11 |
2010-02-01 10:10:00.000 |
LIFT1 |
10530 |
| 12 |
2010-02-01 10:15:00.000 |
LIFT1 |
10575 |
| 13 |
2010-02-01 11:00:00.000 |
LIFT1 |
10725 |
| 14 |
2010-02-01 11:05:00.000 |
LIFT2 |
21445 |
| 15 |
2010-02-01 11:05:00.000 |
LIFT1 |
10755 |
| 16 |
2010-02-01 11:50:00.000 |
LIFT1 |
11295 |
| 17 |
2010-02-01 12:00:00.000 |
LIFT1 |
11325 |
In this example LIFT1 consumed 25 cl between 08:00 and 08:05. The consumption rate then was 5 cl per minute.
The task is to calculate the time weighted average consumption rate, in centiliters per minute, for the 20% periods that had the highest consumption rate (round the number of periods up to closest integer so you will for example include 3 periods if there are a total of 11 periods). If more than one period has the same consumption, use the latest period.
See example below for LIFT1.
| VehicleName |
StartTime |
EndTime |
Consumption |
TimeInMinutes |
ConsumptionPerMinute |
| LIFT1 |
08:00:00 |
08:05:00 |
25 |
5 |
5 |
| LIFT1 |
08:05:00 |
08:15:00 |
40 |
10 |
4 |
| LIFT1 |
08:15:00 |
09:05:00 |
250 |
50 |
5 |
| LIFT1 |
09:05:00 |
10:00:00 |
165 |
55 |
3 |
| LIFT1 |
10:00:00 |
10:10:00 |
50 |
10 |
5 |
| LIFT1 |
10:10:00 |
10:15:00 |
45 |
5 |
9 |
| LIFT1 |
10:15:00 |
11:00:00 |
150 |
45 |
3,33333333333333 |
| LIFT1 |
11:00:00 |
11:05:00 |
30 |
5 |
6 |
| LIFT1 |
11:05:00 |
11:50:00 |
540 |
45 |
12 |
| LIFT1 |
11:50:00 |
12:00:00 |
30 |
10 |
3 |
We have a total of 10 periods so 20% would be two periods. The two periods with highest consumption rate had 12 cl/minute and 9 cl/minute and their durations were 45 and 5 minutes. The time weighted average consumption rate for LIFT1 would be:(12 x 45 + 9 x 5) / (45 + 5) = 11,7 cl/minute. Same calculation for LIFT2 gives an average consumption rate of 15 cl/minut. The result should be written to a table called ConsumptionSummary.
| VehicleName |
Top20PctAveragePerMinute |
| LIFT1 |
11,7 |
| LIFT2 |
15 |
Files
Correct results for the smaller testdata
| VehicleName |
Top20PctAveragePerMinute |
| LIFT1 |
8202.18834892846 |
| LIFT2 |
27949.9640499379 |
| LIFT3 |
13310.5810794649 |
| LIFT4 |
19097.1263597138 |
Winners of the competition were:
|
1 |
Henrik Nilsson, Brummer & Partners |
9,50 s |
|
2 |
Zhong Yu, B3IT |
14,17 s |
|
3 |
Sergey Klimkevich, Memnon Networks AB |
16,20 s |
|
4 |
Magnus Sörin, Avanade |
35,32 s |
|
5 |
Peter Larsson, Developer Workshop |
58,87 s |