--- title: "Trace Flags (Transact-SQL)" description: "Learn how to set specific server characteristics or to alter a particular behavior in SQL Server, using DBCC TRACEON." author: rwestMSFT ms.author: randolphwest ms.reviewer: wiassaf, randolphwest ms.date: 10/24/2022 ms.service: sql ms.subservice: t-sql ms.topic: "language-reference" helpviewer_keywords: - "trace flags [SQL Server], about trace flags" - "trace flags [SQL Server]" - "global trace flags [SQL Server]" - "flags [SQL Server]" - "session trace flags [SQL Server]" - "performance [SQL Server], trace" - "debugging [SQL Server], trace flags" dev_langs: - "TSQL" --- # DBCC TRACEON - Trace Flags (Transact-SQL) [!INCLUDE[tsql-appliesto-ss-asdmi-md](../../includes/applies-to-version/sql-asdbmi.md)] Trace flags are used to set specific server characteristics or to alter a particular behavior. For example, trace flag 3226 is a commonly used startup trace flag that suppresses successful backup messages in the error log. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems, but they may also be recommended by Microsoft Support to address behavior that is negatively impacting a specific workload. All documented trace flags and those recommended by Microsoft Support are fully supported in a production environment when used as directed. Trace flags in this list may have additional considerations regarding their particular usage, so it is advisable to carefully review all the recommendations given here and/or by your support engineer. Also, as with any configuration change in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], it is always best to thoroughly test the flag in a non-production environment before deploying. ## Remarks In [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], there are three scopes at which trace flags can work: 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 on Windows, or using **mssql-conf** [on Linux](../../linux/sql-server-linux-configure-mssql-conf.md). This ensures the trace flag remains active after a server restart. Restart SQL Server for the trace flag to take effect. - 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 signs 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](../../t-sql/database-console-commands/dbcc-traceon-transact-sql.md) 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](../../t-sql/database-console-commands/dbcc-traceoff-transact-sql.md) 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 can't 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](../../database-engine/configure-windows/database-engine-service-startup-options.md). - At the query level, by using the QUERYTRACEON [query hint](https://support.microsoft.com/kb/2801413). The QUERYTRACEON option is only supported for Query Optimizer trace flags documented in the table below. 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`. ```sql DBCC TRACEON (3205, -1); ``` You can enable all plan-affecting hotfixes controlled by trace flags 4199 and 4137 for a particular query. ```sql SELECT x FROM correlated WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199, QUERYTRACEON 4137); ``` ## Trace flags The following table lists and describes the trace flags that are available in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Azure SQL Managed Instance supports the following global Trace Flags: 460, 2301, 2389, 2390, 2453, 2467, 7471, 8207, 9389, 10316, and 11024. Session trace-flags aren't yet supported in SQL Managed Instance. Some trace flags were introduced in specific [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] versions. For more information on the applicable version, see the Microsoft Support article associated with a specific trace flag. Trace flag behavior may not be supported in future releases of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Тrace flags can be referenced directly in the table via a bookmark that you can add to the end of the URL, using this format #tfNNNN. For example, to jump directly to trace flag 1118 in the table, use `dbcc-traceon-trace-flags-transact-sql#tf1118`. |Trace flag|Description| |---|---| |**101**|Increases the verboseness of the merge replication agent logging.

**Important:** Trace flag 101 can only be enabled for the [Replication Merge Agent](../../relational-databases/replication/agents/replication-merge-agent.md) using the `-T` option when executing **replmerg.exe** from the command prompt.

**Warning:** Trace flag 101 isn't meant to be enabled continuously in a production environment, but only for time-limited troubleshooting purposes. For more information, see an archived version of [KB2892633](https://mskb.pkisolutions.com/kb/2892633).

**Scope:** Replication Merge Agent only.| |**102**|Increases the verboseness of the merge replication agent logging and directs it to the *\..msmerge_history* table.

**Important:** Trace flag 102 can only be enabled for the [Replication Merge Agent](../../relational-databases/replication/agents/replication-merge-agent.md) using the `-T` option when executing **replmerg.exe** from the command prompt.

**Warning:** Trace flag 102 isn't meant to be enabled continuously in a production environment, but only for time-limited troubleshooting purposes. For more information, see an archived version of [KB2892633](https://mskb.pkisolutions.com/kb/2892633).

