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 hinthttps://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
661 YesDisables the ghost record removal process. This is how deleted records are removed permanently.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
845 MaybeEnables lock pages in memory if you’re on Standard Edition.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
1117< 2016NoCauses files in a filegroup to grow at the same time. Most often used for tempdb, but affects all databases.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
1118< 2016NoRemoves the use of mixed extents. Most often used to help with tempdb contention.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
1204 NoReturns resources involved with a deadlock.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
1211 YesDisables lock escalation if there’s memory pressure on your system. Takes precedence over 1224 if both are enabled.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
1222 NoReturns resources involved with a deadlock in XML format.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
1224 YesDisables lock escalation based on the number of locks. 1211 takes precedence if both are enabled.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
1236 NoBut someone thinks they’re a rocket scientist. Fixes a problem with locks and spinlocks in 20122014.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
1806 YesDisables Instant File Initialization! Boo! Boo this man!https://www.brentozar.com/blitz/trace-flags-enabled-globally/
2312 MaybeThis will make the optimizer use the 2014 cardinality estimator.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
2330 YesDisables collection of index usage/missing index requests. Bad idea for 99.9% of people.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
2371< 2016NoLowers the threshold for automatic statistics updates to occur based on table size. Good for VLDBs.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
2467 NoCPU scheduling - place threads on the least loaded node.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
2468 NoCPU scheduling - place threads within the same node.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
2549 MaybeSometimes used to help efficiency of DBCC CHECKDB for files on multiple disks. Won’t help otherwise.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
2562 MaybeChanges the behavior of DBCC CHECKDB to run in one batch. Don’t use this if tempdb is a problem area for you.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
2861 MaybeKeeps zero cost plans in cache. Often enabled by some monitoring products that want to monitor the impact of even lightweight queries.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
3023 NoUsed to enable checksums for backups, if you can’t enable them through a 3rd party tool.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
3042 MaybeInstead of preallocating space for compressed backups, SQL will expand incrementally. Can slow backups down.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
3505 YesDisables Checkpoints. Um, you want those.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
3605 NoUsed alongside 1204 and 1222 to send deadlock information to the error log.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
3801 ProbablyIt prohibits running USE [database] statements. Probably helpful in Azure, but not anywhere else.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
4138 YesDisables all row goal optimizations. You probably want these. What’s life without goals? Basically college.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
4199 NoThis is a bucket trace flag to enable optimizer fixes in SPs/CUs that are enabled by separate trace flags.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
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.https://www.brentozar.com/blitz/trace-flags-enabled-globally/
8048 MaybeChanges SQL’s behavior to NUMA based partitioning by enabling SOFT NUMA when SQL’s NUMA-awareness code doesn’t work on larger systemshttps://www.brentozar.com/blitz/trace-flags-enabled-globally/
8079> 2014 SP2NoEnables Automatic SOFT NUMA partitioninghttps://blogs.msdn.microsoft.com/sqlreleaseservices/sql-2014-service-pack-2-is-now-available/
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?https://www.brentozar.com/blitz/trace-flags-enabled-globally/
9481 MaybeThis will make the optimizer use the pre-2014 cardinality estimator.https://www.brentozar.com/blitz/trace-flags-enabled-globally/