Trace Flags Worth Knowing About

A trace flag can change engine behavior with one small number. Trace flags worth knowing about are the ones you can identify, justify, and remove when they no longer serve the workload.

Trace Flags Worth Knowing About Are Version-Specific

Trace flags are switches for particular SQL Server behaviors. Some are documented and supported for a given version; others are diagnostic or internal. A flag recommended for an older release can be built into current behavior or become unnecessary. Never move a startup list from an old server to a new one without checking the current documentation for that exact build.

I have seen inherited startup parameters treated like family heirlooms. Nobody knew why they were there, but nobody wanted to touch them. Record each flag, its purpose, owner, version guidance, and removal test. A number without that story is a maintenance liability. Which flags on your server could your team explain today?

Know Session and Global Scope

DBCC TRACEON can enable a flag for the current session. Adding the global argument affects the instance until it is disabled or restarted. Startup parameters apply when the service starts. The scope determines who is affected and how long the behavior lasts. Do not test a global flag on a shared production server because the syntax is convenient.

I use session scope for a controlled diagnostic when the flag supports it. Then I verify the state and turn it off when the test ends. An instance-wide flag needs a reviewed change and workload testing. The small command is not a small operational decision.

SELECT SERVERPROPERTY(N'ProductVersion') AS product_version;

Inspect Current Flags

DBCC TRACESTATUS reports enabled trace flags and their session or global status. Run it as part of an inherited-server inventory. Compare the result with startup parameters because a current global state does not by itself explain how the flag was enabled. Record the server build at the same time. An upgrade can change the relevance of every entry.

The command below shows all current flags. Keep the output with the configuration snapshot and review each documented purpose. I do not call a flag bad merely because it is unfamiliar. I do call an undocumented reason a gap that needs research before the next upgrade.

DBCC TRACESTATUS(-1);

Recognize Historical Trace Flags Worth Knowing About

Older guidance mentioned flags for tempdb allocation behavior, optimizer choices, and backup or diagnostic details. Some of those behaviors are now defaults or have explicit database-scoped and server configuration alternatives. That is why a static list of numbers ages poorly. The useful skill is knowing how to verify a flag’s current purpose and support status.

I keep only examples that answer a current problem on the actual build. If a flag was added after an incident, find the incident evidence and retest the original symptom without it in a safe environment. The fact that a flag has been present for years does not prove it is still helping. It proves nobody has removed it yet.

Prefer Named Configuration When Available

A documented configuration option communicates intent better than an unexplained flag number. Database-scoped configuration and ALTER DATABASE options can also limit change to the workload that needs it. Before adding a trace flag, check whether a supported named setting exists and whether its behavior matches the need. Test both performance and functional effects.

I prefer the setting that the next DBA can discover through catalog views and documentation. A trace flag belongs in the change record with exact syntax and rollback. If the only justification is that an old forum post says to use it, the research is unfinished. This is one place where copying a one-line fix can create a long-lived mystery.

Test in a Narrow Scope

Reproduce the original issue in a nonproduction environment where possible. Capture the current plan or behavior, apply the flag at the smallest supported scope, then compare under representative workload. Avoid invented performance claims. Use measurements from your server and document their collection method. Check for side effects outside the original query or feature.

I make a rollback plan before turning on a global flag. If the test fails, the team should know whether DBCC TRACEOFF is enough or whether a service restart is needed for a startup parameter. Schedule a safe window when the change affects the whole instance. A quick flag test can become a long investigation if nobody wrote down the before state.

Verify After Restart

A session flag ends with its session. A global flag enabled by command does not necessarily survive a restart. A startup parameter does. After maintenance, inspect current status again and compare with the approved list. That catches missing intended flags and old flags left in startup configuration. It also confirms that the instance started with the expected options.

I include trace flags in post-upgrade checks. Version changes can alter whether a flag remains supported or useful. The review should happen before the upgrade and after it, not only when a query starts behaving oddly. The best flag is one whose reason and lifecycle are documented.

Keep a Removal Date

For each nonstandard flag, record why it was added, the affected workload, the evidence of benefit, the owner, and the date to review removal. A permanent exception can still be valid, but it should earn that status. If the original symptom disappeared through an application fix, the flag deserves another test.

I have seen a long startup list survive three hardware replacements. It was impressive in the same way an attic full of old cables is impressive. A periodic review keeps only the switches that still have a job. That makes upgrades safer and troubleshooting clearer.

Use Current Documentation for Trace Flags Worth Knowing About

Before enabling any specific number, read the Microsoft documentation for your SQL Server version and support policy. Check whether the flag is startup-only, global-only, or session-capable. Verify interactions with database compatibility level and newer configuration options. Do not rely on a copied list without release context.

A post about trace flags worth knowing about should teach caution without fear. They are valid tools when documented and tested for a real need. The operational failure is leaving a flag in place without knowing what it changes. Keep the list of trace flags worth knowing about short, readable, and attached to evidence from your own instance.

Related reading on this blog: Checking Traceflag Status with TRACESTATUS and Trace Flags 1117 and 1118 No Longer Required since SQL Server 2016.

A trace flag is not a tuning badge, it is a scoped change with a reason and a review date.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.