**Scope:** Replication Merge Agent only.| |**139**| Forces correct conversion semantics in the scope of DBCC check commands like [DBCC CHECKDB](../../t-sql/database-console-commands/dbcc-checkdb-transact-sql.md), [DBCC CHECKTABLE](../../t-sql/database-console-commands/dbcc-checktable-transact-sql.md) and [DBCC CHECKCONSTRAINTS](../../t-sql/database-console-commands/dbcc-checkconstraints-transact-sql.md), 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 [KB4010261](https://support.microsoft.com/kb/4010261).

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] RTM CU3, [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1 and later.

**Warning:** Trace flag 139 isn't meant to be enabled continuously in a production environment, and should be used for the sole purpose of performing database validation checks described in [KB4010261](https://support.microsoft.com/kb/4010261). It should be immediately disabled after validation checks are completed.

**Scope:** Global only.| |**174**|Increases the [!INCLUDE[ssDEnoversion](../../includes/ssdenoversion-md.md)] plan cache bucket count from 40,009 to 160,001 on 64-bit systems. For more information, see [KB3026083](https://support.microsoft.com/kb/3026083).

**Note:** 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 [KB3213683](https://support.microsoft.com/kb/3213683) and [KB4541096](https://support.microsoft.com/kb/4541096).

**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 an archived version of [KB195565](https://mskb.pkisolutions.com/kb/195565).

**Note:** This trace flag requires trace flag [3605](#3605) to be enabled.

**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](../../relational-databases/extended-stored-procedures-programming/creating-extended-stored-procedures.md).

**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. Identity caching is used to improve INSERT performance on tables with identity columns.

**Note:** Starting with [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)], to accomplish this at the database level, see the IDENTITY_CACHE option in [ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md).

**Scope:** Global only.| |**460**|Replaces data truncation message ID [8152](../../relational-databases/errors-events/database-engine-events-and-errors.md#errors-8000-to-8999) with message ID [2628](../../relational-databases/errors-events/database-engine-events-and-errors.md#errors-2000-to-2999). For more information, see [KB4468101](https://support.microsoft.com/kb/4468101).

Starting with [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)], to accomplish this at the database level, see the VERBOSE_TRUNCATION_WARNINGS option in [ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md).

**Note:** This trace flag applies to [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU12 and later.

**Note:** Starting with database compatibility level 150, message ID 2628 is the default and this trace flag has no effect.

**Scope:** Global or session.| |**610**|Controls minimally logged inserts into indexed tables. This trace flag isn't required starting SQL Server 2016 as minimal logging is turned on by default for indexed tables. In [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], 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](/previous-versions/sql/sql-server-2008/dd425070(v=sql.100)).

**Scope:** Global or session.| |**634**|Disables the background columnstore compression task. [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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. If you turn on trace flag 652, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] no longer brings database pages into the buffer pool before these database pages are consumed by the scans. As a result, queries that benefit from the page pre-fetching feature exhibit lower performance.

**Scope:** Global or session.| |**661**|Disables the ghost record removal process. Trace flag 661 disables the ghost record removal process. A ghost record is the result of a delete operation. When you delete a record, the deleted record is kept as a ghost record. Later, the deleted record is purged by the ghost record removal process. When you disable this process, the deleted record isn't purged. Therefore, the space that the deleted record consumes isn't freed. This behavior affects space consumption and the performance of scan operations. For more information, review the [Ghost cleanup process guide](../../relational-databases/ghost-record-cleanup-process-guide.md).

**Scope:** Global only.| |**692**|Disables fast inserts while bulk loading data into heap or clustered index. Starting [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], fast inserts is enabled by default, using 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 isn't feasible, this trace flag can help reduce unused space reserved at the expense of performance.

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and later.

**Scope:** Global or session.| |**715**|Enables table lock for bulk load operations into a heap with no nonclustered 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 aren't 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.| |**809**|Enables what is referred to as _Direct Write_ behavior for the [Hybrid buffer pool](../../database-engine/configure-windows/hybrid-buffer-pool.md). This mode requires that [persisted log buffer](../../relational-databases/databases/add-persisted-log-buffer.md) be enabled in the same database as hybrid buffer pool. `Direct Write` allows the Hybrid buffer pool to serve as a cache for both dirty and clean pages, reducing the workload demands on the DRAM buffer pools. This optional but recommended behavior was introduced in [!INCLUDE[sssql22-md](../../includes/sssql22-md.md)]. |**818**|Enables additional I/O diagnostics to check for Lost Write or Stale Read conditions during file I/O operations. Trace flag 818 enables an in-memory ring buffer that is used for tracking the last 2,048 successful write operations that are performed by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], not including sort and workfile I/Os. When errors such as Error 605, 823, or 3448 occur, the incoming buffer's log sequence number (LSN) value is compared to the recent write list. If the LSN that is retrieved is older than the one specified during the write operation, a new error message is logged in the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] error log. For more information, see [KB826433](https://support.microsoft.com/kb/826433).

**Note:** Starting with [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] this trace flag has no effect.

**Scope:** Global only.| |**830**|Disables detection and reporting of I/O requests that take a very long time to complete. By default [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] uses a mechanism to detect read and write I/O requests that take a long time (typically longer than 15 seconds). This trace flag disable this detection. For more information see [MSSQLSERVER_833](../../relational-databases/errors-events/mssqlserver-833-database-engine-error.md)

**Note:** It is not recommended that you enable this trace flag because you may decrease your ability to detect I/O issues on the system.

**Scope:** Global only.| |**834**|Uses large-page allocations for the buffer pool, columnstore, and in-memory tables. The large page size varies depending on the hardware platform, but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU. In other words, trace flag 834 increases the efficiency of managing physical to virtual memory address translation that is performed by memory management hardware. For information on SQL Server and Large Pages, review the following [blog](https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-and-large-pages-explained-8230/ba-p/315787).

**Note:** Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup. Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages can't be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated to SQL Server.

**Note:** When enabled, the large-page memory model pre-allocates all SQLOS memory at instance startup and doesn't return that memory to the OS.

**Note:** If you are using the Columnstore Index feature of [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] and later, we don't recommend turning on trace flag 834. For more information, see [KB3210239](https://support.microsoft.com/kb/3210239). If using [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] and columnstore, see trace flag [876](#876) instead.

**Scope:** Global only.| |**836**|Use the max server memory option for the buffer pool. Trace flag 836 causes SQL Server to size the buffer pool at startup based on the value of the max server memory option instead of based on the total physical memory. You can use trace flag 836 to reduce the number of buffer descriptors that are allocated at startup in 32-bit Address Windowing Extensions (AWE) mode.

**Note:** Trace flag 836 applies only to 32-bit versions of SQL Server that have the AWE allocation enabled. You can turn on trace flag 836 only at startup.

**Scope:** Global only.| |**845**|Enables locked pages on Standard SKUs of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], when the service account for [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] has the Lock Pages in Memory privilege enabled. For more information, see [KB970070](https://support.microsoft.com/kb/970070) and [Server Memory Server Configuration Options](../../database-engine/configure-windows/server-memory-server-configuration-options.md#lock-pages-in-memory-lpim).

**Note:** Starting with [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] this behavior is enabled by default for Standard SKUs, and trace flag 845 must not be used.

**Scope:** Global only.| |**876**|Uses large-page allocations for columnstore.

**Note:** Unlike trace flag 834, using trace flag 876 doesn't pre-allocate SQLOS memory at instance startup, and unused memory can be released.

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] and later.

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

**Scope:** Global only.| |**890**|Suppress long buffer pool scan complete messages ([error 898](../../relational-databases/errors-events/mssqlserver-898-database-engine-error.md)) in the error log. For more information on buffer pool scan and the message that is logged in the error log, see [Operations that trigger a buffer pool scan may run slowly on large-memory computers](/troubleshoot/sql/performance/buffer-pool-scan-runs-slowly-large-memory-machines).

**Note:** Starting with [!INCLUDE[sssql17-md](../../includes/sssql17-md.md)]

**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 [KB2163980](https://support.microsoft.com/kb/2163980).

**Warning:** This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it isn't supported to run it continuously in a production environment. Database upgrade scripts need to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance.

**Scope:** Global only.| |**1117**|When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. This trace flag affects all databases. It is recommended only if in every database it is safe to grow all files in a filegroup by the same amount.

**Note:** Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] 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)](../../t-sql/statements/alter-database-transact-sql-file-and-filegroup-options.md).

**Scope:** Global only.| |**1118**|Forces page allocations on uniform extents instead of mixed extents, 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 [KB328551](https://support.microsoft.com/kb/328551).

**Note:** Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] 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)](../../t-sql/statements/alter-database-transact-sql-set-options.md).

