DBCC TRACEON - Trace Flags (Transact-

SQL)
11/24/2017 33 minutes to read Contributors


 all

THIS TOPIC APPLIES TO: SQL Server (starting with 2012) Azure SQL Database

Azure SQL Data Warehouse Parallel Data Warehouse

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
Description
flag
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.
Trace
Description
flag
Note: This trace flag applies to SQL Server 2016 RTM CU3, SQL Server 2016 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

Increases the SQL Server Database Engine plan cache bucket count from 40,009 to
160,001 on 64-bit systems. For more information, see this Microsoft Support article.

174 Note: Please ensure that you thoroughly test this option, before rolling it into a
production environment.

Scope: global only

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.
176
Scope: global or session
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
205 article.

Scope: global only

Prints versioning information about extended stored procedure dynamic-link libraries
(DLLs). For more information about GetXpVersion(), see Creating Extended Stored
260 Procedures.

Scope: global or session

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.
272
Note: Starting with SQL Server 2017, to accomplish this at the database level, see the
IDENTITY_CACHE option in ALTER DATABASE SCOPED CONFIGURATION
(Transact-SQL).

Scope: global only

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-
Trace
Description
flag
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

Disables the background columnstore compression task. SQL Server 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

634 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

Disables page pre-fetching scans. For more information, see this Microsoft Support
article.
652
Scope: global or session
Disables the ghost record removal process. For more information, see this Microsoft
Support article.
661
Scope: global only
Disables fast inserts while bulk loading data into heap or clustered index. Starting SQL
Server 2016, 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.

692 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 SQL Server 2016 RTM and higher builds.

Scope: global or session

715 Enables table lock for bulk load operations into a heap with no non-clustered indexes.
Trace
Description
flag
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

Uses Microsoft Windows large-page allocations for the buffer pool. For more
information, see this Microsoft Support article.

834 Note: If you are using the Columnstore Index feature of SQL Server 2012 to SQL Server
2017, we do not recommend turning on trace flag 834.

Scope: global only

Enables locked pages on Standard SKUs of SQL Server, when the service account for
SQL Server 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.
845
Note: Starting with SQL Server 2012 this behavior is enabled by default for Standard
SKUs, and trace flag 845 must not be used.

Scope: global only

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
902
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 SQL Server instance.

Scope: global only

1117 When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.

Note: Starting with SQL Server 2016 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
Trace
Description
flag
DATABASE File and Filegroup Options (Transact-SQL).

Scope: global only

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
1118 information, see this Microsoft Support article.

Note: Starting with SQL Server 2016 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

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.
1204
Scope: global only
Disables lock escalation based on memory pressure, or based on number of locks. The
SQL Server Database Engine 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 Database Engine, or cause 1204 errors (unable to allocate lock
resource) because of insufficient memory.
1211
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

1224 Disables lock escalation based on the number of locks. However, memory pressure can
still activate lock escalation. The Database Engine escalates row or page locks to table (or
partition) locks if the amount of memory used by lock objects exceeds one of the
following conditions:
Trace
Description
flag

- Forty percent of the memory that is used by Database Engine. This is applicable only
when the locks parameter of sp_configure is set to 0.
- Forty percent of the lock memory that is configured by using the locks parameter of
sp_configure. For more information, see Server Configuration Options (SQL Server).

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.

Note: Lock escalation to the table-level or HoBT-level granularity can also be controlled
by using the LOCK_ESCALATION option of the ALTER TABLE statement.

Scope: global or session

Enables database lock partitioning. For more information, see this Microsoft Support
article.

1236 Note: Starting with SQL Server 2012 SP3 and SQL Server 2014 SP1 this behavior is
controlled by the engine and trace flag 1236 has no effect.

Scope: global only

Allows the ALTER PARTITION FUNCTION statement to honor the current user-
defined session deadlock priority instead of being the likely deadlock victim by default.
For more information, see this Microsoft Support article.
1237
Note: Starting with SQL Server 2017 and database compatibility level 140 this is the
default behavior and trace flag 1237 has no effect.

