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.