**Scope:** Global only.| |**1204**|Returns the resources and types of locks participating in a deadlock and also the current command affected. For more information about deadlocks, see the [Transaction Locking and Row Versioning Guide](../../relational-databases/sql-server-transaction-locking-and-row-versioning-guide.md#deadlocks).

**Note:** Avoid using trace flag 1204 on workload-intensive systems causing deadlocks. For more information about other means of detecting deadlocks, see the [Transaction Locking and Row Versioning Guide](../../relational-databases/sql-server-transaction-locking-and-row-versioning-guide.md#deadlock_detection).

**Scope:** Global only.| |**1211**|Disables lock escalation based on memory pressure, or based on number of locks. The [!INCLUDE[ssDEnoversion](../../includes/ssdenoversion-md.md)] won't escalate row or page locks to table locks.

Using this trace flag can generate excessive number of locks and if the lock memory grows large enough, attempts to allocate additional locks for any query may fail. This can slow the performance of the [!INCLUDE[ssDE](../../includes/ssde-md.md)], or cause 1204 errors (unable to allocate lock resource) because of insufficient memory.

If both trace flags 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 instead. This helps avoid "out-of-locks" errors when many locks are being used.

For more information on how to resolve blocking problems that are caused by lock escalation in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], see [KB323630](https://support.microsoft.com/kb/323630).

**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 doesn't comply with any XSD schema. For more information about deadlocks, see the [Transaction Locking and Row Versioning Guide](../../relational-databases/sql-server-transaction-locking-and-row-versioning-guide.md#deadlocks).

**Note:** Avoid using trace flag 1222 on workload-intensive systems causing deadlocks. For more information about other means of detecting deadlocks, see the [Transaction Locking and Row Versioning Guide](../../relational-databases/sql-server-transaction-locking-and-row-versioning-guide.md#deadlock_detection).

**Scope:** Global only.| |**1224**|Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. The [!INCLUDE[ssDE](../../includes/ssde-md.md)] 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:

If both trace flags 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](../../t-sql/statements/alter-table-transact-sql.md) statement.

For more information on how to resolve blocking problems that are caused by lock escalation in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], see [KB323630](https://support.microsoft.com/kb/323630).

**Scope:** Global or session.| |**1229**|Disables all lock partitioning regardless of the number of CPUs. By default, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] enables lock partitioning when a server has 16 or more CPUs, to improve the scalability characteristics of larger systems. For more information on lock partitioning, see the [Transaction Locking and Row Versioning Guide](../../relational-databases/sql-server-transaction-locking-and-row-versioning-guide.md#lock_partitioning).

**Warning:** Trace flag 1229 can cause spinlock contention and poor performance.

**Scope:** Global only.| |**1236**|Enables database lock partitioning. For more information, see [KB2926217](https://support.microsoft.com/kb/2926217).

**Note:** Starting with [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] SP3 and [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP1 this behavior is controlled by the engine and trace flag 1236 has no effect.

**Scope:** Global only.| |**1237**|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 [KB4025261](https://support.microsoft.com/kb/4025261).

**Note:** Starting with [!INCLUDE[sssql17](../../includes/sssql17-md.md)] and database [compatibility level](../../t-sql/statements/alter-database-transact-sql-compatibility-level.md) 140 this is the default behavior and trace flag 1237 has no effect.

**Scope:** Global or session or query (QUERYTRACEON).| |**1260**|Disable scheduler monitor dumps.

**Scope:** Global only.| |**1448**|Enables the replication log reader to move forward even if the asynchronous secondaries have not acknowledged the reception of a change. Even with this trace flag enabled the log reader always waits for the synchronous secondaries whose synchronization state is SYNCHRONIZED. The log reader won't go beyond the minimum acknowledged Log Sequence Number of the SYNCHRONIZED secondaries. This trace flag applies to the instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], 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 asynchronous secondary replica fails.

**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 bandwidth. For more information, see [Tune compression for availability group](../../database-engine/availability-groups/windows/tune-compression-for-availability-group.md).

**Scope:** Global only.| |**1800**|Enables [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] optimization when disks of different sector sizes are used for primary and secondary replica log files, in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Always On and Log Shipping environments. This trace flag is only required to be enabled on [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instances with transaction log file residing on disk with sector size of 512 bytes. It is **not** required to be enabled on disk with 4-KB sector sizes. For more information, see [KB3009974](https://support.microsoft.com/kb/3009974).

**Note:** This trace flag applies to [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] SP1 CU13, [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] SP2 CU3, [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] RTM CU5, and later.

**Scope:** Global only.| |**1802**|Disables ACL change and impersonated access verification during database attach or detach operations. This may be useful when attaching a database and encountering access permission errors, such as error 5120.

**Scope:** Global only.| |**1819**| Allows [backup to URL](../../relational-databases/backup-restore/sql-server-backup-to-url.md) to use a proxy server when accessing Azure block blobs. In addition to this trace flag, you must set the WinHTTP proxy configuration on the server via one of the following methods:

- The [proxycfg.exe](/windows/win32/winhttp/proxycfg-exe--a-proxy-configuration-tool) utility on [!INCLUDE [winxp-md](../../includes/winxp-md.md)] or [!INCLUDE [winserver2003-md](../../includes/winserver2003-md.md)] and earlier.
- The [netsh.exe](/windows/win32/winsock/netsh-exe) utility on [!INCLUDE [winvista-md](../../includes/winvista-md.md)] and [!INCLUDE [winserver2008-md](../../includes/winserver2008-md.md)] and later.

**Scope:** Global or session or query (QUERYTRACEON).| |**2301**|Enable advanced decision support optimizations that are specific to decision support queries. This option applies to decision support processing of large data sets.

**Scope:** Global or session or query (QUERYTRACEON).| |**2312**|Enables you to set the Query Optimizer cardinality estimation model to [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] and later, irrespective of the compatibility level of the database.

**Note:** If the database compatibility level is lower than 120, enabling trace flag 2312 uses the cardinality estimation model of [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] (120). For more information, see [KB2801413](https://support.microsoft.com/kb/2801413).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

**Scope:** Global or session or query (QUERYTRACEON).| |**2335**|Causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to assume a fixed amount of memory is available during query optimization, for a scenario where the [max server memory server configuration](../../database-engine/configure-windows/server-memory-server-configuration-options.md) is set too high, and causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to generate an inefficient plan for a specific query. It doesn't limit the memory [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] grants to execute the query. The memory configured for [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] will still be used by data cache, query execution and other consumers.

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

**Scope:** Global or session or query (QUERYTRACEON).| |**2338**|Causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to use a narrow plan when executing an UPDATE statement to update indexes in a table. When you do an UPDATE against a clustered index column, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] updates not only the clustered index itself, but also all the non-clustered indexes because the non-clustered indexes contain the cluster key. A common way that the update occurs is to update the clustered index, and then all non-clustered indexes at the same time. [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] would update one row, then move to next row until all is complete. This is called narrow plan update or also called Per-Row Update. In some cases the database engine can choose to do a wide plan update. This trace flag forces a narrow plan update.

**Scope:** Global or session or query (QUERYTRACEON).| |**2340**|Causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] not to use a sort operation (batch sort) for optimized Nested Loops joins when generating a plan. By default, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] can use an optimized Nested Loops join instead of a full scan or a Nested Loops join with an explicit Sort, when the Query Optimizer concludes that a sort is most likely not required, but still a possibility if the cardinality or cost estimates are incorrect. For more information, see [KB2009160](https://support.microsoft.com/kb/2009160).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'DISABLE_OPTIMIZED_NESTED_LOOP'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

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

**Scope:** Global or session or query (QUERYTRACEON).| |**2371**|Changes the fixed update statistics threshold to a linear update statistics threshold. For more information, see this [AUTO_UPDATE_STATISTICS Option](../../relational-databases/statistics/statistics.md#auto_update_statistics-option).

**Note:** Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] and under the [database compatibility level](../../relational-databases/databases/view-or-change-the-compatibility-level-of-a-database.md) 130 or above, this behavior is controlled by the engine and trace flag 2371 has no effect.

**Scope:** Global only.| |**2389**|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.

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

**Note:** This trace flag doesn't apply to CE version 120 or above. Use trace flag 4139 instead.

**Scope:** Global or session or query (QUERYTRACEON).| |**2390**|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 [KB2801413](https://support.microsoft.com/kb/2801413).

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

**Note:** This trace flag doesn't apply to CE version 120 or above. Use trace flag 4139 instead.

**Scope:** Global or session or query (QUERYTRACEON).| |**2422**|Enables the [!INCLUDE[ssDEnoversion](../../includes/ssdenoversion-md.md)] to abort a request when the maximum time set by Resource Governor REQUEST_MAX_CPU_TIME_SEC configuration is exceeded. For more information, see [KB4038419](https://support.microsoft.com/kb/4038419).

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2, [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU3, and later.

**Scope:** Global only.| |**2430**|Enables alternate lock class cleanup. For more information, see [KB2754301](https://support.microsoft.com/kb/2754301).

**Scope:** Global only.| |**2446**|Causes SQL Server to generate a Showplan XML fragment with the ParameterRuntimeValue when using the lightweight query execution statistics profiling infrastructure or executing the `sys.dm_exec_query_statistics_xml` DMV while troubleshooting long running queries.

**Note:** This trace flag applies to [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU31, and [!INCLUDE[ssSQL22](../../includes/sssql22-md.md)] and later.

**Warning:** Trace flag 2446 isn't meant to be enabled continuously in a production environment, but only for time-limited troubleshooting purposes. Using this trace flag will introduce additional and possibly significant CPU and memory overhead as we will create a Showplan XML fragment with runtime parameter information, whether the `sys.dm_exec_query_statistics_xml` DMV is called or not.

**Scope:** Global only.| |**2451**|Enables the equivalent of the last actual execution plan in `sys.dm_exec_query_plan_stats`.

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] and later.

**Note:** Starting with [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] to accomplish this at the database level see the LAST_QUERY_PLAN_STATS option in [ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md).

**Scope:** Global only.| |**2453**|Allows a table variable to trigger recompile when enough number of rows are changed. For more information, see [KB2952444](https://support.microsoft.com/kb/2952444).

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

**Scope:** Global or session or query (QUERYTRACEON).| |**2467**|Enables an alternate parallel worker thread allocation policy, based on which node has the least allocated threads. For more information, see [Parallel Query Processing](../../relational-databases/query-processing-architecture-guide.md#parallel-query-processing). Refer to [Configure the max worker threads Server Configuration Option](../../database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option.md) for information on configuring the max worker threads server option.

**Note:** Query degree of parallelism (DOP) has to fit into a single node for this alternate policy to be used, or the default thread allocation policy is used instead. Using this trace flag, it isn't recommended to execute queries specifying a DOP over the number of schedulers in a single node, as this could interfere with queries specifying a DOP below or equal to the number of schedulers in a single node.

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

**Scope:** Global only.| |**2469**|Enables alternate exchange for `INSERT INTO ... SELECT` into a partitioned columnstore index. For more information, see [KB3204769](https://support.microsoft.com/kb/3204769).

**Scope:** Global or session or query (QUERYTRACEON).| |**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](../../database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option.md).

**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 [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP2, a MAXDOP option is available to override the max degree of parallelism configuration option of sp_configure for the DBCC statements.

**Scope:** Global or session.| |**2544**|Causes a memory dump of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to become a full dump (default is mini dump). Full dumps are a complete copy of the active target process memory. That would include all thread state, all process allocated memory, and all loaded modules. Full dumps will therefore have a size roughly the amount of memory used by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] process, which in turn may be almost as large as total system RAM. On large servers dedicated to a single [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance, that might mean a file that is several hundreds of gigabytes or more.

**Warning:** Generating a full memory dump can suspend the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] process for an extended period of time (several seconds to several minutes) and may generate a very large dump file. Use this with caution and only rarely if the situation requires it.

For more detailed information, see [Use the Sqldumper.exe utility to generate a dump file in SQL Server](/troubleshoot/sql/tools/use-sqldumper-generate-dump-file).

**Scope:** Global only.| |**2549**|Forces the `DBCC CHECKDB` command to assume each database file is on a unique disk drive but treating different physical files as one logical file. `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:** Don't use this trace flag unless you know that each file is based on a unique physical disk.

**Note:** Although this trace flag improves the performance of the `DBCC CHECKDB` commands that 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 [KB2634571](https://support.microsoft.com/kb/2634571).

**Scope:** Global only.| |**2551**|Causes a memory dump of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to become a filtered dump (default is mini dump). This captures a percentage of full memory, where large areas of memory structures pertaining to [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] are purposefully filtered out and not serialized to disk as they bring no troubleshooting added value (typically, data/index pages, some internal caches like [!INCLUDE [inmemory-md](../../includes/inmemory-md.md)] data pages and Log Pool memory). This results in a file, which is smaller than a full memory dump while retaining most of its usefulness as preferred option in most situations where mini dumps aren't sufficient. For more detailed information, see [Use the Sqldumper.exe utility to generate a dump file in SQL Server](/troubleshoot/sql/tools/use-sqldumper-generate-dump-file).

**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. But 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 improves the performance of the `DBCC CHECKDB` commands that 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 [KB2634571](https://support.microsoft.com/kb/2634571).

**Scope:** Global or session.| |**2566**|Runs the `DBCC CHECKDB` command without data purity check unless the `DATA_PURITY` option is specified.

**Note:** Column-value integrity checks are enabled by default and don't require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks aren't 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 an archived version of [KB945770](https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/945770).

**Scope:** Global only.| |**2592**|Enables symbol resolution on stack dumps when the *Debugging Tools for Windows* are installed. For example, using trace flag 3656 requires that trace flag 2592 is enabled.

**Warning:** This is a debugging trace flag and not meant for production environment use.

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] and later.

**Scope:** Global and session.| |**3023**|Enables CHECKSUM option as default for BACKUP command.

**Note:** Starting with [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] this behavior is controlled by setting the **backup checksum default** configuration option. For more information, see [backup checksum default](../../database-engine/configure-windows/backup-checksum-default.md) and [Server Configuration Options (SQL Server)](../../database-engine/configure-windows/server-configuration-options-sql-server.md).

**Scope:** Global and session.| |**3042**|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 duration of the backup operation). For more information about the pre-allocation algorithm, see [Backup Compression (SQL Server)](../../relational-databases/backup-restore/backup-compression-sql-server.md).

**Scope:** Global only.| |**3051**|Enables [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Backup to URL logging to a specific error log file. For more information, see [SQL Server Backup to URL Best Practices and Troubleshooting](../../relational-databases/backup-restore/sql-server-backup-to-url-best-practices-and-troubleshooting.md).

**Scope:** Global only.| |**3205**|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 that don't support compression.

**Scope:** Global or session.| |**3226**|By default, every successful backup operation adds an entry in the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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 becomes 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.| |**3427**|Enables fix for issue when many consecutive transactions insert data into temp tables in [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] where this operation consumes more CPU than in [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)]. For more information, see [KB3216543](https://support.microsoft.com/kb/3216543).

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1 CU2 through [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2 CU2. Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2 CU3 and [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)], this trace flag has no effect.

**Scope:** Global only.| |**3459**|Disables parallel redo. For more information, see [KB3200975](https://support.microsoft.com/kb/3200975), [KB4101554](https://support.microsoft.com/kb/4101554) and this blog post, [Availability group secondary replica redo model and performance](https://techcommunity.microsoft.com/t5/sql-server-blog/sql-server-2016-2017-availability-group-secondary-replica-redo/ba-p/385905).

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)], and later.

**Scope:** Global only.| |**3468**|Disables [indirect checkpoints](../../relational-databases/logs/database-checkpoints-sql-server.md#IndirectChkpt) on `tempdb`.

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1 CU5, [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU1 and later.

**Scope:** Global only.| |**3605**|Redirects tracing messages to the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] error log. For example, using trace flags 205 and 8721 require trace flag 3605 to be enabled.

**Warning:** This is a debugging trace flag and not meant to be enabled continuously in a production environment.

**Scope:** Global or session.| |**3608**|Prevents [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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](../../relational-databases/databases/move-system-databases.md) and [Move User Databases](../../relational-databases/databases/move-user-databases.md).

**Note:** Don't use during normal operation.

**Scope:** Global only.| |**3625**|Limits the amount of information returned to users who aren't 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.| |**3656**|Enables symbol resolution on stack dumps when the Debugging Tools for Windows are installed.

**Warning:** This is a debugging trace flag and not meant for production environment use.

**Note:** Starting with [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)], trace flag [2592](#2592) must be enabled with trace flag 3656 to enable symbol resolution.

**Scope:** Global and session.| |**3924**|Enables automatic removal of orphaned DTC transactions with SPID=-2, which is a problem for some third party transaction monitors. For more information, see [KB4519668](https://support.microsoft.com/kb/4519668) and [KB4511816](https://support.microsoft.com/kb/4511816).

**Scope:** Global only.| |**3972**|Disables concurrent Page Free Space (PFS) updates feature. For more information on concurrent PFS updates, see [Intelligent Performance](../../sql-server/what-s-new-in-sql-server-2019.md#intelligent-performance). For an issue where this trace flag is useful see Non-yielding scheduler dumps during the recovery of a secondary availability database with a database snapshot [KB5007794](https://support.microsoft.com/kb/5007794).

**Scope:** Global only.| |**4022**|Disables automatic execution of stored procedures when [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] starts. For more information about automatic execution of startup stored procedures, see [sp_procoption](../../relational-databases/system-stored-procedures/sp-procoption-transact-sql.md).

**Scope:** Global only.| |**4043**| Fixes an error that occurs when you apply a security policy on PolyBase external table and use Row-Level Security (RLS) in [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)]. The error message resembles the following: "Security predicates can only be added to user tables and schema bound views" For more information, see [KB4552159](https://support.microsoft.com/kb/4552159).

**Scope:** Global or session.| |**4136**|Disables parameter sniffing unless `OPTION(RECOMPILE)`, `WITH RECOMPILE` or `OPTIMIZE FOR ` is used. For more information, see [KB980653](https://support.microsoft.com/kb/980653).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], to accomplish this at the database level, see the PARAMETER_SNIFFING option in [ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md).

To accomplish the same result at the query level, add the OPTIMIZE FOR UNKNOWN [query hint](../../t-sql/queries/hints-transact-sql-query.md). The OPTIMIZE FOR UNKNOWN hint doesn't disable the parameter sniffing mechanism, but effectively bypasses it to achieve the same intended result.
Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, a second option to accomplish this at the query level is to add the `USE HINT 'DISABLE_PARAMETER_SNIFFING'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

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

**Scope:** Global or session.| |**4137**|Causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to generate a plan using minimum selectivity when estimating AND predicates for filters to account for partial correlation instead of independence, under the query optimizer cardinality estimation (CE) model of [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] and earlier (70). For more information, see [KB2658214](https://support.microsoft.com/kb/2658214).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag when using the CE 70.

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

**Note:** This trace flag doesn't apply to CE version 120 or above. Use trace flag 9471 instead.

**Scope:** Global or session or query (QUERYTRACEON).| |**4138**|Causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to generate a plan that doesn't use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords. For more information, see [KB2667211](https://support.microsoft.com/kb/2667211).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'DISABLE_OPTIMIZER_ROWGOAL'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

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

**Scope:** Global or session or query (QUERYTRACEON).| |**4139**|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 [KB2952101](https://support.microsoft.com/kb/2952101).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

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

**Note:** This trace flag doesn't apply to CE version 70. Use trace flags 2389 and 2390 instead.

**Scope:** Global or session or query (QUERYTRACEON).| |**4199**|Enables Query Optimizer (QO) fixes released in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Cumulative Updates and Service Packs.

QO changes that are made to previous releases of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] are enabled by default under the latest database [compatibility level](../../t-sql/statements/alter-database-transact-sql-compatibility-level.md) in a given product release, without trace flag 4199 being enabled.

The following table summarizes the behavior when using specific database compatibility levels and trace flag 4199. For more information, see [KB974006](https://support.microsoft.com/kb/974006).

**Database Engine (DE) version****Database compatibility level****TF 4199****QO changes from all previous database compatibility levels****QO changes for DE version post-RTM**
[!INCLUDE[sssql16-md](../../includes/sssql16-md.md)]**100 to 120**OffDisabledDisabled
OnEnabledEnabled
**130** (Default)OffEnabledDisabled
OnEnabledEnabled
[!INCLUDE[ssSQL17](../../includes/sssql17-md.md)]**100 to 120**OffDisabledDisabled
OnEnabledEnabled
**130**OffEnabledDisabled
OnEnabledEnabled
**140** (Default)OffEnabledDisabled
OnEnabledEnabled
[!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] and [!INCLUDE[ssSDSfull](../../includes/sssdsfull-md.md)]**100 to 120**OffDisabledDisabled
OnEnabledEnabled
**130 to 140**OffEnabledDisabled
OnEnabledEnabled
**150** (Default)OffEnabledDisabled
OnEnabledEnabled


Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], to accomplish this at the database level, see the QUERY_OPTIMIZER_HOTFIXES option in [ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

**Important:** Query Optimizer fixes that address wrong results or access violation errors aren't enabled by trace flag 4199. Those fixes aren't considered optional and become enabled by default once the update package is installed.

**Scope:** Global or session or query (QUERYTRACEON).| |**4610**|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 on troubleshooting TokenAndPermUserStore cache size issues, see [Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005](https://support.microsoft.com/topic/queries-take-a-longer-time-to-finish-running-when-the-size-of-the-tokenandpermuserstore-cache-grows-in-sql-server-2005-ad1622e7-3bb5-7902-19a0-5d0e6271033d).

**Scope:** Global only.| |**4616**|Makes server-level metadata visible to application roles. In [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], an application role can't access metadata outside its own database because application roles aren't associated with a server-level principal. This is a change of behavior from earlier versions of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata.

**Scope:** Global only.| |**4618**|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 on troubleshooting TokenAndPermUserStore cache size issues, see [Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005](https://support.microsoft.com/topic/queries-take-a-longer-time-to-finish-running-when-the-size-of-the-tokenandpermuserstore-cache-grows-in-sql-server-2005-ad1622e7-3bb5-7902-19a0-5d0e6271033d).

**Scope:** Global only.| |**4621**|Limits the number of entries in the TokenAndPermUserStore cache store to the number specified by the user in a registry key. For more information, see [KB959823](https://support.microsoft.com/kb/959823).

**Scope:** Global only.| |**4631**|Disables SHA2_256/AES256 for hashing passwords that generate encryption keys. Starting in [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)], SHA2 is used instead of SHA1. This means extra steps might be necessary to have your [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] installation decrypt items that were encrypted by [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], as described in [Create identical symmetric keys on two servers](../../relational-databases/security/encryption/create-identical-symmetric-keys-on-two-servers.md#encryption-changes-in-sql-server-2017-cu2). For more information, see [KB4053407](https://support.microsoft.com/kb/4053407).

**Note:** This trace flag applies to [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] and later.

**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 resume re-encryption scan, disable trace flag 5004 and run ALTER DATABASE SET ENCRYPTION ON.

**Scope:** Global only.| |**6408**|Enables visibility of the estimated execution plan to see the remote query plan of PolyBase pushdown computation.

**Note:** This trace flag applies to [!INCLUDE[sssql19](../../includes/sssql19-md.md)] and later. For more information, see [How to tell if external pushdown occurred](../../relational-databases/polybase/polybase-how-to-tell-pushdown-computation.md).

**Scope:** Global or session or query (QUERYTRACEON).| |**6498**|Enables more than one large query compilation to gain access to the big gateway when there is sufficient memory available. This trace flag can be used to keep memory usage for the compilation of incoming queries under control, avoiding compilation waits for concurrent large queries. 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 [KB3024815](https://support.microsoft.com/kb/3024815).

**Note:** Starting with [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP2 and [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] this behavior is controlled by the engine and trace flag 6498 has no effect.

**Scope:** Global only.| |**6527**|Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration. By default, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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 won't 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.| |**6532**|Enables performance improvement of query operations with spatial data types in [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] and [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)]. The performance gain will vary, depending on the configuration, the types of queries, and the objects. For more information, see [KB3107399](https://support.microsoft.com/kb/3107399).

**Note:** Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] 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 [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] and [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)]. The performance gain will vary, depending on the configuration, the types of queries, and the objects. For more information, see [KB3107399](https://support.microsoft.com/kb/3107399).

**Note:** Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] 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 beginning with [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)]. The performance gain will vary, depending on the configuration, the types of queries, and the objects. For more information, see [KB3107399](https://support.microsoft.com/kb/3107399).

**Scope:** Global only.| |**6545**|Enables CLR strict security. For more information, see [KB4018930](https://support.microsoft.com/kb/4018930).

**Note:** This trace flag applies to [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] SP3 CU10, [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP2 CU6, [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] RTM CU7, [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1 CU4, and later. Starting with [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] this feature is enabled by default and trace flag 6545 has no effect.

**Scope:** Global only.| |**6559**|Enables fix that changes default CLR threading model logic. For more information, see [KB4517771](https://support.microsoft.com/kb/4517771).

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2 CU10, [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU18, [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] CU1, and later.

**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 [KB3051993](https://support.microsoft.com/kb/3051993).

**Scope:** Global and session.| |**7412**|Enables the lightweight query execution statistics profiling infrastructure. For more information, see [KB3170113](https://support.microsoft.com/kb/3170113).

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1 and later. Starting with [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] this trace flag has no effect because lightweight profiling is enabled by default.

**Scope:** Global only.| |**7470**|Enables additional computations for memory grants required for sort operations. For more information, see [KB3088480](https://support.microsoft.com/kb/3088480).

**Note:** This trace flag applies to [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] SP2 CU8, [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] RTM CU10, [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP1 CU3, and later.

**Warning:** Trace flag 7470 will increase memory requirements for queries using sort operators and may impact memory availability for other concurrent queries.

**Scope:** Global or session or query (QUERYTRACEON).| |**7471**|Enables running multiple [UPDATE STATISTICS](../../t-sql/statements/update-statistics-transact-sql.md) for different statistics on a single table concurrently. For more information, see [KB3156157](https://support.microsoft.com/kb/3156157).

**Note:** This trace flag applies to [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP1 and later.

**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 [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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 [!INCLUDE[ssNoVersion](../../includes/ssNoVersion-md.md)] is experiencing high number of QDS_LOADDB waits related to Query Store synchronous load (default behavior during database recovery).

**Note:** Starting with [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] this behavior is controlled by the engine and trace flag 7752 has no effect.

**Scope:** Global only.| |**7806**|Enables a dedicated administrator connection (DAC) on [!INCLUDE[ssExpress](../../includes/ssexpress-md.md)]. By default, no DAC resources are reserved on [!INCLUDE[ssExpress](../../includes/ssexpress-md.md)]. For more information, see [Diagnostic Connection for Database Administrators](../../database-engine/configure-windows/diagnostic-connection-for-database-administrators.md).

**Scope:** Global only.| |**8011**|Disable the ring buffer for Resource Monitor. You can use the diagnostic information in this ring buffer to diagnose out-of-memory conditions. Therefore, if you use this trace flag, the information that is available to diagnose performance and functional problems with [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] is greatly reduced. Trace flag 8011 always applies across the server and has global scope. You can turn on trace flag 8011 at startup or in a user session.

**Scope:** Global only.| |**8012**|Disable the ring buffer for schedulers. [!INCLUDE[ssNoVersion](../../includes/ssNoVersion-md.md)] records an event in the schedule ring buffer every time that one of the following events occurs:
- A scheduler switches context to another worker
- A worker is suspended
- A worker is resumed
- A worker enters the preemptive mode or the non-preemptive mode.

You can use the diagnostic information in this ring buffer to analyze scheduling problems. For example, you can use the information in this ring buffer to troubleshoot problems when [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] stops responding. Trace flag 8012 disables recording of events for schedulers. You can turn on trace flag 8012 only at startup.

**Warning:** When you use this trace flag, the information that is available for you to diagnose performance and functional problems with [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] is greatly reduced.

**Scope:** Global only.| |**8015**|Disable auto-detection and NUMA setup. For more information, see [KB2813214](https://support.microsoft.com/kb/2813214).

**Scope:** Global only.| |**8018**|Disable the exception ring buffer. The exception ring buffer records the last 256 exceptions that are raised on a node. Each record contains some information about the error and contains a stack trace. A record is added to the ring buffer when an exception is raised. Trace flag 8018 disables the creation of the ring buffer, and no exception information is recorded. Trace flag 8019 disables stack collection during the record creation.

**Warning:** When you use this trace flag, the information that is available for you to diagnose performance and functional problems with [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] is greatly reduced.

**Scope:** Global only.| |**8019**|Disable stack collection for the exception ring buffer. Trace flag 8019 has no effect if trace flag 8018 is turned on.

**Warning:** When you use this trace flag, the information that is available for you to diagnose performance and functional problems with [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] is greatly reduced.

**Scope:** Global only.| |**8020**|Disable working set monitoring. [!INCLUDE[ssNoVersion](../../includes/ssNoVersion-md.md)] uses the size of the working set when it receives global memory state signals from the operating system. Trace flag 8020 removes the size of the working set memory from consideration when [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] interprets the global memory state signals. If you use this trace flag incorrectly, heavy paging may occur, and the performance may be poor. Therefore, contact Microsoft Support before you turn on trace flag 8020. You can turn on trace flag 8020 only at startup.

**Scope:** Global only.| |**8032**|Reverts the cache limit parameters to the [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] setting, which in general allows caches to be larger. Use this setting when frequently reused cache entries don't fit into the cache and when the [optimize for ad hoc workloads Server Configuration Option](../../database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option.md) 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 [KB2809338](https://support.microsoft.com/kb/2809338).

**Note:** Starting with [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP2 and [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] this behavior is dynamic and controlled by the engine.

**Scope:** Global only.| |**8075**|Reduces [VAS](../../relational-databases/memory-management-architecture-guide.md#changes-to-memory-management-starting-2012-11x-gm) fragmentation when you receive memory page allocation errors on a 64-bit [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] or [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)]. For more information, see [KB3074434](https://support.microsoft.com/kb/3074434).

**Note:** This trace flag applies to [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)], [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] RTM CU10, and [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP1 CU3. Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] this behavior is controlled by the engine and trace flag 8075 has no effect.

**Scope:** Global only.| |**8079**|Allows [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] 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 [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP2. Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] this behavior is controlled by the engine and trace flag 8079 has no effect.

**Scope:** Global only.| |**8089**| In [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU16, you can enable the bitmap filtering for reducing the size of filtered memory dumps. [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] allocates a bitmap that keeps track of memory pages to be excluded from a filtered dump. Sqldumper.exe reads the bitmap and filters out pages without the need to read any other memory manager metadata.

**Note:** This trace flag applies to [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU16 through CU19 only. Starting with [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU20 the bitmap filtering is enabled by default. Trace flag 8089 will no longer apply and will be ignored if turned on. The bitmap filtering can be disabled via trace flag 8095. For more information, see [KB4488943](https://support.microsoft.com/kb/4488943).

**Scope:** Global only.| |**8095**| Disables the bitmap filtering for filtered memory dumps. [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] allocates a bitmap that keeps track of memory pages to be excluded from a filtered dump. Sqldumper.exe reads the bitmap and filters out pages without the need to read any other memory manager metadata.

**Note:** This trace flag applies to builds where bitmap filtering is enabled by default: [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] CU13 and later, [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU20 and later, and [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)].

**Scope:** Global only.| |**8099**|Enables a spinlock contention fix for high-end systems running [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] serving many concurrent users.

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] CU2 and CU3 only. Starting with [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] CU4, this behavior is enabled by default. For more information, see [KB4538688](https://support.microsoft.com/kb/4538688).

**Scope:** Global only.| |**8101**|Enables a fix to address high CPU usage on modern hardware, such as Intel Skylake processors, with a large number of CPUs and a high number of concurrent users. For more information, see [KB4538688](https://support.microsoft.com/kb/4538688).

**Scope:** Global only.| |**8121**|Fixes a system-wide low memory issue that occurs when SQL Server commits memory above the maximum server memory under the memory model with the Lock Pages In Memory. This trace flag affords the memory that the Resource monitor system thread needs in order to reduce SQL Server memory consumption. For fix information, see [KB5008996](https://support.microsoft.com/kb/5008996).

**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 an archived version of [KB302341](https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/302341).

**Scope:** Global only.| |**8558**|Enables a fix to ensure that you don't observe edge cases where when RCSI isolation level is enabled then a transaction sometimes can't see the latest data from the tables that were modified using DTC transactions even after xa_commit returned success for a short duration of time.

**Note:** Ensure that you thoroughly test this option, before rolling it into a production environment. This trace flag applies to [!INCLUDE [sssql22-md](../../includes/sssql22-md.md)] and later.

**Scope:** Global only.| |**8721**|Reports to the error log when auto-update statistics executes. For more information, see an archived version of [KB195565](https://mskb.pkisolutions.com/kb/195565).

**Note:** This trace flag requires trace flag [3605](#3605) to be enabled.

**Scope:** Global only.| |**8744**|Disable pre-fetching for the [Nested Loops](../../relational-databases/performance/joins.md#nested_loops) operator.

**Warning:** Incorrect use of this trace flag may cause additional physical reads when [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] executes plans that contain the Nested Loops operator.

**Scope:** Global and session.| |**8790**|Causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to use a wide query plan when executing an UPDATE statement to update indexes in a table. When you do an UPDATE against a clustered index column, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] updates not only the clustered index itself, but also all the non-clustered indexes because the non-clustered indexes contain the cluster index key. To optimize performance and reduce random I/O [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] may choose to sort all non-clustered index data in memory and then update all indexes by the order. This is called a wide plan(also called Per-Index Update) and can be forced using this trace flag

**Scope:** Global, session or query (QUERYTRACEON).| |**9024**|Converts a global log pool memory object into NUMA node partitioned memory object. For more information, see [KB2809338](https://support.microsoft.com/kb/2809338).

**Note:** Starting with [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] SP3 and [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] SP1 this behavior is controlled by the engine and trace flag 9024 has no effect.

**Scope:** Global only.| |**9109**| Disables start of Query Notification functionality. For more information, see [Restore or recovery may fail or take a long time if query notification is used in a database](/troubleshoot/sql/admin/restore-recovery-may-fail).

**Warning:** Use caution with this trace flag. This trace flag may be useful in a limited set of scenarios primarily for troubleshooting or isolating a problem.

**Scope:** Global and session.| |**9347**|Disables batch mode for sort operator. [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] introduced a new batch mode sort operator that boosts performance for many analytical queries. For more information, see [KB3172787](https://support.microsoft.com/kb/3172787).

**Scope:** Global or session or query (QUERYTRACEON).| |**9348**|Enables the use of Query Optimizer cardinality estimates to decide whether BULK INSERT for a clustered columnstore index should be initiated or not. If the estimated number of rows to insert is less than 102,400, the [!INCLUDE[ssde_md](../../includes/ssde_md.md)] does use BULK INSERT. If more than 102,400 rows are estimated, a BULK INSERT is initiated. For more information, see [KB2998301](https://support.microsoft.com/kb/2998301).

**Scope:** Global or session or query (QUERYTRACEON).| |**9349**|Disables batch mode for top N sort operator. [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] introduced a new batch mode top sort operator that boosts performance for many analytical queries.

**Scope:** Global or session or query (QUERYTRACEON).| |**9358**|Disables batch mode for sort operator. For more information, see [KB3171555](https://support.microsoft.com/kb/3171555).

**Note:** Starting with [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] this behavior is enabled by default and this trace flag has no effect.

**Scope:** Global or session or query (QUERYTRACEON).| |**9389**|Enables additional dynamic memory grant for batch mode operators. If a query doesn't 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](../../relational-databases/memory-management-architecture-guide.md#effects-of-min-memory-per-query).

**Scope:** Global or session.| |**9398**|Disables [Adaptive Join](../../relational-databases/performance/intelligent-query-processing-details.md#batch-mode-adaptive-joins) operator that enables the choice of a [Hash join or Nested Loops join](../../relational-databases/performance/joins.md) method to be deferred until after the first input has been scanned, as introduced in [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)]. For more information, see [KB4099126](https://support.microsoft.com/kb/4099126).

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

**Scope:** Global or session or query (QUERYTRACEON).| |**9410**|Enables a non-default fix for a query that uses a hash aggregate operator and spills. Enabling this trace flag increases the available memory for distinct hash operations. For more information, see [KB3167159](https://support.microsoft.com/kb/3167159).

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

**Scope:** Global or session or query (QUERYTRACEON).| |**9453**|Disables batch mode execution. For more information, see [KB4016902](https://support.microsoft.com/kb/4016902).

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

**Scope:** Global or session or query (QUERYTRACEON).| |**9471**|Causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to generate a plan using minimum selectivity for single-table filters, under the query optimizer cardinality estimation model of [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] and later.

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

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

**Note:** This trace flag doesn't apply to CE version 70. Use trace flag 4137 instead.

**Scope:** Global or session or query (QUERYTRACEON).| |**9476**|Causes [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to generate a plan using the Simple Containment assumption instead of the default Base Containment assumption, under the query optimizer cardinality estimation model of [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)] and later. For more information, see this [KB3189675](https://support.microsoft.com/kb/3189675).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

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

**Scope:** Global or session or query (QUERYTRACEON).| |**9481**|Enables you to set the Query Optimizer cardinality estimation model to [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] and earlier, irrespective of the compatibility level of the database. For more information, see [KB2801413](https://support.microsoft.com/kb/2801413).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], to accomplish this at the database level, see the LEGACY_CARDINALITY_ESTIMATION option in [ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md).

Starting with [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1, to accomplish this at the query level, add the `USE HINT 'FORCE_LEGACY_CARDINALITY_ESTIMATION'` [query hint](../../t-sql/queries/hints-transact-sql-query.md) instead of using this trace flag.

**Scope:** Global or session or query (QUERYTRACEON).| |**9485**|Disables SELECT permission for `DBCC SHOW_STATISTICS`. For more information, see [KB2683304](https://support.microsoft.com/kb/2683304).

**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 [!INCLUDE [sql2008r2-md](../../includes/sql2008r2-md.md)] and earlier), when using the Query Optimizer cardinality estimation model of [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] and later.

**Scope:** Global or session or query (QUERYTRACEON).| |**9495**|Disables parallelism during insertion for INSERT...SELECT operations and it applies to both user and temporary tables. For more information, see [KB3180087](https://support.microsoft.com/kb/3180087).

**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](../../database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group.md) and [Tune compression for availability group](../../database-engine/availability-groups/windows/tune-compression-for-availability-group.md).

**Scope:** Global or session.| |**9571**|Disables Availability Groups Auto seeding to the default database path. For more information, see [Disk Layout](../../database-engine/availability-groups/windows/automatic-seeding-secondary-replicas.md).

**Scope:** Global or session.| |**9576**|Disables the enhanced error collection for Availability Group failovers introduced in [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP1 CU10, [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2 CU2, and [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU9. For more information, see [SQL Server Availability Groups – Enhanced Database Level Failover](/archive/blogs/sql_server_team/sql-server-availability-groups-enhanced-database-level-failover).

**Scope:** Global only.| |**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 [!INCLUDE[ssSQL11](../../includes/sssql11-md.md)] and [!INCLUDE[ssSQL14](../../includes/sssql14-md.md)]. In [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], 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](../../database-engine/availability-groups/windows/tune-compression-for-availability-group.md).

**Scope:** Global or session.| |**9929**|Reduces the In-Memory checkpoint files to 1 MB each. For more information, see [KB3147012](https://support.microsoft.com/kb/3147012).

**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 aren't the target of the DML operation in [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)]. For more information, see [KB4013877](https://support.microsoft.com/kb/4013877).

**Note:** Trace flag 9939 isn't needed if trace flag 4199 is also explicitly enabled.

**Scope:** Global or session or query (QUERYTRACEON).| |**9944**|Enables a non-default fix for slow database recovery time when a database has a large number of memory optimized tables or memory optimized table types, and blocking with PREMPTIVE_OS_FINDFILE or PREEMPTIVE_OS_CREATEDIRECTORY wait types may be observed. For more information, see [KB4090789](https://support.microsoft.com/kb/4090789) and [KB4052338](https://support.microsoft.com/kb/4052338).

**Scope:** Global only.| |**10054**|Disables the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Query Optimizer rule that decorrelates subqueries in OR predicates into outer joins.

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] and later.

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

**Scope:** Global or session or query (QUERYTRACEON).| |**10204**|Disables merge/recompress during columnstore index reorganization. In [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)], when a columnstore index is reorganized, there is new functionality to automatically merge any small compressed rowgroups into larger compressed rowgroups, and recompressing any rowgroups that have a large number of deleted rows.

**Note:** Trace flag 10204 doesn't apply to columnstore indexes that are created on memory-optimized tables.

**Scope:** Global or session.| |**10207**|Allows clustered columnstore index (CCI) scans to skip corrupt segments or metadata, allowing data retrieval from a corrupt CCI. For more information, see [KB3067257](https://support.microsoft.com/kb/3067257).

**Scope:** Global or session.| |**10316**|Enables creation of additional indexes on [internal memory-optimized staging temporal table](../../relational-databases/tables/system-versioned-temporal-tables-with-memory-optimized-tables.md), beside the default one. If you have specific query pattern that includes columns that aren't covered by the default index, you may consider adding additional indexes.

**Note:** System-versioned temporal tables for Memory-Optimized Tables are designed to provide high transactional throughput. 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 isn't specified explicitly as part of the [UPDATE STATISTICS](../../t-sql/statements/update-statistics-transact-sql.md) statement. For more information, see [KB4039284](https://support.microsoft.com/kb/4039284).

**Scope:** Global only.| |**11024**|Enables triggering the auto update of statistics when the modification count of any partition exceeds the local [threshold](../../relational-databases/statistics/statistics.md#auto_update_statistics-option). For more information, see [KB4041811](https://support.microsoft.com/kb/4041811).

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2, [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU3, and later.

**Scope:** Global only.| |**11047**|Applies the default timeout set by `query wait (s)` or the Resource Governor `REQUEST_MEMORY_GRANT_TIMEOUT_SEC` configuration to columnstore index build operations. For more information, see [KB4480641](https://support.microsoft.com/kb/4480641).

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2 CU5, [!INCLUDE[ssSQL17](../../includes/sssql17-md.md)] CU14, and later.

**Scope:** Global only.| |**11064**|Improves the scalability of data loading operations into columnstore indexes, by optimizing memory distribution between the `SELECT` and `INSERT` statements. For more information on loading data into a columnstore index, see [Columnstore indexes - Data loading guidance](../../relational-databases/indexes/columnstore-indexes-data-loading-guidance.md).

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] and later.

**Scope:** Global only.| |**11068**|Uses the server, database, or resource pool configured max degree of parallelism (MAXDOP) value for columnstore index insert operations. For more information on overriding degrees of parallelism, see the [Query Processing Architecture Guide](../../relational-databases/query-processing-architecture-guide.md#overriding-degrees-of-parallelism).

**Important:** This trace flag is only effective if trace flag 11064 is also enabled.

**Important:** Use this trace flag when faster data loads are preferred over maintaining [columnstore segment](../../relational-databases/indexes/columnstore-indexes-overview.md#column-segment) quality. For example, using this trace flag when loading 1,048,577 rows into a columnstore may result in more than one compressed rowgroup, if the insert operation is executing in parallel mode. Without this trace flag, the insert operation would result in one compressed rowgroup.

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] and later.

**Scope:** Global only.| |**11631**| An `ALTER INDEX ... REORGANIZE` and the [background merge task](../../relational-databases/indexes/columnstore-indexes-overview.md#bckmergetsk) will clean up the deleted rows in a columnstore index rowgroup only when a certain threshold of rows has been deleted from that rowgroup. The default threshold is 10% of the maximum row limit (1 million), or of 100,000 rows.

This trace flag changes the threshold to 10% of the total current rows in a columnstore rowgroup. For example, if a rowgroup contains 20,000 rows, the threshold will be 2,000 deleted rows before this rowgroup is considered for cleanup. For more information, see [KB5000895](https://support.microsoft.com/kb/5000895).

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] CU9 and later.

**Scope:** Global only.| |**11634**| An `ALTER INDEX ... REORGANIZE` and the [background merge task](../../relational-databases/indexes/columnstore-indexes-overview.md#bckmergetsk) will clean up the deleted rows in a columnstore index rowgroup only when a certain threshold of rows has been deleted from that rowgroup. The default threshold is 10% of maximum row limit (1 million), or 100,000 rows.

This trace flag changes the threshold to 1% of the rows in a columnstore rowgroup. If enabled together with trace flag 11631 then it will be 1% of the current number of rows in a rowgroup, instead of 1% of 1 million rows. For more information, see [KB5000895](https://support.microsoft.com/kb/5000895).

**Note:** This trace flag applies to [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] CU9 and later.

**Scope:** Global only.| |**13116**|Disables the fix for bug [13685819](https://support.microsoft.com/en-us/topic/kb5000645-cumulative-update-16-for-sql-server-2016-sp2-a3997fa9-ec49-4df0-bcc3-12dd58b78265#bkmk_13685819). Use this trace flag if after you apply [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2 CU16, you encounter an issue in which DML (insert/update/delete) queries that use parallel plans can't complete any execution and encounter HP_SPOOL_BARRIER waits.

**Note:** This trace flag applies to [!INCLUDE[sssql16-md](../../includes/sssql16-md.md)] SP2 CU16.

**Scope:** Global only.| |**15097**|Enables AVX-512 support for [!INCLUDE [sssql22-md](../../includes/sssql22-md.md)] and later.

**Important:** We recommend enabling AVX-512 support for the following CPUs:

- Intel Ice Lake and later
- AMD EYPC Genoa and later

**Note:** This trace flag applies to [!INCLUDE[ssSQL22](../../includes/sssql22-md.md)] and later.

**Scope:** Global or session.| ## See also - [Data Types (Transact-SQL)](../../t-sql/data-types/data-types-transact-sql.md) - [DBCC INPUTBUFFER (Transact-SQL)](../../t-sql/database-console-commands/dbcc-inputbuffer-transact-sql.md) - [DBCC OUTPUTBUFFER (Transact-SQL)](../../t-sql/database-console-commands/dbcc-outputbuffer-transact-sql.md) - [EXECUTE (Transact-SQL)](../../t-sql/language-elements/execute-transact-sql.md) - [SELECT (Transact-SQL)](../../t-sql/queries/select-transact-sql.md) - [SET NOCOUNT (Transact-SQL)](../../t-sql/statements/set-nocount-transact-sql.md) - [Query Hints (Transact-SQL)](../../t-sql/queries/hints-transact-sql-query.md) ## Next steps - [ALTER DATABASE SET Options (Transact-SQL)](../../t-sql/statements/alter-database-transact-sql-set-options.md) - [ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md) - [DBCC TRACEOFF (Transact-SQL)](../../t-sql/database-console-commands/dbcc-traceoff-transact-sql.md) - [DBCC TRACEON (Transact-SQL)](../../t-sql/database-console-commands/dbcc-traceon-transact-sql.md) - [DBCC TRACESTATUS (Transact-SQL)](../../t-sql/database-console-commands/dbcc-tracestatus-transact-sql.md)