Scope: global or session or query

Disable scheduler monitor dumps.
1260
Scope: global only
Enables the replication log reader to move forward even if the async secondaries have not
acknowledged the reception of a change. Even with this trace flag enabled the log reader
always waits for the sync secondaries. The log reader will not go beyond the min ack of
the sync secondaries. This trace flag applies to the instance of SQL Server, not just an
1448 availability group, an availability database, or a log reader instance. Takes effect
immediately without a restart. This trace flag can be activated ahead of time or when an
async secondary fails. For more information, see this Microsoft Support article.

Scope: global only

1462 Disables log stream compression for asynchronous availability groups. This feature is
enabled by default on asynchronous availability groups in order to optimize network
Trace
Description
flag
bandwidth. For more information, see Tune compression for availability group.

Scope: global only

Enables SQL Server optimization when disks of different sector sizes are used for
primary and secondary replica log files, in SQL Server Always On and Log Shipping
environments. This trace flag is only required to be enabled on SQL Server instances with
transaction log file residing on disk with sector size of 512 bytes. It is not required to be
1800
enabled on disk with 4k sector sizes. For more information, see this Microsoft Support
article.

Scope: global only

Enable advanced decision support optimizations. For more information, see this
Microsoft Support article.
2301
Scope: global and session and query
Enables you to set the query optimizer cardinality estimation model to the SQL Server
2014 through SQL Server 2017 versions, dependent of the compatibility level of the
database. For more information, see Microsoft Support article.

2312 Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE
HINT 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' query hint instead of
using this trace flag.

Scope: global or session or query

Causes SQL Server to assume a fixed amount of memory is available during query
optimization. It does not limit the memory SQL Server grants to execute the query. The
memory configured for SQL Server will still be used by data cache, query execution and
other consumers. For more information, see this Microsoft Support article.
2335
Note: Please ensure that you thoroughly test this option, before rolling it into a
production environment.

Scope: global or session or query

Causes SQL Server not to use a sort operation (batch sort) for optimized nested loop joins
when generating a plan. For more information, see this Microsoft Support article.

Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE
HINT 'DISABLE_OPTIMIZED_NESTED_LOOP' query hint instead of using this trace
2340 flag.

Note: Please ensure that you thoroughly test this option, before rolling it into a
production environment.

Scope: global or session or query

Trace
Description
flag
Changes the fixed auto update statistics threshold to dynamic auto update statistics
threshold. For more information, see this Microsoft Support article.

2371 Note: Starting with SQL Server 2016 and under the database compatibility level 130, this
behavior is controlled by the engine and trace flag 2371 has no effect.

Scope: global only

Enable automatically generated quick statistics for ascending keys (histogram
amendment). If trace flag 2389 is set, and a leading statistics column is marked as
ascending, then the histogram used to estimate cardinality will be adjusted at query
compile time. For more information, see this Microsoft Support article.

Note: Please ensure that you thoroughly test this option, before rolling it into a
2389
production environment.

Note: This trace flag does not apply to CE version 120 or above. Use trace flag 4139
instead.

Scope: global or session or query

Enable automatically generated quick statistics for ascending or unknown keys
(histogram amendment). If trace flag 2390 is set, and a leading statistics column is
marked as ascending or unknown, then the histogram used to estimate cardinality will be
adjusted at query compile time. For more information, see this Microsoft Support article.

Note: Please ensure that you thoroughly test this option, before rolling it into a
2390
production environment.

Note: This trace flag does not apply to CE version 120 or above. Use trace flag 4139
instead.

Scope: global or session or query

Allows a table variable to trigger recompile when enough number of rows are changed.
For more information, see this Microsoft Support article.

2453 Note: Please ensure that you thoroughly test this option, before rolling it into a
production environment.

Scope: global or session or query

