SQL Server Trace Flags: How to Use Them and Which Ones Still Matter

Trace flags are switches that change specific SQL Server behaviors: what gets written to the error log, how the query optimizer behaves, how locks escalate. Many DBAs inherit servers with a list of trace flags in the startup parameters that nobody can explain anymore. Some of them still matter, some have done nothing for years, and a few should never be on permanently.

This guide explains how trace flags work and how they differ from other SQL Server settings, how to turn them on safely and make them permanent, which trace flags are still worth knowing, which ones you can retire, and how to audit what’s enabled on your servers.

Trace Flags vs. Other SQL Server Settings

“SQL Server flags” can mean several different things. They’re configured in different places and behave differently:

Since SQL Server 2016, many behaviors that once needed a trace flag have become regular options, at the database or query level. When an option exists, it’s the better choice: it’s visible, documented in the database itself and doesn’t affect the whole instance.

Trace Flag Scopes: Global, Session and Query

A trace flag works at one of three scopes:

• Global: affects every connection on the instance. Turn it on with DBCC TRACEON and the -1 argument, or with the -T startup parameter.
• Session: affects only the current connection. Use DBCC TRACEON without -1; the flag is gone when the session ends.
• Query: affects a single statement through the QUERYTRACEON hint. This works only for the query optimizer trace flags that Microsoft documents for it.

Not every flag supports every scope. Some, such as 3226 or 1222, work only globally.

-- Turn a trace flag on globally (until the next restart)
DBCC TRACEON (3226, -1);

-- Turn it off again
DBCC TRACEOFF (3226, -1);

-- List all globally enabled trace flags
DBCC TRACESTATUS (-1);

-- Query scope: only for supported query optimizer trace flags
SELECT OrderID, Total
FROM dbo.Orders
WHERE CustomerID = 42
OPTION (QUERYTRACEON 4199);

How to Make a Trace Flag Permanent

A flag enabled with DBCC TRACEON disappears at the next restart, including an unplanned failover. To keep it, add it as a startup parameter:

1. Open SQL Server Configuration Manager, right-click the SQL Server service and choose Properties.
2. On the Startup Parameters tab, add -T3226 (one parameter per flag) and click Add.
3. Restart the service, or also run DBCC TRACEON (3226, -1) so the flag takes effect now.

On Linux, use mssql-conf, for example sudo /opt/mssql/bin/mssql-conf traceflag 3226 on, then restart the service. In a failover cluster or an availability group, add the same startup parameters on every node, or behavior will change after a failover.

Trace Flags Still Worth Knowing

Trace Flags You Can Retire

Many trace flag lists online still recommend flags that stopped doing anything years ago. If your server runs SQL Server 2016 or later, these are candidates for removal:

Prefer Database Scoped Configurations and Query Hints

When a behavior has a documented option, use it instead of a trace flag. A database scoped configuration affects only one database and is visible in sys.database_scoped_configurations; a USE HINT affects only one query and documents itself in the code:

-- Instead of trace flag 4199 for the whole instance
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

-- Or only for one query
SELECT OrderID, Total
FROM dbo.Orders
WHERE CustomerID = 42
OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));

-- See what is set for the current database
SELECT name, value
FROM sys.database_scoped_configurations
ORDER BY name;

How to Audit Trace Flags on Your Servers

Compare what’s running now with what will be running after the next restart. They often differ, because someone enabled a flag with DBCC TRACEON and never added it to the startup parameters, or the other way round:

-- What is active right now
DBCC TRACESTATUS (-1);

-- What the service starts with (startup parameters such as -T3226)
SELECT value_name, value_data
FROM sys.dm_server_registry
WHERE value_name LIKE N'SQLArg%';

For every flag you find, answer three questions: what does it do on this version, is there a documented option that replaces it, and who decided to enable it and why. Keep the answers in your server documentation. As with any configuration change, test before changing production.