LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon