Trace Flags (Transact-SQL)

 

Updated: December 2, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel 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.

System_CAPS_ICON_note.jpg 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.

System_CAPS_ICON_important.jpg Important

Trace flag behavior may not be supported in future releases of SQL Server.

Trace flagDescription
174Increases 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.

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

Scope: global only
205Reports 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
260Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about GetXpVersion(), see Creating Extended Stored Procedures.

Scope: global or session
610Controls minimally logged inserts into indexed tables. For more information, see Data Loading Performance Guide.

Scope: global or session
634Disables 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 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
652Disables page pre-fetching scans. For more information, see this Microsoft Support article.

Scope: global or session
661Disables the ghost record removal process. For more information, see this Microsoft Support article.

Scope: global only
715Enables 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
834Uses 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 SQL Server 2012 to SQL Server 2016, we do not recommend turning on trace flag 834.

Scope: global only
902Bypasses 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 SQL Server instance.

Scope: global only
1117When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.

Note: Beginning 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 affect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

Scope: global only
1118Removes 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: Beginning with SQL Server 2016 this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no affect. For more information, see ALTER DATABASE SET Options (Transact-SQL).

Scope: global only
1204Returns 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
1211Disables 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.

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
1222Returns 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
1224Disables 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:

- 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- or HoBT-level granularity can also be controlled by using the LOCK_ESCALATION option of the ALTER TABLE statement.

Scope: global or session
1236Enables database lock partitioning. For more information, see this Microsoft Support article.

Note: Beginning 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
1260Disable scheduler monitor dumps.

Scope: global only
1448Enables 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 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
1462Disables log stream compression for asynchronous availability groups. This feature is enabled by default on asynchronous availability groups in order to optimize network bandwidth. For more information, see Tune compression for availability group.

Scope: global only
1800Enables SQL Server optimization when disks of different sector sizes are used for primary and secondary replica log files, in SQL Server AG and Log Shipping environments. For more information, see this Microsoft Support article.

Scope: global only
2301Enable advanced decision support optimizations. For more information, see this Microsoft Support article.

Scope: global and session and query
2312Enables you to set the query optimizer cardinality estimation model to the SQL Server 2014 through SQL Server 2016 versions, dependent of the compatibility level of the database. For more information, see Microsoft Support article.

Scope: global or session or query
2335Causes 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.

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

Scope: global or session or query
2340Causes 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.

Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT 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
2371Changes the fixed auto update statistics threshold to dynamic auto update statistics threshold. For more information, see this Microsoft Support article.

Note: Beginning with SQL Server 2016 this behavior is controlled by the engine and trace flag 2371 has no effect.

Scope: global only
2389Enable 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 production environment.

Scope: global or session or query
2390Enable 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 production environment.

Scope: global or session or query
2453Allows a table variable to trigger recompile when enough number of rows are changed. 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 or session or query
2528Disables 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.

Parallel DBCC should typically be left enabled. For DBCC CHECKDB, the query processor reevaluates and automatically adjusts parallelism with each table or batch of tables checked. Sometimes, checking may start when the server is almost idle. An administrator who knows that the load will increase before checking is complete may want to manually decrease or disable parallelism.

Disabling parallel checking of DBCC can cause DBCC to take much longer to complete and if DBCC is run with the TABLOCK feature enabled and parallelism set off, tables may be locked for longer periods of time.

Scope: global or session
2549Runs 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.

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
2562Runs 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 the DBCC CHECKDB command. For more information, see Microsoft Support article.

Scope: global only
2566Runs 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, 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
3023Enables CHECKSUM option as default for BACKUP command. For more information, see this Microsoft Support article.

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

Scope: global and session
3042Bypasses 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 duration of the backup operation). For more information about the pre-allocation algorithm, see Backup Compression (SQL Server).

Scope: global only
3051Enables 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.

Scope: global only
3205By 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 that do not support compression.

Scope: global or session
3226By 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.

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
3608Prevents 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. Use for Move System Databases and Move User Databases.

Note: Do not use during normal operation.

Scope: global only
3625Limits 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
4136Disables 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 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). To accomplish this at the query level, add the OPTIMIZE FOR UNKNOWN query hint. Beginning with SQL Server 2016 SP1, a second option to accomplish this at the query level is to add the USE HINT 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
4137Causes 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.

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

Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT 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
4139Enable 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.

Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT 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
4199Controls query optimizer changes released in SQL Server Cumulative Updates and Service Packs. Starting with SQL Server 2016, trace flag 4199 changes that are made to previous releases of SQL Server will become enabled under database compatibility level 130 without trace flag 4199 enabled. For more information, see this Microsoft Support article.

To enable this at the database level, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Note: Starting with SQL Server 2016, customers are advised to remove trace flag 4199 after they migrate their databases to the latest compatibility level because trace flag 4199 will be reused for future query optimizer changes that may not apply to your application and could cause unexpected plan performance changes on a production system. This means that different trace flag 4199 changes are enabled for each compatibility level that is supported in a given product release.

Scope: global or session or query
4610Increases 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 Support article.

Scope: global only
4616Makes 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 of SQL Server. Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata.

Scope: global only
4618Limits 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 Support article.

Scope: global only
6498Enables 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.

Note: Beginning 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
6527Disables 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 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
6532Enables 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.

Note: Beginning with SQL Server 2016 this behavior is controlled by the engine and trace flag 6532 has no effect.

Scope: global and session
6533Enables 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.

Note: Beginning with SQL Server 2016 this behavior is controlled by the engine and trace flag 6533 has no effect.

Scope: global and session
6534Enables 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 information, see this Microsoft Support article.

Scope: global and session
7314Forces 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
7412Enables the lightweight query execution statistics profiling infrastructure. For more information, see this Microsoft Support article.

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

Scope: global only
8011Disable the ring buffer for Resource Monitor. For more information, see this Microsoft Support article.

Scope: global and session
8012Disable the ring buffer for schedulers. For more information, see this Microsoft Support article.

Scope: global only
8015Disable auto-detection and NUMA setup. For more information, see this Microsoft Support article.

Scope: global only
8018Disable the exception ring buffer. For more information, see this Microsoft Support article.

Scope: global only
8019Disable stack collection for the exception ring buffer. For more information, see this Microsoft Support article.

Scope: global only
8020Disable working set monitoring. For more information, see this Microsoft Support article.

Scope: global only
8032Reverts 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.

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
8048Converts NUMA partitioned memory objects into CPU partitioned. For more information, see this Microsoft Support article.

Note: Beginning 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
8079Allows 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.

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

Scope: global only
8207Enables singleton updates for Transactional Replication. 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 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
8721Reports to the error log when auto-update statistics executes. For more information, see this Microsoft Support article.

Scope: global only
8744Disable 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 executes plans that contain the Nested Loops operator.

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

Note: Beginning 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
9347Disables 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 Microsoft Support article.

Scope: global or session or query
9349Disables 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.

Scope: global or session or query
9389Enables 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.

Scope: global or session
9471Causes 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 2016 versions.

Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT 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
9476Causes 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 2016 versions. For more information, see Microsoft Support article.

Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT 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
9481Enables 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 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

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

Scope: global or session or query
9485Disables SELECT permission for DBCC SHOW_STATISTICS.

Scope: global only
9495Disables 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
9567Enables compression of the data stream for 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
9592Enables 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
10204Disables 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.

Note: Trace flag 10204 does not apply to columnstore indexes which are created on memory-optimized tables.

Scope: global or session
10316Enables 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

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, DBCC TRACEON 2528: To enable the 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.

DBCC TRACEON (3205,-1);  

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

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

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)

Community Additions

ADD
Show: