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 Flag	Freak Out?	Effect
460	No	Fixes “string or binary data would be truncated” error, but make sure you patch
610	No	Used to attempt minimally logged inserts to tables with indexes, similar to inserts with the TABLOCK hint
652	Yes	Disables 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.
661	Yes	Disables the ghost record removal process. This is how deleted records are removed permanently.
834	Maybe	Uses large page allocations for the buffer pool. Has been known to prevent SQL from starting up, or being very slow to start up.
845	Maybe	Enables lock pages in memory if you’re on Standard Edition.
1117	No	Causes files in a filegroup to grow at the same time. Most often used for tempdb, but affects all databases.
1118	No	Removes the use of mixed extents. Most often used to help with tempdb contention.
1204	No	Returns resources involved with a deadlock.
1211	Yes	Disables lock escalation if there’s memory pressure on your system. Takes precedence over 1224 if both are enabled.
1222	No	Returns resources involved with a deadlock in XML format.
1224	Yes	Disables lock escalation based on the number of locks. 1211 takes precedence if both are enabled.
1236	No	But someone thinks they’re a rocket scientist. Fixes a problem with locks and spinlocks in 2012/2014.
1806	Yes	Disables Instant File Initialization! Boo! Boo this man!
2312	Maybe	This will make the optimizer use the 2014 cardinality estimator.
2330	Yes	Disables collection of index usage/missing index requests. Bad idea for 99.9% of people.
2371	No	Lowers the threshold for automatic statistics updates to occur based on table size. Good for VLDBs.
2467	No	CPU scheduling – place threads on the least loaded node.
2468	No	CPU scheduling – place threads within the same node.
2549	Maybe	Sometimes used to help efficiency of DBCC CHECKDB for files on multiple disks. Won’t help otherwise.
2562	Maybe	Changes the behavior of DBCC CHECKDB to run in one batch. Don’t use this if tempdb is a problem area for you.
2861	Maybe	Keeps zero cost plans in cache. Often enabled by some monitoring products that want to monitor the impact of even lightweight queries.
3023	No	Used to enable checksums for backups, if you can’t enable them through a 3rd party tool.
3042	Maybe	Instead of preallocating space for compressed backups, SQL will expand incrementally. Can slow backups down.
3226	No	Stops logging all of your backup success entries to the error log. Often seen when doing transaction log backups on hundreds or thousands of databases.
3505	Yes	Disables Checkpoints. Um, you want those.
3605	No	Used alongside 1204 and 1222 to send deadlock information to the error log.
3801	Probably	This gets the only* probably on the list. It prohibits running USE [database] statements. Probably helpful in Azure, but not anywhere else.
4101-4135	No	Trace 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.
4136	Probably	This 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.
4137	Probably	Changes 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.
4138	Yes	Disables all row goal optimizations. You probably want these. What’s life without goals? Basically college.
4139	No	Can be useful in sub-2014 versions of SQL Server for fighting ascending key problems. You may need Trace Flags 2389 and 2390 as well.
4199	No	This is a bucket trace flag to enable optimizer fixes in SPs/CUs that are enabled by separate trace flags.
7471	Maybe	This causes deadlocks if you’re creating and updating statistics simultaneously. Only turn it on if you’re updating stats with multiple parallel jobs.
8015	Maybe	Someone thinks they’re a rocket scientist. This will make SQL ignore the NUMA setup of a server and manage the nodes as one.
8048	Maybe	Someone thinks they’re a rocket scientist. Changes SQL’s behavior to NUMA based partitioning.
8649	Yes	This 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?
9481	Maybe	This 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.