sp_Blitz Result: Trace Flags Enabled Globally

SQL Server has configuration knobs called trace flags that you can turn off and on – either at the session level for experimentation, or at the server level.  Trace flags are typically used for diagnostic purposes and they may need to be disabled once the issue is corrected. This check is to alert you of any globally enabled trace flags so that it can be determined if they are still necessary.

This part of our SQL Server sp_Blitz script runs DBCC TRACESTATUS(-1) to get the list of globally enabled trace flags.

Below is a list of commonly used Trace Flags, and what they do. Of course, by “Freak Out”, we mean “find the person who enabled it and have a calm and rational discussion with them about why they did that while you walk them out the door.”

Special thanks to Man Of Mystery® Joe Obbish for filling in blanks on some of the 41XX Trace Flags.

Trace FlagFreak Out?Effect
460NoFixes “string or binary data would be truncated” error, but make sure you patch
610NoUsed to attempt minimally logged inserts to tables with indexes, similar to inserts with the TABLOCK hint
652YesDisables page pre-fetching in scans. Under most circumstances you want SQL to read pages that will be touched by a scan into memory as soon as possible.
661YesDisables the ghost record removal process. This is how deleted records are removed permanently.
834MaybeUses large page allocations for the buffer pool. Has been known to prevent SQL from starting up, or being very slow to start up.
845MaybeEnables lock pages in memory if you’re on Standard Edition.
1117NoCauses files in a filegroup to grow at the same time. Most often used for tempdb, but affects all databases.
1118NoRemoves the use of mixed extents. Most often used to help with tempdb contention.
1204NoReturns resources involved with a deadlock.
1211YesDisables lock escalation if there’s memory pressure on your system. Takes precedence over 1224 if both are enabled.
1222NoReturns resources involved with a deadlock in XML format.
1224YesDisables lock escalation based on the number of locks. 1211 takes precedence if both are enabled.
1236NoBut someone thinks they’re a rocket scientist. Fixes a problem with locks and spinlocks in 2012/2014.
1806YesDisables Instant File Initialization! Boo! Boo this man!
2312MaybeThis will make the optimizer use the 2014 cardinality estimator.
2330YesDisables collection of index usage/missing index requests. Bad idea for 99.9% of people.
2371NoLowers the threshold for automatic statistics updates to occur based on table size. Good for VLDBs.
2467NoCPU scheduling – place threads on the least loaded node.
2468NoCPU scheduling – place threads within the same node.
2549MaybeSometimes used to help efficiency of DBCC CHECKDB for files on multiple disks. Won’t help otherwise.
2562MaybeChanges the behavior of DBCC CHECKDB to run in one batch. Don’t use this if tempdb is a problem area for you.
2861MaybeKeeps zero cost plans in cache. Often enabled by some monitoring products that want to monitor the impact of even lightweight queries.
3023NoUsed to enable checksums for backups, if you can’t enable them through a 3rd party tool.
3042MaybeInstead of preallocating space for compressed backups, SQL will expand incrementally. Can slow backups down.
3226NoStops logging all of your backup success entries to the error log. Often seen when doing transaction log backups on hundreds or thousands of databases.
3505YesDisables Checkpoints. Um, you want those.
3605NoUsed alongside 1204 and 1222 to send deadlock information to the error log.
3801ProbablyThis gets the only* probably on the list. It prohibits running USE [database] statements. Probably helpful in Azure, but not anywhere else.
4101-4135NoTrace flags that start with 41 are usually optimizer/performance fixes. You may want to just enable 4199 if you’re using a lot of them.
4136ProbablyThis Trace Flag disables parameter sniffing, and should only be used in carefully chosen and monitored environments. If you’re on 2016+, you’re better off using the PARAMETER_SNIFFING Database Scoped Configuration.
4137ProbablyChanges how some cardinality estimates are calculated (think queries with lots of ANDs in them). It may help in some cases. See here for more details.
4138YesDisables all row goal optimizations. You probably want these. What’s life without goals? Basically college.
4139NoCan be useful in sub-2014 versions of SQL Server for fighting ascending key problems. You may need Trace Flags 2389 and 2390 as well.
4199NoThis is a bucket trace flag to enable optimizer fixes in SPs/CUs that are enabled by separate trace flags.
7471MaybeThis causes deadlocks if you’re creating and updating statistics simultaneously. Only turn it on if you’re updating stats with multiple parallel jobs.
8015MaybeSomeone thinks they’re a rocket scientist. This will make SQL ignore the NUMA setup of a server and manage the nodes as one.
8048MaybeSomeone thinks they’re a rocket scientist. Changes SQL’s behavior to NUMA based partitioning.
8649YesThis drops cost threshold for parallelism to 0. It’s used as a query hint ON DEV SERVERS to troubleshoot perf issues. It looks like someone may have enabled it globally. How are your CPUs doing?
9481MaybeThis will make the optimizer use the pre-2014 cardinality estimator.

To Fix the Problem

If it is determined that the trace flag is no longer necessary, the following command can be run to turn off the trace flag one time:

DBCC TRACEOFF (traceflag, -1)

But if the trace flag is configured as part of SQL Server’s startup options, you’ll need to use SQL Server Configuration Manager to look at the startup parameters.

Return to sp_Blitz or Ask Us Questions

*=Prior to updates regarding 4136 and 4137.