SQL Server trace flags for performance: reference
Enable, list and persist SQL Server trace flags, with a table of common flags and their caveats.
Reference list of SQL Server trace flags often met in performance and troubleshooting work, with how to enable them and which ones deserve caution. See the Microsoft documentation on trace flags for the authoritative list per version.
Enable, list and persist trace flags
-- Session level: cleared when the session closes
DBCC TRACEON (3226);
-- Global (-1): applies to all sessions
DBCC TRACEON (1117, 1118, 2371, 3226, 4199, -1);
-- Show the trace flags enabled globally
DBCC TRACESTATUS (-1);
Trace flags enabled with DBCC TRACEON are cleared when SQL Server restarts. To persist them, add them as startup parameters (-T<flag>) in SQL Server Configuration Manager.
Commonly used flags
The "Caution" column tells whether enabling the flag deserves a second thought.
| Flag | Caution | Effect |
|---|---|---|
| 460 | No | Replaces the "string or binary data would be truncated" message with a more detailed one (make sure the instance is patched). |
| 610 | No | Tries minimally logged inserts into tables with indexes, similar to inserts with the TABLOCK hint. |
| 652 | Yes | Disables page pre-fetching in scans. In most cases you want pages read into memory as early as possible. |
| 661 | Yes | Disables the ghost record cleanup process, which permanently removes deleted records. |
| 834 | Maybe | Uses large page allocations for the buffer pool. Known to prevent SQL Server from starting or make startup very slow. |
| 845 | Maybe | Enables lock pages in memory on Standard Edition. |
| 1117 | No | Makes all files of a filegroup grow together. Mostly used for tempdb but affects all databases. |
| 1118 | No | Removes mixed extents. Mostly used to reduce tempdb contention. |
| 1204 | No | Returns the resources involved in a deadlock (text format). |
| 1211 | Yes | Disables lock escalation under memory pressure. Takes precedence over 1224. |
| 1222 | No | Returns the resources and lock types involved in a deadlock in XML format. |
| 1224 | Yes | Disables lock escalation based on the number of locks. 1211 takes precedence. |
| 1236 | No | Fixes a lock/spinlock problem in SQL Server 2012/2014. |
| 1806 | Yes | Disables instant file initialization. |
| 2312 | Maybe | Forces the SQL Server 2014 cardinality estimator. |
| 2330 | Yes | Disables collection of index usage and missing index requests. Rarely a good idea. |
| 2371 | No | Lowers the threshold for automatic statistics updates according to table size. Useful for very large databases. |
| 2467 | No | CPU scheduling: place threads on the least loaded node. |
| 2468 | No | CPU scheduling: place threads within the same node. |
| 2549 | Maybe | Can help DBCC CHECKDB when files are on multiple disks; no use otherwise. |
| 2562 | Maybe | Makes DBCC CHECKDB run in a single batch. Avoid if tempdb is a problem area. |
| 2861 | Maybe | Keeps zero-cost plans in the cache. Often enabled by monitoring products. |
| 3014 | No | Writes more information to the error log during backup operations. |
| 3023 | No | Enables checksums for backups when they cannot be enabled through the backup tool. |
| 3042 | Maybe | Compressed backups grow incrementally instead of preallocating space. Can slow backups down. |
| 3226 | No | Stops logging successful backup messages in the error log. Handy when taking log backups of hundreds of databases. |
| 3505 | Yes | Disables automatic checkpoints. |
| 3605 | No | Used with 1204 and 1222 to send deadlock information to the error log. |
| 3608 | Maybe | Prevents SQL Server from automatically starting and recovering all databases except master. |
| 3625 | No | Limits the information returned in error messages to users who are not sysadmin, to avoid leaking sensitive details. |
| 3801 | Probably | Prohibits USE [database] statements. Mostly useful in Azure. |
| 4101-4135 | No | Flags starting with 41 are usually optimizer fixes. Consider just enabling 4199. |
| 4136 | Probably | Disables parameter sniffing. Use only in carefully chosen and monitored environments; on SQL Server 2016+, prefer the PARAMETER_SNIFFING database scoped configuration. |
| 4137 | Probably | Changes some cardinality estimates (queries with many ANDs). |
| 4138 | Yes | Disables all row goal optimizations, which you usually want. |
| 4139 | No | Helps with ascending key problems before SQL Server 2014 (2389 and 2390 may also be needed). |
| 4199 | No | Enables optimizer fixes shipped in service packs and cumulative updates that have their own trace flags. |
| 4616 | Maybe | Makes server-level roles visible to applications. |
| 7471 | Maybe | Allows concurrent update statistics jobs on the same table; can cause deadlocks if statistics are created and updated simultaneously. |
| 7806 | No | Enables the dedicated administrator connection on SQL Server Express. |
| 8015 | Maybe | Makes SQL Server ignore the NUMA configuration and manage the nodes as one. |
| 8048 | Maybe | Switches to NUMA-based partitioning. |
| 8649 | Yes | Drops the cost threshold for parallelism to 0. A troubleshooting hint for dev servers only. |
| 9481 | Maybe | Forces the pre-2014 cardinality estimator. |
Availability groups (for example with SharePoint)
| Flag | Effect |
|---|---|
| 9567 | Compresses the data stream of availability groups during automatic seeding. Can greatly reduce transfer time but increases CPU load. |
| 9592 | Enables log stream compression for availability groups with synchronous replicas. Disabled by default because of the extra latency; enabled by default for asynchronous replicas. |
The WRITELOG wait type is common and can cause significant delays: it is the time spent waiting for the log cache to be flushed to disk. Enabling DELAYED_DURABILITY on the databases is one option to reduce it, at the price of possibly losing the most recent transactions after a crash.
Notes
- Test any trace flag outside production first; effects and defaults depend on the SQL Server version (for example, 1117 and 1118 behaviour is built in for tempdb from SQL Server 2016).
- Global flags set with
DBCC TRACEONdo not survive a restart.