2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC
CHECKFILEGROUP, and DBCC CHECKTABLE. By default, the degree of parallelism
is automatically determined by the query processor. The maximum degree of parallelism
is configured just like that of parallel queries. For more information, see Configure the
max degree of parallelism Server Configuration Option.
Trace
Description
flag
Note: Parallel DBCC checks should typically be enabled (default). The query processor
reevaluates and automatically adjusts parallelism for each table or batch of tables checked
by DBCC CHECKDB.

The typical use scenario is when a system administrator knows that server load will
increase before DBCC CHECKDB completes, and so chooses to manually decrease or
disable parallelism, in order to increase concurrency with other user workload. However,
disabling parallel checks in DBCC CHECKDB can cause it to take longer to complete.

Note: If DBCC CHECKDB is executed using the TABLOCK option and parallelism is
disabled, tables may be locked for longer periods of time.

Note: Starting with SQL Server 2014 SP2, a MAXDOP option is available to override
the max degree of parallelism configuration option of sp_configure for the statement.

Scope: global or session

Runs the DBCC CHECKDB command assuming each database file is on a unique disk
drive. DBCC CHECKDB command builds an internal list of pages to read per unique
disk drive across all database files. This logic determines unique disk drives based on the
drive letter of the physical file name of each file.

Note: Do not use this trace flag unless you know that each file is based on a unique
physical disk.
2549
Note: Although this trace flag improve the performance of the DBCC CHECKDB
commands which target usage of the PHYSICAL_ONLY option, some users may not see
any improvement in performance. While this trace flag improves disk I/O resources
usage, the underlying performance of disk resources may limit the overall performance of
the DBCC CHECKDB command. For more information, see Microsoft Support article.

Scope: global only

2562 Runs the DBCC CHECKDB command in a single "batch" regardless of the number of
indexes in the database. By default, the DBCC CHECKDB command tries to minimize
tempdb resources by limiting the number of indexes or "facts" that it generates by using a
"batches" concept. This trace flag forces all processing into one batch.

One effect of using this trace flag is that the space requirements for tempdb may increase.
Tempdb may grow to as much as 5% or more of the user database that is being processed
by the DBCC CHECKDB command.

Note: Although this trace flag improve the performance of the DBCC CHECKDB
commands which target usage of the PHYSICAL_ONLY option, some users may not see
any improvement in performance. While this trace flag improves disk I/O resources
usage, the underlying performance of disk resources may limit the overall performance of
Trace
Description
flag
the DBCC CHECKDB command. For more information, see Microsoft Support article.

Scope: global only

Runs the DBCC CHECKDB command without data purity check unless
DATA_PURITY option is specified.

Note: Column-value integrity checks are enabled by default and do not require the
DATA_PURITY option. For databases upgraded from earlier versions of SQL Server,
2566 column-value checks are not enabled by default until DBCC CHECKDB WITH
DATA_PURITY has been run error free on the database at least once. After this, DBCC
CHECKDB checks column-value integrity by default. For more information, see
Microsoft Support article.

Scope: global only

Enables CHECKSUM option as default for BACKUP command. For more information,
see this Microsoft Support article.

Note: Starting with SQL Server 2014 this behavior is controlled by setting the backup
3023
checksum default configuration option. For more information, see Server Configuration
Options (SQL Server).

Scope: global and session

Bypasses the default backup compression pre-allocation algorithm to allow the backup
file to grow only as needed to reach its final size. This trace flag is useful if you need to
save on space by allocating only the actual size required for the compressed backup.
Using this trace flag might cause a slight performance penalty (a possible increase in the
3042
duration of the backup operation). For more information about the pre-allocation
algorithm, see Backup Compression (SQL Server).

Scope: global only

Enables SQL Server Backup to URL logging to a specific error log file. For more
information, see SQL Server Backup to URL Best Practices and Troubleshooting.
3051
Scope: global only
By default, if a tape drive supports hardware compression, either the DUMP or BACKUP
statement uses it. With this trace flag, you can disable hardware compression for tape
drivers. This is useful when you want to exchange tapes with other sites or tape drives
3205
that do not support compression.

