SQL Server

Some useful SQL Server 2016 trace flags

Some useful SQL Server 2016 trace flags

Trace flags are used to temporarily set specific server characteristics or

to switch off a particular behavior. There are 3 types of trace flags in

SQL Server 2016:

  • query trace flags
  • global trace flags
  • session trace flags

Query trace flags are active for the context of a specific query. A global trace

flag is enabled for the entire time SQL Server 2016 service is running and is

applied to each of the client connections. After activated, global trace flag

will be in effect until you restart server, or until you deactivate trace flag

by using the DBCC TRACEOFF command. A session trace flag is only applicable to

the client connection that enables this trace flag and when the client connection

is closed, the behavior reverts back to default. You can set global or session

trace flags by using the DBCC TRACEON command or by using the -T option with the

sqlservr command-line executable. To set the query trace flag, you can use the

QUERYTRACEON query hint.

In this article, you can find the description of some useful SQL Server 2016 trace

flags. By the way, some of these trace flags are undocumented, but however may be

useful for administering and monitoring.

Enabling and disabling trace flags

You can use the DBCC TRACEON command to turn on the specified trace flag.

This is the syntax:

DBCC TRACEON (trace# [,…n][, -1]) [WITH NO_INFOMSGS]

If you want to turn off the specified trace flag(s), you can use the DBCC TRACEOFF

command. This is the syntax:

DBCC TRACEOFF (trace# [,…n] [,-1]) [WITH NO_INFOMSGS]

You can use the DBCC TRACESTATUS command to get the status information for the

particular trace flag(s) currently turned on. This is the syntax:

DBCC TRACESTATUS ([ [trace# [,…n] ] [,] [-1] ]) [WITH NO_INFOMSGS]

To get the status information for all trace flags currently turned on, you can

use -1 for trace#.

This is the example:


Useful trace flags

1. Trace flag 1204.

This trace flag returns the type of locks participating in the deadlock and the

current command affected. This trace flag is documented in SQL Server 2016. This

is global trace flag.

2. Trace flag 1205 (undocumented).

This trace flag returns more detailed information about the command being executed

at the time of a deadlock. This trace flag was documented in SQL Server 7.0 Books

Online, but is not documented in SQL Server 2016.

3. Trace flag 1211.

This trace flag disables lock escalation based on memory pressure, or based on

number of locks. If turns on, then SQL Server 2016 will not escalate row or page

locks to table locks.

Note. When the trace flag 1211 is turned on then excessive numbers of locks

can be generated.

4. Trace flag 1222.

This trace flag returns the resources and types of locks that are participating

in a deadlock and also the current command affected, in an XML format that does

not comply with any XSD schema.

5. Trace flag 1224.

This trace flag disables lock escalation based on the number of locks. If both

trace flag 1211 and 1224 are set, 1211 takes precedence over 1224.

6. Trace flag 1807 (undocumented).

You cannot create a database file on a mapped or UNC network location. This

opportunity is generally unsupported under SQL Server 2016. However, you can

bypass this by turn on trace flag 1807.

7. Trace flag 2508 (undocumented).

This trace flag disables parallel non-clustered index checking for DBCC CHECKTABLE.

8. Trace flag 2528.

This trace flag disables parallel checking of objects by DBCC CHECKDB, DBCC

CHECKFILEGROUP, and DBCC CHECKTABLE. Usually trace flags 2508 and 2528 should not

be used because checking in parallel provides better performance, but sometimes

when another process requires CPU resources during checking, you can disable

parallel checking to free some CPU resources for another process.

9. Trace flag 3205.

This trace flag disables hardware compression for tape drivers. You can use this

trace flag if your tape drives do not support compression.

10. Trace flag 3608.

This trace flag skips automatic recovery (at startup) for all databases except

the master database. To turn on this trace flag, you must use -T startup option.

For example, you should start SQL Server 2016 with the following parameter: -T 3608

To make it, run Services from the Control Panel, double click on MS SQL Server

service, click Stop button, then specify parameter value (-T 3608) and click

the Start button.

11. Trace flag 3609 (undocumented).

This trace flag skips the creation of the tempdb database at startup. To turn on

this trace flag, you must use -T startup option.

12. Trace flag 4616.

This trace flag makes server-level metadata visible to application roles.

By default, an application role cannot access metadata outside its own

database. This is global only trace flag.

13. Trace flag 6534.

This trace flag enables performance improvement of query operations with spatial

data types in SQL Server 2016. The performance gain will vary, depending on the

configuration, the types of queries, and the objects.

14. Trace flag 7806.

This trace flag enables a dedicated administrator connection on SQL Server 2016

Express. By default, no dedicated administrator connection resources are reserved

on SQL Server Express.

15. Trace flag 8721.

This trace flag reports to the error log when auto-update statistics executes.

The trace flag 8721 is global only.

16. Trace flag 8744.

This trace flag disables pre-fetching for the Nested Loop operator. Keep in mind,

that incorrect use of this trace flag may cause additional physical reads when

SQL Server executes plans that contain the Nested Loops operator.