Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.
The following table lists and describes the trace flags that are available in SQL Server
[!NOTE] Some trace flags were introduced in specific SQL Server versions. For more information on the applicable version, see the Microsoft Support article associated with a specific trace flag.
[!IMPORTANT] Trace flag behavior may not be supported in future releases of SQL Server
Trace flag | Description |
---|---|
139 | Forces correct conversion semantics in the scope of DBCC check commands like DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKCONSTRAINTS, when analyzing the improved precision and conversion logic introduced with compatibility level 130 for specific data types, on a database that has a lower compatibility level. For more information, see this Microsoft Support article. Note: This trace flag applies to [!INCLUDEssSQL15] RTM CU3, [!INCLUDEssSQL15] SP1 and higher builds. WARNING: Trace flag 139 is not meant to be enabled continuously in a production environment, and should be used for the sole purpose of performing database validation checks described in this Microsoft Support article. It should be immediately disabled after validation checks are completed. Scope: global only |
174 | Increases the [!INCLUDEssDEnoversion] plan cache bucket count from 40,009 to 160,001 on 64-bit systems. For more information, see this Microsoft Support article. Note: Please ensure that you thoroughly test this option, before rolling it into a production environment. Scope: global only |
176 | Enables a fix to address errors when rebuilding partitions online for tables that contain a computed partitioning column. For more information, see this Microsoft Support article. Scope: global or session |
205 | Reports to the error log when a statistics-dependent stored procedure is being recompiled as a result of auto-update statistics. For more information, see this Microsoft Support article. Scope: global only |
260 | Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about GetXpVersion(), see Creating Extended Stored Procedures. Scope: global or session |
272 | Disables identity pre-allocation to avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server. Note that identity caching is used to improve INSERT performance on tables with identity columns. Note: Starting with [!INCLUDEssSQL17], to accomplish this at the database level, see the IDENTITY_CACHE option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). Scope: global only |
460 | Replaces data truncation message id 8152 with message id 2628. For more information, see this Microsoft Support article. Note: This trace flag applies to [!INCLUDEssSQL17] CU12, and higher builds. Scope: global or session |
610 | Controls minimally logged inserts into indexed tables. This trace flag is not required starting SQL Server 2016 as minimal logging is turned on by default for indexed tables. In SQL Server 2016, when the bulk load operation causes a new page to be allocated, all of the rows sequentially filling that new page are minimally logged if all the other pre-requisites for minimal logging are met. Rows inserted into existing pages (no new page allocation) to maintain index order are still fully logged, as are rows that are moved as a result of page splits during the load. It is also important to have ALLOW_PAGE_LOCKS turned ON for indexes (which is ON by default) for minimal logging operation to work as page locks are acquired during allocation and thereby only page or extent allocations are logged.For more information, see Data Loading Performance Guide. Scope: global or session |
634 | Disables the background columnstore compression task. [!INCLUDEssNoVersion] periodically runs the Tuple Mover background task that compresses columnstore index rowgroups with uncompressed data, one such rowgroup at a time. Columnstore compression improves query performance but also consumes system resources. You can control the timing of columnstore compression manually, by disabling the background compression task with trace flag 634, and then explicitly invoking ALTER INDEX…REORGANIZE or ALTER INDEX…REBUILD at the time of your choice. Scope: global only |
652 | Disables page pre-fetching scans. For more information, see this Microsoft Support article. Scope: global or session |
661 | Disables the ghost record removal process. For more information, see this Microsoft Support article. Scope: global only |
692 | Disables fast inserts while bulk loading data into heap or clustered index. Starting [!INCLUDEssSQL15], fast inserts is enabled by default leveraging minimal logging when database is in simple or bulk logged recovery model to optimize insert performance for records inserted into new pages. With fast inserts, each bulk load batch acquires new extent(s) bypassing the allocation lookup for existing extent with available free space to optimize insert performance. With fast inserts, bulk loads with small batch sizes can lead to increased unused space consumed by objects hence it is recommended to use large batchsize for each batch to fill the extent completely. If increasing batchsize is not feasible, this traceflag can help reduce unused space reserved at the expense of performance. Note: This trace flag applies to [!INCLUDEssSQL15] RTM and higher builds. Scope: global or session |
715 | Enables table lock for bulk load operations into a heap with no non-clustered indexes. When this trace flag is enabled, bulk load operations acquire bulk update (BU) locks when bulk copying data into a table. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table, while preventing other processes that are not bulk loading data from accessing the table. The behavior is similar to when the user explicitly specifies TABLOCK hint while performing bulk load, or when the sp_tableoption table lock on bulk load is enabled for a given table. However, when this trace flag is enabled, this behavior becomes default without any query or database changes. Scope: global or session |
834 | Uses Microsoft Windows large-page allocations for the buffer pool. For more information, see this Microsoft Support article. Note: If you are using the Columnstore Index feature of [!INCLUDEssSQL11] to [!INCLUDEssCurrent], we do not recommend turning on trace flag 834. Scope: global only |
845 | Enables locked pages on Standard SKUs of [!INCLUDEssNoVersion], when the service account for [!INCLUDEssNoVersion] has the Lock Pages in Memory privilege enabled. For more information, see this Microsoft Support article and the documentation page on Server Memory Server Configuration Options. Note: Starting with [!INCLUDEssSQL11] this behavior is enabled by default for Standard SKUs, and trace flag 845 must not be used. Scope: global only |
902 | Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack. If you encounter an error during script upgrade mode, it is recommended to contact Microsoft SQL Customer Service and Support (CSS) for further guidance. For more information, see this Microsoft Support article. WARNING: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your [!INCLUDEssNoVersion] instance. Scope: global only |
1117 | When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. Note: Starting with [!INCLUDEssSQL15] this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no effect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL). Scope: global only |
1118 | Removes most single page allocations on the server, reducing contention on the SGAM page. When a new object is created, by default, the first eight pages are allocated from different extents (mixed extents). Afterwards, when more pages are needed, those are allocated from that same extent (uniform extent). The SGAM page is used to track these mixed extents, so can quickly become a bottleneck when numerous mixed page allocations are occurring. This trace flag allocates all eight pages from the same extent when creating new objects, minimizing the need to scan the SGAM page. For more information, see this Microsoft Support article. Note: Starting with [!INCLUDEssSQL15] this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no effect. For more information, see ALTER DATABASE SET Options (Transact-SQL). Scope: global only |
1204 | Returns the resources and types of locks participating in a deadlock and also the current command affected. For more information, see this Microsoft Support article. Scope: global only |
1211 | Disables lock escalation based on memory pressure, or based on number of locks. The [!INCLUDEssDEnoversion] will not escalate row or page locks to table locks. Using this trace flag can generate excessive numbers of locks. This can slow the performance of the [!INCLUDEssDE], or cause 1204 errors (unable to allocate lock resource) because of insufficient memory. If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used. Scope: global or session |
1222 | Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. Scope: global only |
INSERT INTO ... SELECT
into a partitioned columnstore index. For more information, see this Microsoft Support article.Database compatibility level | TF 4199 | QO changes from previous database compatibility levels | QO changes for current version post-RTM |
100 to 120 | Off | Disabled | Disabled |
On | Enabled | Enabled | |
130 | Off | Enabled | Disabled |
On | Enabled | Enabled | |
140 | Off | Enabled | Disabled |
On | Enabled | Enabled | |
150 | Off | Enabled | Disabled |
On | Enabled | Enabled |
Scope: global only| |6532|Enables performance improvement of query operations with spatial data types in SQL Server 2012 (11.x) and SQL Server 2012 (11.x) SQL Server 2014 (12.x) The performance gain will vary, depending on the configuration, the types of queries, and the objects. For more information, see this Microsoft Support article.
Note: Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 6532 has no effect.
Scope: global and session| |6533|Enables performance improvement of query operations with spatial data types in SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) and SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) The performance gain will vary, depending on the configuration, the types of queries, and the objects. For more information, see this Microsoft Support article.
Note: Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 6533 has no effect.
Scope: global and session| |6534|Enables performance improvement of query operations with spatial data types in SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) and SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) The performance gain will vary, depending on the configuration, the types of queries, and the objects. For more information, see this Microsoft Support article.
Scope: global only| |7314|Forces NUMBER values with unknown precision/scale to be treated as double values with OLE DB provider. For more information, see this Microsoft Support article.
Scope: global and session|
|7412|Enables the lightweight query execution statistics profiling infrastructure. For more information, see this Microsoft Support article.
Note: This trace flag applies to SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SP1 and higher builds. Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview this trace flag has no effect because lightweight profiling is enabled by default.
Scope: global only| |7471|Enables running multiple UPDATE STATISTICS for different statistics on a single table concurrently. For more information, see this Microsoft Support article.
Note: This trace flag applies to SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SP1 and higher builds.
Scope: global only| |7745|Forces Query Store to not flush data to disk on database shutdown.
Note: Using this trace may cause Query Store data not previously flushed to disk to be lost in case of shutdown. For a SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server shutdown, the command SHUTDOWN WITH NOWAIT can be used instead of this trace flag to force an immediate shutdown.
Scope: global only| |7752|Enables asynchronous load of Query Store.
Note: Use this trace flag if SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server is experiencing high number of QDS_LOADDB waits related to Query Store synchronous load (default behavior).
Scope: global only| |7806|Enables a dedicated administrator connection (DAC) on SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express By default, no DAC resources are reserved on SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express For more information, see Diagnostic Connection for Database Administrators.
Scope: global only|
|8011|Disable the ring buffer for Resource Monitor. For more information, see this Microsoft Support article.
Scope: global and session| |8012|Disable the ring buffer for schedulers. For more information, see this Microsoft Support article.
Scope: global only| |8015|Disable auto-detection and NUMA setup. For more information, see this Microsoft Support article.
Scope: global only| |8018|Disable the exception ring buffer. For more information, see this Microsoft Support article.
Scope: global only| |8019|Disable stack collection for the exception ring buffer. For more information, see this Microsoft Support article.
Scope: global only| |8020|Disable working set monitoring. For more information, see this Microsoft Support article.
Scope: global only| |8032|Reverts the cache limit parameters to the SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) RTM setting which in general allows caches to be larger. Use this setting when frequently reused cache entries do not fit into the cache and when the optimize for ad hoc workloads Server Configuration Option has failed to resolve the problem with plan cache.
WARNING: Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.
Scope: global only|
|8048|Converts NUMA partitioned memory objects into CPU partitioned. For more information, see this Microsoft Support article.
Note: Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SP2 and SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 8048 has no effect.
Scope: global only|
|8075|Reduces VAS fragmentation when you receive memory page allocation errors on a 64-bit SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) or SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) For more information, see this Microsoft Support article.
Note: This trace flag applies to SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) RTM CU10, and SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SP1 CU3. Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 8075 has no effect.
Scope: global only| |8079|Allows SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SP2 to interrogate the hardware layout and automatically configure Soft-NUMA on systems reporting 8 or more CPUs per NUMA node. The automatic Soft-NUMA behavior is Hyperthread (HT/logical processor) aware. The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling and network and encryption capabilities.
Note: This trace flag applies to SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SP2. Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 8079 has no effect.
Scope: global only| |8207|Enables singleton updates for Transactional Replication and CDC. Updates to subscribers can be replicated as a DELETE and INSERT pair. This might not meet business rules, such as firing an UPDATE trigger. With trace flag 8207, an update to a unique column that affects only one row (a singleton update) is replicated as an UPDATE and not as a DELETE or INSERT pair. If the update affects a column on which a unique constraint exists, or if the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair. For more information, see this Microsoft Support article.
Scope: global only| |8721|Reports to the error log when auto-update statistics executes. For more information, see this Microsoft Support article.
Scope: global only| |8744|Disable pre-fetching for the Nested Loop operator. For more information, see this Microsoft Support article.
Note: Incorrect use of this trace flag may cause additional physical reads when SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server executes plans that contain the Nested Loops operator.
Scope: global and session| |9024|Converts a global log pool memory object into NUMA node partitioned memory object. For more information, see this Microsoft Support article.
Note: Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SP3 and SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SP1 this behavior is controlled by the engine and trace flag 9024 has no effect.
Scope: global only| |9347|Disables batch mode for sort operator. SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) introduced a new batch mode sort operator that boosts performance for many analytical queries. For more information, see this Microsoft Support article.
Scope: global or session or query| |9349|Disables batch mode for top N sort operator. SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) introduced a new batch mode top sort operator that boosts performance for many analytical queries.
Scope: global or session or query| |9389|Enables additional dynamic memory grant for batch mode operators. If a query does not get all the memory it needs, it spills data to tempdb, incurring additional I/O and potentially impacting query performance. If the dynamic memory grant trace flag is enabled, a batch mode operator may ask for additional memory and avoid spilling to tempdb if additional memory is available. For more information, see the Effects of min memory per query section of the Memory Management Architecture Guide.
Scope: global or session| |9398|Disables Adaptive Join operator that enables the choice of a Hash join or Nested Loops join method to be deferred until the after the first input has been scanned, as introduced in SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) For more information, see this Microsoft Support article.
Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.
Scope: global and session and query| |9453|Disables batch mode execution. For more information, see this Microsoft Support article.
Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.
Scope: global and session and query| |9471|Causes SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server to generate a plan using minimum selectivity for single-table filters, under the query optimizer cardinality estimation model of SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) through SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 versions.
Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT ‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’ query hint instead of using this trace flag.
Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.
Note: This trace flag does not apply to CE version 70. Use trace flag 4137 instead.
Scope: global or session or query| |9476|Causes SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server to generate a plan using the Simple Containment assumption instead of the default Base Containment assumption, under the query optimizer cardinality estimation model of SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) through SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 versions. For more information, see this Microsoft Support article.
Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT ‘ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS’ query hint instead of using this trace flag.
Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.
Scope: global or session or query| |9481|Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) and earlier versions, irrespective of the compatibility level of the database. For more information, see Microsoft Support article.
Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) to accomplish this at the database level, see the LEGACY_CARDINALITY_ESTIMATION option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
Starting with SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT ‘FORCE_LEGACY_CARDINALITY_ESTIMATION’ query hint instead of using this trace flag.
Scope: global or session or query|
|9485|Disables SELECT permission for DBCC SHOW_STATISTICS.
Scope: global only| |9488|Sets the fixed estimation for Table Valued Functions to the default of 1 (corresponding to the default under the query optimizer cardinality estimation model of SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 and earlier versions), when using the query optimizer cardinality estimation model of SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) through SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) SQL Server 2017 versions.
Scope: global or session or query| |9495|Disables parallelism during insertion for INSERT…SELECT operations and it applies to both user and temporary tables. For more information, see Microsoft Support article
Scope: global or session| |9567|Enables compression of the data stream for Always On Availability Groups during automatic seeding. Compression can significantly reduce the transfer time during automatic seeding and will increase the load on the processor. For more information, see Automatically initialize Always On availability group and Tune compression for availability group.
Scope: global or session| |9591|Disables log block compression in Always On Availability Groups. Log block compression is the default behavior used with both synchronous and asynchronous replicas in SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) SQL Server 2017 SQL Server 2012 (11.x) and SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) SQL Server 2017 SQL Server 2012 (11.x) SQL Server 2014 (12.x) In SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) SQL Server 2017 SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) compression is only used with asynchronous replica.
Scope: global or session| |9592|Enables log stream compression for synchronous availability groups. This feature is disabled by default on synchronous availability groups because compression adds latency. For more information, see Tune compression for availability group.
Scope: global or session| |9929|Reduces the In-Memory checkpoint files to 1 MB each. For more information, see this Microsoft Support article.
Scope: global only|
|9939|Enables parallel plans and parallel scan of memory-optimized tables and table variables in DML operations that reference memory-optimized tables or table variables, as long as they are not the target of the DML operation in SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) SQL Server 2017 SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) For more information, see this Microsoft Support article.
Note: Trace flag 9939 is not needed if trace flag 4199 is also explicitly enabled.
Scope: global or session or query|
|10204|Disables merge/recompress during columnstore index reorganization. In SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) SQL Server 2017 SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) when a columnstore index is reorganized, there is new functionality to automatically merge any small compressed rowgroups into larger compressed rowgroups, as well as recompressing any rowgroups that have a large number of deleted rows.
Note: Trace flag 10204 does not apply to columnstore indexes which are created on memory-optimized tables.
Scope: global or session|
|10316|Enables creation of additional indexes on internal memory-optimized staging temporal table, beside the default one. If you have specific query pattern that includes columns which are not covered by the default index you may consider adding additional ones.
Note: System-versioned temporal tables for Memory-Optimized Tables are designed to provide high transactional throughput. Please be aware that creating additional indexes may introduce overhead for DML operations that update or delete rows in the current table. With the additional indexes you should aim to find the right balance between performance of temporal queries and additional DML overhead.
Scope: global or session| |11023|Disables the use of the last persisted sample rate for all subsequent statistics update, where a sample rate is not specified explicitly as part of the UPDATE STATISTICS statement. For more information, see this Microsoft Support article.
Scope: global or session|
|11024|Enables triggering the auto update of statistics when the modification count of any partition exceeds the local threshold. For more information, see this Microsoft Support article.
Note: This trace flag applies to SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) SQL Server 2017 SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SP2, SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2019 preview SQL Server 2014 (12.x) SQL Server SQL Server SQL Server Express SQL Server Express SQL Server 2005 (9.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server SQL Server 2014 (12.x) SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2012 (11.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2008 R2 SQL Server 2012 (11.x) SQL Server 2017 SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2016 (13.x) SQL Server 2017 (14.x) CU3, and higher builds.
Scope: global or session|
In SQL Server there are three types of trace flags: query, session and global. Query trace flags are active for the context of a specific query. Session trace flags are active for a connection and are visible only to that connection. Global trace flags are set at the server level and are visible to every connection on the server. Some flags can only be enabled as global, and some can be enabled at either global or session scope.
The following rules apply:
- A global trace flag must be enabled globally. Otherwise, the trace flag has no effect. We recommend that you enable global trace flags at startup, by using the -T command line option. This ensures the trace flag remains active after a server restart.
- If a trace flag has either global, session or query scope, it can be enabled with the appropriate scope. A trace flag that is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.
Trace flags are set on or off by using either of the following methods: - Using the DBCC TRACEON and DBCC TRACEOFF commands.
For example, to enable the 2528 trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON (2528, -1)
. The effect of enabling a global trace flag with DBCC TRACEON is lost on server restart. To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.
- Using the -T startup option to specify that the trace flag be set on during startup.
The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option. This ensures the trace flag remains active after a server restart. For more information about startup options, see Database Engine Service Startup Options. - At the query level, by using the QUERYTRACEON query hint.
Use the DBCC TRACESTATUS
command to determine which trace flags are currently active.
The following example sets trace flag 3205 on for all sessions at the server level by using DBCC TRACEON.
You can enable all plan-affecting hotfixes controlled by trace flags 4199 and 4137 for a particular query.
Data Types (Transact-SQL)
DBCC INPUTBUFFER (Transact-SQL)
DBCC OUTPUTBUFFER (Transact-SQL)
DBCC TRACEOFF (Transact-SQL)
DBCC TRACEON (Transact-SQL)
DBCC TRACESTATUS (Transact-SQL)
EXECUTE (Transact-SQL)
SELECT (Transact-SQL)
SET NOCOUNT (Transact-SQL)
ALTER DATABASE SET Options (Transact-SQL)
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
Query Hints (Transact-SQL)