github twitter linkedin email
Trace Flag Reference

Trace FlagApplicableDangerousDescriptionReference
610 NoUsed to attempt minimally logged inserts to tables with indexes, similar to inserts with the TABLOCK hint
652 YesDisables 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 YesDisables the ghost record removal process. This is how deleted records are removed permanently.
834 MaybeUses large page allocations for the buffer pool. Has been known to prevent SQL from starting up, or being very slow to start up.
845 MaybeEnables lock pages in memory if you’re on Standard Edition.
1117< 2016NoCauses files in a filegroup to grow at the same time. Most often used for tempdb, but affects all databases.
1118< 2016NoRemoves the use of mixed extents. Most often used to help with tempdb contention.
1204 NoReturns resources involved with a deadlock.
1211 YesDisables lock escalation if there’s memory pressure on your system. Takes precedence over 1224 if both are enabled.
1222 NoReturns resources involved with a deadlock in XML format.
1224 YesDisables lock escalation based on the number of locks. 1211 takes precedence if both are enabled.
1236 NoBut someone thinks they’re a rocket scientist. Fixes a problem with locks and spinlocks in 20122014.
1806 YesDisables Instant File Initialization! Boo! Boo this man!
2312 MaybeThis will make the optimizer use the 2014 cardinality estimator.
2330 YesDisables collection of index usage/missing index requests. Bad idea for 99.9% of people.
2371< 2016NoLowers the threshold for automatic statistics updates to occur based on table size. Good for VLDBs.
2467 NoCPU scheduling - place threads on the least loaded node.
2468 NoCPU scheduling - place threads within the same node.
2549 MaybeSometimes used to help efficiency of DBCC CHECKDB for files on multiple disks. Won’t help otherwise.
2562 MaybeChanges the behavior of DBCC CHECKDB to run in one batch. Don’t use this if tempdb is a problem area for you.
2861 MaybeKeeps zero cost plans in cache. Often enabled by some monitoring products that want to monitor the impact of even lightweight queries.
3023 NoUsed to enable checksums for backups, if you can’t enable them through a 3rd party tool.
3042 MaybeInstead of preallocating space for compressed backups, SQL will expand incrementally. Can slow backups down.
3226 NoStops 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 YesDisables Checkpoints. Um, you want those.
3605 NoUsed alongside 1204 and 1222 to send deadlock information to the error log.
3801 ProbablyIt prohibits running USE [database] statements. Probably helpful in Azure, but not anywhere else.
4101-4135 NoTrace 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 ProbablyThis 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 ProbablyChanges 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 YesDisables all row goal optimizations. You probably want these. What’s life without goals? Basically college.
4139 NoCan 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 NoThis is a bucket trace flag to enable optimizer fixes in SPs/CUs that are enabled by separate trace flags.
7471 MaybeThis causes deadlocks if you’re creating and updating statistics simultaneously. Only turn it on if you’re updating stats with multiple parallel jobs.
8015 MaybeSomeone thinks they’re a rocket scientist. This will make SQL ignore the NUMA setup of a server and manage the nodes as one.
8048 MaybeChanges SQL’s behavior to NUMA based partitioning by enabling SOFT NUMA when SQL’s NUMA-awareness code doesn’t work on larger systems
8079> 2014 SP2NoEnables Automatic SOFT NUMA partitioning
8649 YesThis 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 MaybeThis will make the optimizer use the pre-2014 cardinality estimator.