fiftynine.io
Trace Flag Reference
| Trace Flag | Applicable | Dangerous | Description | Reference |
|---|---|---|---|---|
| 610 | No | Used to attempt minimally logged inserts to tables with indexes, similar to inserts with the TABLOCK hint | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 661 | Yes | Disables the ghost record removal process. This is how deleted records are removed permanently. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 845 | Maybe | Enables lock pages in memory if you’re on Standard Edition. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 1117 | < 2016 | No | Causes 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 | < 2016 | No | Removes the use of mixed extents. Most often used to help with tempdb contention. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ |
| 1204 | No | Returns resources involved with a deadlock. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 1211 | Yes | Disables 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 | No | Returns resources involved with a deadlock in XML format. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 1224 | Yes | Disables 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 | No | But someone thinks they’re a rocket scientist. Fixes a problem with locks and spinlocks in 2012⁄2014. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 1806 | Yes | Disables Instant File Initialization! Boo! Boo this man! | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 2312 | Maybe | This will make the optimizer use the 2014 cardinality estimator. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 2330 | Yes | Disables collection of index usage/missing index requests. Bad idea for 99.9% of people. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 2371 | < 2016 | No | Lowers 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 | No | CPU scheduling - place threads on the least loaded node. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 2468 | No | CPU scheduling - place threads within the same node. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 2549 | Maybe | Sometimes 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 | Maybe | Changes 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 | Maybe | Keeps 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 | No | Used 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 | Maybe | Instead of preallocating space for compressed backups, SQL will expand incrementally. Can slow backups down. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 3505 | Yes | Disables Checkpoints. Um, you want those. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 3605 | No | Used alongside 1204 and 1222 to send deadlock information to the error log. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 3801 | Probably | It prohibits running USE [database] statements. Probably helpful in Azure, but not anywhere else. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 4138 | Yes | Disables 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 | 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 4199 | No | This 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 | 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 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. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 8048 | Maybe | Changes SQL’s behavior to NUMA based partitioning by enabling SOFT NUMA when SQL’s NUMA-awareness code doesn’t work on larger systems | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 8079 | > 2014 SP2 | No | Enables Automatic SOFT NUMA partitioning | https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-2014-service-pack-2-is-now-available/ |
| 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? | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ | |
| 9481 | Maybe | This will make the optimizer use the pre-2014 cardinality estimator. | https://www.brentozar.com/blitz/trace-flags-enabled-globally/ |