Scope: global or session

3226 By default, every successful backup operation adds an entry in the SQL Server error log
and in the system event log. If you create very frequent log backups, these success
messages accumulate quickly, resulting in huge error logs in which finding other
messages is problematic.
Trace
Description
flag

With this trace flag, you can suppress these log entries. This is useful if you are running
frequent log backups and if none of your scripts depend on those entries.

Scope: global only

3427 Enables fix for issue when many consecutive transactions inserting data into temp table in
SQL Server 2016 consume more CPU than in SQL Server 2014. For more information,
see Microsoft Support article

Scope: global only

Prevents SQL Server from automatically starting and recovering any database except the
master database. If activities that require tempdb are initiated, then model is recovered
and tempdb is created. Other databases will be started and recovered when accessed.
Some features, such as snapshot isolation and read committed snapshot, might not work.
3608 Use for Move System Databases and Move User Databases.

Note: Do not use during normal operation.

Scope: global only

3625 Limits the amount of information returned to users who are not members of the sysadmin
fixed server role, by masking the parameters of some error messages using '******'. This
can help prevent disclosure of sensitive information.

Scope: global only

Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE or
OPTIMIZE FOR <value> is used. For more information, see Microsoft Support article.
To accomplish this at the database level, see the PARAMETER_SNIFFING option in
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). To accomplish
this at the query level, add the OPTIMIZE FOR UNKNOWN query hint. Starting with
SQL Server 2016 SP1, a second option to accomplish this at the query level is to add the
4136
USE HINT 'PARAMETER_SNIFFING' 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

4137 Causes SQL Server to generate a plan using minimum selectivity when estimating AND
predicates for filters to account for correlation, under the query optimizer cardinality
estimation model of SQL Server 2012 and earlier versions. For more information, see this
Microsoft Support article.

Starting with SQL Server 2016 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 when using the Legacy CE.
Trace
Description
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 120 or above. Use trace flag 9471
instead.

Scope: global or session or query

Causes SQL Server to generate a plan that does not use row goal adjustments with
queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords. For more
information, see this Microsoft Support article.

Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE
4138 HINT 'DISABLE_OPTIMIZER_ROWGOAL' 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

Enable automatically generated quick statistics (histogram amendment) regardless of key
column status. If trace flag 4139 is set, regardless of the leading statistics column status
(ascending, descending, or stationary), the histogram used to estimate cardinality will be
adjusted at query compile time. For more information, see this Microsoft Support article.

Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE
HINT 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS' query hint instead of using
this trace flag.
4139
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 flags 2389 and 2390
instead.

Scope: global or session or query

4199 Enables query optimizer (QO) changes released in SQL Server Cumulative Updates and
Service Packs.

QO changes that are made to previous releases of SQL Server are enabled by default
under the latest database compatibility level in a given product release, without trace flag
4199 enabled.

The following table summarizes the behavior when using specific database compatibility
Trace
Description
flag
levels and trace flag 4199. For more information, see this Microsoft Support article.

QO changes for
Database TF QO changes from previous
current version post-
compatibility level 4199 database compatibility levels
RTM
Off Disabled Disabled
100 to 120
On Enabled Enabled
Off Enabled Disabled
130
On Enabled Enabled
Off Enabled Disabled
140
On Enabled Enabled

To accomplish this at the database level, see the QUERY_OPTIMIZER_HOTFIXES

option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE
HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES' query hint instead of using this
trace flag.

Scope: global or session or query

Increases the size of the hash table that stores the cache entries by a factor of 8. When
used together with trace flag 4618 increases the number of entries in the
TokenAndPermUserStore cache store to 8,192. For more information, see this Microsoft
4610
Support article.

Scope: global only

Makes server-level metadata visible to application roles. In SQL Server, an application
role cannot access metadata outside its own database because application roles are not
associated with a server-level principal. This is a change of behavior from earlier versions
4616 of SQL Server. Setting this global flag disables the new restrictions, and allows for
application roles to access server-level metadata.

