Johan Åhlén

Johan Åhlén's blog about life, universe and everything.

December 2010 - Posts

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:

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

 

SQLug.se challenge 2010 - the Swedish national championship for SQL Server developers

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