Introduction
Microsoft defines the trace flags as:
Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior.
The purpose of trace flags is to change a SQL Server default behavior. The context they apply to can be global, session or query and it depends on the nature of the flag. For example:
Flag 3226: By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic. >With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries. Scope: global only.
9349 Disables batch mode for top N sort operator. SQL Server 2016 introduces a new batch mode top sort operator that boosts performance for many analytical queries. Scope: global or session or query
WARNING: There a lot of different kind of flags, official and unofficial. But be aware that some might be counter-productive in your system. Before enabling a flag in production, always test before!
The global flags are enabled until the SQL Server is stopped. To keep the flags enabled after a restart, they should be configure in SQL Server Configuration Manager, more info in: Configure Server Startup Options
After the change is applied, the server must be restarted.
Example disabling index hints
I've a legacy Axapta application that uses query index hints and very often it uses the wrong indexes. I've found a query with an index hint that takes 3min40sec and if I remove it, the query just runs in 3 seconds. The solution found was to activate the flag 8602: Ignore index hints that are specified in query/procedure.
To show all the flags affecting the session and the global (all connections!):
DBCC TRACESTATUS(-1)
Result message: DBCC execution completed. If DBCC printed error messages, contact your system administrator.
That's OK. There're no flags enabled yet.
To enable the flag 8602 for the current session:
DBCC TRACEON(8602)
Checking it:
DBCC TRACESTATUS(-1)
To enable the flag for the global scope:
DBCC TRACEON(8602,-1)
To show all flags affecting the current session and all the global scope:
DBCC TRACESTATUS(-1)
Be aware that now the flag is affecting the current session and all the global. When we disable the flag for the current session the effect of the flag will persist in the current session because it's still enabled at global level:
DBCC TRACEOFF(8602)
DBCC TRACESTATUS(-1)
DBCC TRACEOFF(8602,-1)
Result message: DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There's the option to activate a flag for a simple query:
SELECT x FROM myTable WHERE y = 2 AND z = 10 OPTION (QUERYTRACEON 8602)
Webpages of interest:
- DBCC TRACESTATUS
- DBCC TRACEON
- DBCC TRACEOFF
- List of all documented Trace Flags
- SQL Server Trace Flags - Complete list: It's a more complete list. The 8602 flag appears here, not in the msdn prior link. It has links to additional information (although some of them are no longer available).
- SQL Server Trace Flags: Another full list of flags.