Scope: global only

Limits the number of entries in the TokenAndPermUserStore cache store to 1,024. When
used together with trace flag 4610 increases the number of entries in the
TokenAndPermUserStore cache store to 8,192. For more information, see this Microsoft
4618
Support article.

Scope: global only

5004 Pauses TDE encryption scan and causes encryption scan worker to exit without doing any
work. The database will continue to be in encrypting state (encryption in progress). To
Trace
Description
flag
resume re-encryption scan, disable trace flag 5004 and run ALTER DATABASE
<database_name> SET ENCRYPTION ON.

Scope: global only

Enables more than one large query compilation to gain access to the big gateway when
there is sufficient memory available. It is based on the 80 percentage of SQL Server
Target Memory, and it allows for one large query compilation per 25 gigabytes (GB) of
memory. For more information, see this Microsoft Support article.
6498
Note: Starting with SQL Server 2014 SP2 and SQL Server 2016 this behavior is
controlled by the engine and trace flag 6498 has no effect.

Scope: global only

Disables generation of a memory dump on the first occurrence of an out-of-memory
exception in CLR integration. By default, SQL Server generates a small memory dump
on the first occurrence of an out-of-memory exception in the CLR. The behavior of the
trace flag is as follows:

- If this is used as a startup trace flag, a memory dump is never generated. However, a
6527 memory dump may be generated if other trace flags are used.
- If this trace flag is enabled on a running server, a memory dump will not be
automatically generated from that point on. However, if a memory dump has already
been generated due to an out-of-memory exception in the CLR, this trace flag will have
no effect.

Scope: global only

Enables performance improvement of query operations with spatial data types in SQL
Server 2012 and SQL Server 2014. The performance gain will vary, depending on the
configuration, the types of queries, and the objects. For more information, see this
Microsoft Support article.
6532
Note: Starting with SQL Server 2016 this behavior is controlled by the engine and trace
flag 6532 has no effect.

Scope: global and session

Enables performance improvement of query operations with spatial data types in SQL
Server 2012 and SQL Server 2014. The performance gain will vary, depending on the
configuration, the types of queries, and the objects. For more information, see this
Microsoft Support article.
6533
Note: Starting with SQL Server 2016 this behavior is controlled by the engine and trace
flag 6533 has no effect.

Scope: global and session

Trace
Description
flag
Enables performance improvement of query operations with spatial data types in SQL
Server 2012, SQL Server 2014 and SQL Server 2016. The performance gain will vary,
depending on the configuration, the types of queries, and the objects. For more
6534
information, see this Microsoft Support article.

Scope: global and session

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.
7314
Scope: global and session
Enables the lightweight query execution statistics profiling infrastructure. For more
information, see this Microsoft Support article.
7412
Scope: global only
Enables running multiple UPDATE STATISTICS for different statistics on a single table
concurrently. For more information, see this Microsoft Support article.
7471
Scope: global only
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
7745 lost in case of shutdown. For a SQL Server shutdown, the command SHUTDOWN
WITH NOWAIT can be used instead of this trace flag to force an immediate shutdown.

Scope: global only

Enables asynchronous load of Query Store.

Note: Use this trace flag if SQL Server is experiencing high number of QDS_LOADDB
7752
waits related to Query Store synchronous load (default behavior).

Scope: global only

Enables a dedicated administrator connection (DAC) on SQL Server Express. By default,
no DAC resources are reserved on SQL Server Express. For more information, see
7806 Diagnostic Connection for Database Administrators.

Scope: global only

Disable the ring buffer for Resource Monitor. For more information, see this Microsoft
Support article.
8011
Scope: global and session
8012 Disable the ring buffer for schedulers. For more information, see this Microsoft Support
article.
Trace
Description
flag
Scope: global only
Disable auto-detection and NUMA setup. For more information, see this Microsoft
Support article.
8015
Scope: global only
Disable the exception ring buffer. For more information, see this Microsoft Support
article.
8018
Scope: global only
Disable stack collection for the exception ring buffer. For more information, see this
Microsoft Support article.
8019
Scope: global only
Disable working set monitoring. For more information, see this Microsoft Support article.
8020
Scope: global only
Reverts the cache limit parameters to the SQL Server 2005 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.
8032
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

Converts NUMA partitioned memory objects into CPU partitioned. For more
information, see this Microsoft Support article.

8048 Note: Starting with SQL Server 2014 SP2 and SQL Server 2016 this behavior is
controlled by the engine and trace flag 8048 has no effect.

Scope: global only

Allows SQL Server 2014 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.
8079
Note: This trace flag applies to SQL Server 2014 SP2. Starting with SQL Server 2016
this behavior is controlled by the engine and trace flag 8048 has no effect.

Scope: global only

8207 Enables singleton updates for Transactional Replication. Updates to subscribers can be
Trace
Description
flag
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 has a unique constraint 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

Reports to the error log when auto-update statistics executes. For more information, see
this Microsoft Support article.
8721
Scope: global only
Disable pre-fetching for the Nested Loop operator. For more information, see this
Microsoft Support article.

8744 Note: Incorrect use of this trace flag may cause additional physical reads when SQL
Server executes plans that contain the Nested Loops operator.

Scope: global and session

Converts a global log pool memory object into NUMA node partitioned memory object.
For more information, see this Microsoft Support article.

9024 Note: Starting with SQL Server 2012 SP3 and SQL Server 2014 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 2016 introduces 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

Disables batch mode for top N sort operator. SQL Server 2016 introduces a new batch
mode top sort operator that boosts performance for many analytical queries.
9349
Scope: global or session or query
Enables 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
9389 mode operator may ask for additional memory and avoid spilling to tempdb if additional
memory is available.

Scope: global or session

9453 Disables batch mode execution. For more information, see this Microsoft Support article.
Trace
Description
flag

Note: Please ensure that you thoroughly test this option, before rolling it into a
production environment.

Scope: global and session and query

Causes SQL Server to generate a plan using minimum selectivity for single-table filters,
under the query optimizer cardinality estimation model of SQL Server 2014 through SQL
Server 2017 versions.

Starting with SQL Server 2016 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.
9471
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

Causes 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 2014 through SQL Server 2017 versions. For more
information, see this Microsoft Support article.

Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE
9476 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

Enables you to set the query optimizer cardinality estimation model to the SQL Server
2012 and earlier versions, irrespective of the compatibility level of the database. For more
information, see Microsoft Support article. To accomplish this at the database level, see
the LEGACY_CARDINALITY_ESTIMATION option in ALTER DATABASE
SCOPED CONFIGURATION (Transact-SQL).
9481
Starting with SQL Server 2016 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.
Trace
Description
flag

Scope: global only

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 2008
R2 and earlier versions), when using the query optimizer cardinality estimation model of
9488
SQL Server 2012 through SQL Server 2017 versions.

Scope: global or session or query

Disables parallelism during insertion for INSERT...SELECT operations and it applies to
both user and temporary tables. For more information, see Microsoft Support article
9495
Scope: global or session
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
9567 Automatically initialize Always On availability group and Tune compression for
availability group.

Scope: global or session

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 and SQL Server 2014. In SQL Server 2016, compression is
9591
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

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 2016. For more information,
see this Microsoft Support article.
9939
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
2016, 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.
Trace
Description
flag
Note: Trace flag 10204 does not apply to columnstore indexes which are created on
memory-optimized tables.

Scope: global or session

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

10316 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

Remarks
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.

Examples
The following example sets trace flag 3205 on for all sessions at the server level by using DBCC
TRACEON.

t-sql
DBCC TRACEON (3205,-1);

You can enable all plan-affecting hotfixes controlled by trace flags 4199 and 4137 for a
particular query.

t-sql
SELECT x FROM correlated WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199,
QUERYTRACEON 4137)