SQL Server Trace Flags - Complete list

Link: http://blogs.msdn.microsoft.com/sqlprogrammability/2007/01/13/6-0-best-programming-practices

Scope: global only

Trace Flag: 144

Function: Force server side bucketization. For legacy applications where change to client side code is not an option and when the application has queries that are improperly parameterized, this trace flag forces server side bucketization.

Trace Flag: 146

Undocumented trace flag

Function: Consider using when replaying against SQL 8.0, to avoid an attempt to set an encrypted connection.

Link: None

Trace Flag: 166

Undocumented trace flag

Function: Unclear. Observable effect was to change the identifier for act1008 to act1009 in a query plan.

Link: https://support.microsoft.com/help/3026083/fix-sos-cachestore-spinlock-contention-on-ad-hoc-sql-server-plan-cache

Scope: ?

Trace Flag: 168

Function: Bugfix in ORDER BY. This hotfix introduces trace flag 168. After you apply this hotfix, you must enable trace flag 168. Trace flag 168 must be set before the database is migrated to SQL Server 2005. If trace flag 168 is set after the database is migrated, the query result will remain unsorted.

Link: https://support.microsoft.com/help/926292

Trace Flag: 174

Function: Increases the SQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems.

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

Link: https://support.microsoft.com/help/3213683/fix-unable-to-rebuild-the-partition-online-for-a-table-that-contains-a

Scope: global only

Trace Flag: 176

Function: Enables a fix to address errors when rebuilding partitions online for tables that contain a computed partitioning column.

Link: https://support.microsoft.com/help/153096/fix-sql-server-6.5-service-pack-1-fixlist

Scope: global or session

Trace Flag: 204

Function: SQL 6.5 – Backward compatibility switch that enables non-ansi standard behavior. E.g. previously SQL server ignored trailing blanks in the like statement and allowed queries that contained aggregated functions to have items in the group by clause that were not in the select list.

Trace Flag: 205

Function: Reports to the error log when a statistics-dependent stored procedure is being recompiled as a result of auto-update statistics.

Link: https://support.microsoft.com/help/195565

Scope: global only

Trace Flag: 206

Undocumented trace flag

Function: SQL 6.5 – Provides backward compatibility for the set user statement. KB 160732

Link: None

Trace Flag: 208

Undocumented trace flag

Function: SET QUOTED IDENTIFIER ON

Link: None

Function: Replace error message 8152 with 2628 (String or binary data would be truncated. The statement has been terminated.). Description for 2628 mesage has useful information - which column had the truncation and which row.

Link: https://www.procuresql.com/blog/2018/09/26/string-or-binary-data-get-truncated/

Link: https://feedback.azure.com/forums/908035-sql-server/suggestions/32908417-binary-or-string-data-would-be-truncated-error

Link: https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/

Link: https://support.microsoft.com/help/4468101

Scope: global or session

SQL Server Version: 2019, >= 2017 CU12

Demo: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Trace_Flag/Trace_Flag_460.sql

Trace Flag: 506

Undocumented trace flag

Function: Enforces SQL-92 standards regarding null values for comparisons between variables and parameters. Any comparison of variables and parameters that contain a NULL always results in a NULL.

Link: None

Trace Flag: 610

Function: Controls minimally logged inserts into indexed tables.

Link: http://msdn.microsoft.com/en-us/library/dd425070%28v=SQL.100%29.aspx

Link: https://www.pythian.com/blog/minimally-logged-operations-data-loads/

Link: https://msdn.microsoft.com/library/dd425070.aspx

Link: https://orderbyselectnull.com/2017/07/10/trace-flag-610-and-sql-server-2016/

Scope: global or session

Trace Flag: 611

Undocumented trace flag

Function: SQL 9 – When turned on, each lock escalation is recorded in the error log along with the SQL Server handle number. Aaron confirmed this still works in SQL 2014. Outputs info of the form: "Escalated locks - Reason: LOCK_THRESHOLD, Mode: S, Granularity: TABLE, Table: 222623836, HoBt: 150:256, HoBt Lock Count: 6248, Escalated Lock Count: 6249, Line Number: 1, Start Offset: 0, SQL Statement: select count(*) from dbo.BigTable"

Link: None

Trace Flag: 617

Function: SQL 9 – When turned on, each lock escalation is recorded in the error log along with the SQL Server handle number. As long as there are no SCH_M lock requests waiting in the ‘lock wait list’, the ‘lock wait list’ will be bypassed by statements issued in uncommitted read transaction isolation level. If there is a SCH_M lock request in the ‘lock wait list’, a query in uncommitted read transaction isolation level will not bypass the ‘lock wait list’, but the SCH_S lock request will go into the ‘lock wait list’. In order behind the SCH_M lock waiting in the same list. As a result the grant of the SCH_S request for such a query is dependent on the grant and release of the SCH_M lock request entering the ‘lock wait list’ earlier.

Link: https://blogs.msdn.microsoft.com/saponsqlserver/2014/01/17/new-functionality-in-sql-server-2014-part-3-low-priority-wait/

Trace Flag: 634

Function: Disables the background columnstore compression task. SQL Server periodically runs the Tuple Mover background task that compresses columnstore index rowgroups with uncompressed data, one such rowgroup at a time. Columnstore compression improves query performance but also consumes system 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.

Link: Niko Neugebauer Columnstore Indexes – part 35

Link: http://www.sqlservergeeks.com/trace-flag-634-disable-background-columnstore-compression/

Scope: global only

Trace Flag: 646

Function: Serves for getting detailed information on which Columnstore were eliminated by the Query Optimiser right into the error log.

Link: Niko Neugebauer Columnstore Indexes – part 35

Link: http://www.sqlskills.com/blogs/joe/exploring-columnstore-index-metadata-segment-distribution-and-elimination-behaviors

Trace Flag: 647

Function: Avoids a new-in-SQL 2012 data check (done when adding a column to a table) that can cause ALTER TABLE... ADD operations to take a very long time. The KB has a useful query for determining the row size for a table.

Link: https://support.microsoft.com/help/2986423/fix-it-takes-a-long-time-to-add-new-columns-to-a-table-when-the-row-size-exceeds-the-maximum-allowed-size

Trace Flag: 652

Function: Disable page pre-fetching scans. If you turn on trace flag 652, SQL Server no longer brings database pages into the buffer pool before these database pages are consumed by the scans. If you turn on trace flag 652, queries that benefit from the page pre-fetching feature exhibit low performance.

Link: KB920093

Scope: global or session

Trace Flag: 653

Undocumented trace flag

Function: Disables read ahead for the current connection

Link: None

Trace Flag: 661

Function: 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 is not purged. Therefore, the space that the deleted record consumes is not freed. This behavior affects space consumption and the performance of scan operations.

Link: KB920093

Link: http://blogs.msdn.com/b/sqljourney/archive/2012/07/28/an-in-depth-look-at-ghost-records-in-sql-server.aspx

Scope: global or session

Trace Flag: 662

Undocumented trace flag

Function: Prints detailed information about the work done by the ghost cleanup task when it runs next. Use TF 3605 to see the output in the errorlog

Function: Enables table lock for bulk load operations into a heap with no non-clustered indexes. When this trace flag is enabled, bulk load operations acquire bulk update (BU) locks when bulk copying data into a table. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table, while preventing other processes that are not bulk loading data from accessing the table. The behavior is similar to when the user explicitly specifies TABLOCK hint while performing bulk load, or when the sp_tableoption table lock on bulk load is enabled for a given table. However, when this trace flag is enabled, this behavior becomes default without any query or database changes.

Link: http://technet.microsoft.com/en-au/library/cc917726.aspx

Scope: global or session

Trace Flag: 806

Function: enables DBCC audit checks to be performed on pages to test for logical consistency problems. These checks try to detect when a read operation from a disk does not experience any errors but the read operation returns data that is not valid. Pages will be audited every time that they are read from disk. Page auditing can affect performance and should only be used in systems where data stability is in question.

Link: http://www.sqlskills.com/blogs/paul/how-to-tell-if-the-io-subsystem-is-causing-corruptions

Link: Important Trace Flags That Every DBA Should Know

Link: https://technet.microsoft.com/en-au/library/cc917726.aspx

Scope: ?

Trace Flag: 809

Trace Flag: 834

Function: Uses Microsoft Windows large-page allocations for the buffer pool. Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. The 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.

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

Link: KB920093

Link: https://support.microsoft.com/help/3210239

Link: https://blogs.msdn.microsoft.com/psssql/2012/12/11/how-it-works-sql-server-32-bit-paeawe-on-sql-2005-2008-and-2008-r2-not-using-as-much-ram-as-expected/

Scope: global only

Trace Flag: 835

Undocumented trace flag

Function: SQL 9 / 10 – On 64 bit SQL Server it turns off Lock pages in memory

Link: None

Scope: ?

Trace Flag: 836

Function: 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. 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.

Link: KB920093

Scope: global only

Trace Flag: 839

Function: (Apparently) forces SQL Server to treate all NUMA memory as “flat”, as if it was SMP.

Link: https://blogs.msdn.microsoft.com/psssql/2010/04/02/how-it-works-soft-numa-io-completion-thread-lazy-writer-workers-and-memory-nodes

Trace Flag: 840

Function: SQL 9 – When trace turned on, SQL Server can perform larger I/O extent reads to populate the buffer pool when SQL Server starts this populates the buffer pool faster. Additionally, the larger I/O extent reads improve the initial query compilation and the response time when SQL Server starts.

Link: https://blogs.msdn.microsoft.com/ialonso/2011/12/09/the-read-ahead-that-doesnt-count-as-read-ahead

Trace Flag: 842

Undocumented trace flag

Function: Use sys.dm_os_memory_node_access_stats to verify local vs. foreign memory under NUMA configurations after turning on this flag

Link: None

Trace Flag: 845

Function: Enable Lock pages in Memory on Standard Edition

Link: https://support.microsoft.com/help/970070

Link: https://support.microsoft.com/help/2708594/fix-locked-page-allocations-are-enabled-without-any-warning-after-you-upgrade-to-sql-server-2012

Undocumented trace flag

Function: Turns 8k page allocations for Column Store segments into 2MB instead.

Link: https://twitter.com/slava_oks/status/1044257034361757696

Link: https://github.com/ktaranov/sqlserver-kit/issues/151

Scope: ?

Undocumented trace flag

Trace Flag: 888

Function: Enables support for locked pages for SQL 2000

Link: None

Trace Flag: 902

Function: 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.

Warning: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your SQL Server instance.

Link: https://support.microsoft.com/help/2163980

Link: https://blogs.msdn.microsoft.com/luti/2017/05/17/sql-server-offline-after-applying-service-pack/

Scope: global only

Trace Flag: 916

Undocumented trace flag

Function: The KB article references the flag in the context of seeing a Profiler dump

Link: None

Trace Flag: 1106

Function: SQL 9 - Used space in tempdb increases continuously when you run a query that creates internal objects in tempdb

Link: https://support.microsoft.com/help/947204

Link: https://blogs.msdn.microsoft.com/arvindsh/2014/02/24/tracking-tempdb-internal-object-space-usage-in-sql-2012

Trace Flag: 1117

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

Note: Beginning with SQL Server 2016 this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no affect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

Link: https://www.littlekendra.com/2017/01/03/parallelism-and-tempdb-data-file-usage-in-sql-server/

Link: SQL Server 2016 : Getting tempdb a little more right

Link: http://www.sqlskills.com/blogs/paul/tempdb-configuration-survey-results-and-advice

Link: https://blogs.msdn.microsoft.com/ialonso/2011/12/01/attempt-to-grow-all-files-in-one-filegroup-and-not-just-the-one-next-in-the-autogrowth-chain-using-trace-flag-1117

Link: http://sql-articles.com/articles/general/day-6trace-flag-1117-auto-grow-equally-in-all-data-file

Link: http://www.ryanjadams.com/2017/05/trace-flag-1117-growth-contention/

Link: https://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

Scope: global only

Trace Flag: 1118

Function: Removes most single page allocations on the server, reducing contention on the SGAM page. When a new object is created, by default, the first eight pages are allocated from different extents (mixed extents). Afterwards, when more pages are needed, those are allocated from that same extent (uniform extent). The SGAM page is used to track these mixed extents, so can quickly become a bottleneck when numerous mixed page allocations are occurring. This trace flag allocates all eight pages from the same extent when creating new objects, minimizing the need to scan the SGAM page.

Note: Beginning with SQL Server 2016 this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no affect. For more information, see ALTER DATABASE SET Options (Transact-SQL).

Link: http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

Link: http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

Link: https://support.microsoft.com/help/328551

Link: SQL Server 2016 : Getting tempdb a little more right

Link: https://chrisadkin.org/2015/04/14/well-known-and-not-so-well-known-sql-server-tuning-knobs-and-switches

Scope: global only

Function: Prints detailed lock information as every request for a lock is made (the process ID and type of lock requested)

Link: TECHNET List Of SQL Server Trace Flags

Link: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2008/03/30/tempdb-table-variable-vs-local-temporary-table

Link: KB169960

Link: Important Trace Flags That Every DBA Should Know

Scope: ?

Trace Flag: 1202

Undocumented trace flag

Function: Insert blocked lock requests into syslocks

Link: None

Trace Flag: 1204

Function: Returns the resources and types of locks participating in a deadlock and also the current command affected. Writes information about deadlocks to the ERRORLOG in a "text format"

Link: https://support.microsoft.com/help/832524

Link: https://support.microsoft.com/help/832524/sql-server-technical-bulletin---how-to-resolve-a-deadlock

Link: Important Trace Flags That Every DBA Should Know

Scope: global only

Trace Flag: 1205

Function: More detailed information about the command being executed at the time of a deadlock. Documented in SQL 7 BOL.

Trace Flag: 1206

Function: Used to complement flag 1204 by displaying other locks held by deadlock parties

Link: KB169960

Trace Flag: 1208

Function: KB: “Prints the host name and program name supplied by the client. This can help identify a client involved in a deadlock, assuming the client specifies a unique value for each connection.”

Link: KB169960

Trace Flag: 1211

Function: Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks. Using this trace flag can generate excessive numbers of locks. This can slow the performance of the Database Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memory. If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid "out-of-locks" errors when many locks are being used.

Link: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2330-lock-escalation

Link: Important Trace Flags That Every DBA Should Know

Scope: global or session

Trace Flag: 1216

Undocumented trace flag

Function: SQL 7 - Disables Health reporting. Lock monitor when detects a (worker thread) resource level blocking scenario. If a SPID that owns a lock is currently queued to the scheduler, because all the assigned worker threads have been created and all the assigned worker threads are in an un-resolvable wait state, the following error message is written to the SQL Server error log: Error 1223: Process ID %d:%d cannot acquire lock "%s" on resource %s because a potential deadlock exists on Scheduler %d for the resource. Process ID %d:% d holds a lock "%h" on this resource.

Link: None

Trace Flag: 1217

Undocumented trace flag

Function: Disables (for 7.0) the “UMS Health” reporting messages described in the KB article.

Link: None

Trace Flag: 1222

Function: Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

Link: https://blog.sqlauthority.com/2017/01/09/sql-server-get-historical-deadlock-information-system-health-extended-events

Link: Important Trace Flags That Every DBA Should Know

Scope: global only

Trace Flag: 1224

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

Forty percent of the memory that is used by Database Engine. This is applicable only when the locks parameter of sp_configure is set to 0.
Forty percent of the lock memory that is configured by using the locks parameter of sp_configure. For more information, see Server Configuration Options (SQL Server). If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid "out-of-locks" errors when many locks are being used.
Note: Lock escalation to the table- or HoBT-level granularity can also be controlled by using the LOCK_ESCALATION option of the ALTER TABLE statement.
Link: Docs Trace Flags
Link: Important Trace Flags That Every DBA Should Know
Scope: global or session

Trace Flag: 1228

Function: Enable lock partitioning. By default, lock partitioning is enabled when a server has 16 or more CPUs. Otherwise, lock partitioning is disabled. Trace flag 1228 enables lock partitioning for 2 or more CPUs. Trace flag 1229 disables lock partitioning. Trace flag 1229 overrides trace flag 1228 if trace flag 1228 is also set. Lock partitioning is useful on multiple-CPU servers where some tables have very high lock rates. You can turn on trace flag 1228 and trace flag 1229 only at startup.

Link: Trace Flag 1228 and 1229

Link: Microsoft SQL Server 2005 TPC-C Trace Flags

Trace Flag: 1229

Link: Trace Flag 1228 and 1229

Link: Microsoft SQL Server 2005 TPC-C Trace Flags

Trace Flag: 1236

Function: Enables database lock partitioning. Fixes performance problem in scenarios with high lock activity in SQL 2012 and SQL 2014.

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

Link: https://support.microsoft.com/help/2926217

Scope: global only

Trace Flag: 1237

Function: Allows the ALTER PARTITION FUNCTION statement to honor the current user-defined session deadlock priority instead of being the likely deadlock victim by default.

Note: Starting with SQL Server 2017 and database compatibility level 140 this is the default behavior and trace flag 1237 has no effect.

Link: https://support.microsoft.com/help/4025261

Link: How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888

Scope: global or session or query

Trace Flag: 1260

Function: Disabled mini-dump for non-yield condition. Disables mini-dump generation for "any of the 17883, 17884, 17887, or 17888 errors. The trace flag can be used in conjunction with trace flag –T1262. For example, you could enable –T1262 to get 10- and a 60-second interval reporting and also enable – T1260 to avoid getting mini-dumps."

Link: A Topical Collection of SQL Server Flags v6

Link: How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888

Scope: global only

Trace Flag: 1261

Undocumented trace flag

Function: SQL 8 - Disables Health reporting. Lock monitor when detects a (worker thread) resource level blocking scenario. If a SPID that owns a lock is currently queued to the scheduler, because all the assigned worker threads have been created and all the assigned worker threads are in an un-resolvable wait state, the following error message is written to the SQL Server error log: Error 1229: Process ID %d:%d owns resources that are blocking processes on scheduler %d.

Link: None

Trace Flag: 1262

Function: The default behavior (for 1788* errors) is for SQL to generate a mini-dump on the first occurrence, but never after. 1262 changes the behavior: “When –T1262 is enabled, a mini-dump is generated when the non-yielding condition is declared (15 seconds) and at subsequent 60-second intervals for the same non-yield occurrence. A new nonDiagCorrect17883etc; yielding occurrence causes dump captures to occur again.” In SQL 2000 this was a startup-only flag; in 2005+ it can be enabled via TRACEON. Note that the flag is also covered in Khen2005, p400, but with no new information.

Link: A Topical Collection of SQL Server Flags v6

Trace Flag: 1264

Function: Collect process names in non-yielding scenario memory dumps

Link: A Topical Collection of SQL Server Flags v6

Link: https://support.microsoft.com/help/2630458/

Trace Flag: 1400

Function: SQL 9 RTM – Enables creation of database mirroring endpoint, which is required for setting up and using database mirroring

Link: None

Trace Flag: 1439

Function: Trace database restart and failover messages to SQL Errorlog for mirrored databases

Link: Trace flags in sql server from trace flag 902 to trace flag 1462

Trace Flag: 1448

Function: Enables the replication log reader to move forward even if the async secondaries have not acknowledged the reception of a change. Even with this trace flag enabled the log reader always waits for the sync secondaries. The log reader will not go beyond the min ack of the sync secondaries. This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of time or when an async secondary fails.

Scope: global only

Trace Flag: 1449

Function: When you use SNAC to connect to an instance of a principal server in a database mirroring session: "The connection attempted to fail over to a server that does not have a failover partner".

Link: https://support.microsoft.com/help/936179

Trace Flag: 1462

Function: Disables log stream compression for asynchronous availability groups. This feature is enabled by default on asynchronous availability groups in order to optimize network bandwidth.

Link: Tune compression for availability group

Link: http://www.sqlskills.com/blogs/paul/sql-server-2008-performance-boost-for-database-mirroring

Function: Enables SQL Server optimization when disks of different sector sizes are used for primary and secondary replica log files, in SQL Server AG and Log Shipping environments.

Link: https://support.microsoft.com/help/3009974

Link: http://technet.microsoft.com/en-au/library/cc917726.aspx

Scope: global only

Trace Flag: 1802

Function: SQL 9 - After detaching a database that resides on network-attached storage, you cannot reattach the SQL Server database

Link: https://support.microsoft.com/help/922804

Trace Flag: 1806

Function: Disable Instant File Initialization. Used to guarantee the physical data file space acquisition during data file creation or expansion, on a thin provisioned subsystem

Link: https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization

Link: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-330-instant-file-initialization-can-be-controlled-from-within-sql-server

Link: https://support.microsoft.com/help/2574695/file-initialization-takes-a-long-time-for-sql-server-database-related-operations

Link: Important Trace Flags That Every DBA Should Know

Scope: ?

Trace Flag: 1807

Function: Enable option to have database files on SMB share for SQL Server 2008 and 2008R2

Link: http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx

Link: https://support.microsoft.com/help/304261/description-of-support-for-network-database-files-in-sql-server

Trace Flag: 1808

Function: Directs SQL Server to ignore auto-closing databases even if the Auto-close property is set to ON. Must be set globally. Present in Yukon forward

Link: https://blogs.msdn.microsoft.com/ialonso/2012/04/11/want-your-sql-server-to-simply-ignore-the-auto_close-setting-for-all-open-databases-for-which-it-has-been-enabled

Trace Flag: 1810

Function: Prints the file create/open/close timings

Link: None

Trace Flag: 1816

Function: Bob Ward briefly references this flag in his PASS 2014 SQL Server IO talk, saying that it “could provide more details around errors” that occur with IO done to SQL data files in Azure Storage.

Link: None

Trace Flag: 1851

Function: Anecdotally, from a JustDave’s notes on an Amanda Ford talk at SQL Relay Reading 2014: “...disables the automerge functionality for in-memory oltp”

Link: https://justdaveinfo.wordpress.com/2014/10/16/october-13-microsoft-sql-relay-reading

Trace Flag: 1903

Function: SQL 8 - When you capture a SQL Profiler trace in a file and then you try to import the trace files into tables by using the fn_trace_gettable function no rows may be returned

Link: Note

Trace Flag: 1905

Function: Unknown

Link: Upgrading an expired SQL Server 2016 Evaluation Edition

Trace Flag: 2301

Function: Trace flag 2301 enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of large data sets.

Link: KB920093

Link: http://www.queryprocessor.com/ce_join_base_containment_assumption

Link: https://connect.microsoft.com/SQLServer/feedback/details/772232/make-optimizer-estimations-more-accurate-by-using-metadata

Scope: global or session or query

Trace Flag: 2309

Function: In SQL 2014, enables output from a 3rd parameter for DBCC SHOW_STATISTICS such that the partial statistics histogram (for just one partition) is shown.

Link: https://sqlperformance.com/2015/05/sql-statistics/incremental-statistics-are-not-used-by-the-query-optimizer

Link: http://blog.dbi-services.com/sql-server-2014-new-incremental-statistics

Trace Flag: 2312

Function: Enables you to set the query optimizer cardinality estimation model to the SQL Server 2014 through SQL Server 2016 versions, dependent of the compatibility level of the database.

Link: KB2801413

Link: New Features in SQL Server 2016 Service Pack 1

Link: http://www.sqlservergeeks.com/sql-server-2014-trace-flags-2312/

Scope: global or session or query

Trace Flag: 2315

Function: Aaron: I stumbled onto this one. Seems to output memory allocations taken during the compilation process (and maybe the plan as well? “PROCHDR”), as well as memory broker states & values at the beginning and end of compilation.

Link: None

Trace Flag: 2318

Function: Aaron: stumbled onto this one as well. I’ve only seen one type of output so far: “Optimization Stage: HEURISTICJOINREORDER”. Maybe useful in combo with other compilation trace flags to see the timing of join reordering?

Link: None

Trace Flag: 2324

Function: Disables Implied Predicates

Link: https://answers.sqlperformance.com/questions/2299/why-not-seek-predicate.html?utm_content=buffer9bed5&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer

Trace Flag: 2328

Function: SQL 9+ - Makes cardinality estimates upon resulting selectivity. The reasoning for this is that one or more of the constants may be statement parameters, which would change from one execution of the statement to the next.

Link: https://blogs.msdn.microsoft.com/ianjo/2006/03/28/disabling-constant-constant-comparison-estimation

Link: http://www.queryprocessor.ru/isnumeric_ce_bug_eng

Trace Flag: 2329

Function: Disables “Few Outer Rows” optimization

Link: http://www.queryprocessor.com/few-outer-rows-optimization

Trace Flag: 2330

Function: Query performance decreases when sys.dm_db_index_usage_stats has large number of rows

Link: http://www.brentozar.com/archive/2015/11/trace-flag-2330-who-needs-missing-index-requests/

Link: https://chrisadkin.org/2015/04/14/well-known-and-not-so-well-known-sql-server-tuning-knobs-and-switches/

Trace Flag: 2332

Function: PWhite: “Force DML Request Sort (CUpdUtil::FDemandRowsSortedForPerformance)”

Link: http://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx

Trace Flag: 2335

Function: Causes SQL Server to assume a fixed amount of memory is available during query optimization. It does not limit the memory SQL Server grants to execute the query. The memory configured for SQL Server will still be used by data cache, query execution and other consumers.

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

Link: https://support.microsoft.com/help/2413549

Link: http://dba.stackexchange.com/questions/53726/difference-in-execution-plans-on-uat-and-prod-server

Scope: global or session or query

Trace Flag: 2336

Function: Aaron: Another one that I stumbled onto. Appears to tie memory info and cached page likelihoods with costing

Link: None

Trace Flag: 2340

Function: Causes SQL Server not to use a sort operation (batch sort) for optimized nested loop joins when generating a plan. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT query hint instead of using this trace flag.

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

Link: New Features in SQL Server 2016 Service Pack 1

Link: https://blogs.msdn.microsoft.com/psssql/2010/01/11/high-cpu-after-upgrading-to-sql-server-2005-from-2000-due-to-batch-sort

Link: http://www.queryprocessor.com/batch-sort-and-nested-loops

Scope: global or session or query

Trace Flag: 2341

Function: Enables the use of a hash join for joins to column store indexes even when the join clause would normally be removed “during query normalization”.

Link: https://support.microsoft.com/help/3146123/query-plan-generation-improvement-for-some-columnstore-queries-in-sql-server-2014-or-2016

Trace Flag: 2363

Function: TF Selectivity

Link: Cardinality Estimation Framework 2014 First Look

Link: http://www.queryprocessor.com/ce-process

Link: https://sqlperformance.com/2014/01/sql-plan/cardinality-estimation-for-multiple-predicates

Trace Flag: 2368

Undocumented trace flag

Function: For one query, this resulted in a parallel plan significantly more expensive than the naturally occurring serial plan. Could be related to trace flag 3651.

Link: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

Scope: ?

Trace Flag: 2371

Function: Changes the fixed auto update statistics threshold to dynamic auto update statistics threshold.

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

Link: https://support.microsoft.com/help/2754171

Link: https://blogs.msdn.microsoft.com/axinthefield/sql-server-trace-flag-2371-for-dynamics-ax/

Scope: global only

Trace Flag: 2372

Function: Displays memory utilization during the optimization process. Memory for Phases Memory before and after deriving properties and rules (verbose)

Link: More Undocumented Query Optimizer Trace Flags

Link: Cardinality Estimation Framework 2014 First Look

Trace Flag: 2373

Function: Displays memory utilization during the optimization process. Memory for Deriving Properties.

Link: More Undocumented Query Optimizer Trace Flags

Link: Cardinality Estimation Framework 2014 First Look

Trace Flag: 2374

Undocumented trace flag

Function: Removes QueryHash and QueryPlanHash information from estimated query plans.

Scope: ?

Trace Flag: 2382

Function: SSC: “SQL 8 -Statistics collected for system tables.”

Link: None

Trace Flag: 2387

Undocumented trace flag

Function: There was a small change in CPU and IO costs for some operators. Full effect unknown.

Link: SQL Server - estimates outside of the histogram - half-baked draft

Scope: ?

Trace Flag: 2388

Function: Changes the output of DBCC SHOW_STATISTICS. Instead of the normal Header/Vector/Histogram output, instead we get a single row that gives information related to whether the lead column of the stat object is considered to be ascending or not. This TF is primarily helpful in watching the state of a stat object change from “Unknown”, to “Ascending” (and potentially to “Stationary”). Also In SQL Server, if you want to see the information of last four statistics update on a statistics object then you can use trace flag 2388. In simple words, we can say that this trace flag provide us the historical information about statistics update.

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-2388/

Scope: session only

Trace Flag: 2389

Function: 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.

Link: KB2801413

Link: http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582227.aspx

Link: http://www.sqlmag.com/article/tsql3/making-the-most-of-automatic-statistics-updating--96767

Link: http://sqlperformance.com/2016/07/sql-statistics/trace-flag-2389-new-cardinality-estimator

Link: https://www.sswug.org/sswugresearch/community/trace-flag-2389-and-the-new-cardinality-estimator/

Link: New Features in SQL Server 2016 Service Pack 1

Link: SQL Server - estimates outside of the histogram - half-baked draft

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-2389/

Scope: global or session or query

Trace Flag: 2390

Function: 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

Link: http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582227.aspx

Link: KB2801413

Link: http://www.sqlmag.com/article/tsql3/making-the-most-of-automatic-statistics-updating--96767

Link: https://blogs.msdn.microsoft.com/ianjo/2006/04/24/ascending-keys-and-auto-quick-corrected-statistics

Link: SQL Server - estimates outside of the histogram - half-baked draft

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-2390/ Scope: global or session or query

Trace Flag: 2392

Undocumented trace flag

Function: Trace Flag 2392 can be used to turn the missing index feature off completely (as a workaround to the issue that is corrected by the hotfix). This trace flag has been in the product since SQL Server 2005. The problem is, it will not disable/enable missing index stats collection unless it is enabled at startup. If you set it as a startup TF and restart SQL Server, then no missing index stats are collected. If you then subsequently disable TF 2392 while SQL Server is running, it still won’t collect any missing index stats (despite what you may expect).

Link: https://www.sqlskills.com/blogs/glenn/sql-server-missing-indexes-feature-and-trace-flag-2392/

Link: https://support.microsoft.com/help/4042232/fix-access-violation-when-you-cancel-a-pending-query-if-the-missing-in

Scope: global only

Trace Flag: 2398

Function: Another one I stumbled upon myself...outputs info about “Smart Seek costing”: e.g.: “Smart seek costing (75.2) :: 1.34078e+154 , 1”

Link: None

Trace Flag: 2399

Undocumented trace flag

Function: Small changes in operator costs were observed for some queries. These were typically less than 0.01 units.

Scope: ?

Trace Flag: 2418

Undocumented trace flag

Function: Disables serial Batch mode processing.

Link: https://support.microsoft.com/help/2746341/fix-high-cpu-usage-when-you-execute-an-update-statement-that-includes-a-where-current-of-cursor-clause-in-sql-server-2008

Scope: ?

Trace Flag: 2422

Function: Enables the SQL Server Database Engine to abort a request when the maximum time set by Resource Governor REQUEST_MAX_CPU_TIME_SEC configuration is exceeded.

Note: This trace flag applies to SQL Server 2017 CU3 and higher builds.

Link: https://support.microsoft.com/help/4038419

Scope: global only

Trace Flag: 2430

Function: Fixes performance problem when using large numbers of locks

Link: https://support.microsoft.com/help/2754301

Trace Flag: 2440

Function: SQL 10 - Parallel query execution strategy on partitioned tables. SQL 9 used single thread per partition parallel query execution strategy. In SQL 10, multiple threads can be allocated to a single partition by turning on this flag.

Link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc010af9-afa0-4c87-937c-4343b4e1119a/trace-flag-2440

Trace Flag: 2453

Function: Allow a table variable to trigger recompile when enough number of rows are changed with may allow the query optimizer to choose a more efficient plan.

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

Link: http://sqlperformance.com/2014/06/t-sql-queries/table-variable-perf-fix

Link: https://support.microsoft.com/help/2952444

Link: https://www.brentozar.com/archive/2017/02/using-trace-flag-2453-improve-table-variable-performance

Link: https://www.brentozar.com/archive/2018/03/table-valued-parameters-unexpected-parameter-sniffing

Link: TEMPDB – Files and Trace Flags and Updates

Link: http://www.sqlservergeeks.com/the-correct-cardinality-estimation-for-table-variable-using-trace-flag-2543/

Scope: global or session or query

Trace Flag: 2456

Function: Relieves RESOURCE_SEMAPHORE_MUTEX contention, which may be primarily due to a bug in SQL 2005.

Link: None

Trace Flag: 2466

Function: When SQL Server is determining the runtime DOP for a parallel plan, this flag directs it to use logic found in “older versions” (the post doesn’t say which versions) to determine which NUMA node to place the parallel plan on. This older logic relies on a polling mechanism (roughly every 1 second), and can result in race conditions where 2 parallel plans end up on the same node. The newer logic “significantly reduces” the likelihood of this happening.

Link: https://blogs.msdn.microsoft.com/psssql/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop

Trace Flag: 2467

Function: “If target MAXDOP target is less than a single node can provide and if trace flag 2467 is enabled attempt to locate least loaded node”

Link: https://blogs.msdn.microsoft.com/psssql/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop

Link: SQL Server Parallel Query Placement Decision Logic

Trace Flag: 2468

Undocumented trace flag

Function: “Find the next node that can service the DOP request. Unlike full mode, the global, resource manager keeps track of the last node used. Starting from the last position, and moving to the next node, SQL Server checks for query placement opportunities. If a node can’t support the request SQL Server continues advancing nodes and searching.”

Link: SQL Server Parallel Query Placement Decision Logic

Function: SQL 7 - Prevents DBCC TRACEON 208, SPID 10 errors from appearing in the error log (Note: DBCC TRACEON(208) just means “SET QUOTED IDENTIFIER ON”)

Link: None

Trace Flag: 2508

Undocumented trace flag

Function: Disables parallel non-clustered index checking for DBCC CHECKTABLE

Link: None

Trace Flag: 2509

Scope: global or session

Trace Flag: 2529

Undocumented trace flag

Function: Displays memory usage for DBCC commands when used with TF 3604

Link: None

Trace Flag: 2536

Undocumented trace flag

Function: Allows you to see inactive records in transaction log using fn_dblog. Similar to trace flag 2537 for older version than SQL Server 2008.

Link: http://www.sqlsoldier.com/wp/sqlserver/day19of31daysofdisasterrecoveryhowmuchlogcanabackuplog

Trace Flag: 2537

Undocumented trace flag

Function: Allows you to see inactive records in transaction log using fn_dblog

Link: http://www.sqlsoldier.com/wp/sqlserver/day19of31daysofdisasterrecoveryhowmuchlogcanabackuplog

Link: http://www.sqlskills.com/blogs/paul/finding-out-who-dropped-a-table-using-the-transaction-log

Link: http://sqlserverandme.blogspot.ru/2014/03/how-to-view-transaction-log.html

Scope: session only

Trace Flag: 2540

Function: Unknown, but related to controlling the contents of a memory dump

Link: KB917825

Trace Flag: 2541

Function: Unknown, but related to controlling the contents of a memory dump

Link: KB917825

Trace Flag: 2542

Function: Unknown, but related to controlling the contents of a memory dump

Link: KB917825

Link: http://blogs.msdn.com/b/saponsqlserver/archive/2011/12/22/faster-dbcc-checkdb-released-in-sql-2008-r2-sp1-traceflag-2562-amp-2549.aspx

Trace Flag: 2543

Function: Unknown, but related to controlling the contents of a memory dump

Link: KB917825

Trace Flag: 2544

Function: Produces a full memory dump

Link: KB917825

Trace Flag: 2549

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

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

Link: https://support.microsoft.com/help/2634571

Link: https://support.microsoft.com/help/2732669

Link: http://blogs.msdn.com/b/saponsqlserver/archive/2011/12/22/faster-dbcc-checkdb-released-in-sql-2008-r2-sp1-traceflag-2562-amp-2549.aspx

Scope: global only

Trace Flag: 2550

Function: Unknown, but related to controlling the contents of a memory dump

Link: KB917825

Trace Flag: 2551

Function: Produces a filtered memory dump

Trace Flag: 2559

Function: Unknown, but related to controlling the contents of a memory dump

Link: KB917825

Trace Flag: 2562

Function: Runs the DBCC CHECKDB command in a single "batch" regardless of the number of indexes in the database. By default, the DBCC CHECKDB command tries to minimize tempdb resources by limiting the number of indexes or "facts" that it generates by using a "batches" concept. This trace flag forces all processing into one batch. One effect of using this trace flag is that the space requirements for tempdb may increase. Tempdb may grow to as much as 5% or more of the user database that is being processed by the DBCC CHECKDB command.

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

Link: https://support.microsoft.com/help/2634571

Link: https://support.microsoft.com/help/2732669

Scope: global only

Trace Flag: 2566

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

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

Link: https://sqlperformance.com/2012/11/io-subsystem/minimize-impact-of-checkdb

Link: https://support.microsoft.com/help/2888996/fix-data-purity-corruption-in-sys.sysbinobjs-table-in-master-database-when-you-log-on-to-sql-server-by-using-the-sa-account-and-then-run-dbcc-checkdb

Scope: global only

Trace Flag: 2588

Function: For SQL Server since 2005. Forces DBCC HELP to return syntax of undocumented DBCC statements. If 2520/2588 is not turned on, DBCC HELP will refuse to give you the syntax stating: "No help available for DBCC statement 'undocumented statement'". Also affects dbcc help ('?')

Link: https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/

Link: Undocumented Trace Flags: Inside the Restore Process

Scope: session only

Trace Flag: 3014

Function: Returns more info about backups to the errorlog: Backup activity, Restore activity , File creation.

Link: Important Trace Flags That Every DBA Should Know

Link: https://blogs.msdn.microsoft.com/psssql/2008/02/06/how-it-works-how-does-sql-server-backup-and-restore-select-transfer-sizes

Link: Undocumented Trace Flags: Inside the Restore Process

Link: What’s CHECKDB doing in my database restore?

Scope: session only

Trace Flag: 3023

Function: Enables CHECKSUM option as default for BACKUP command

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

Link: https://support.microsoft.com/help/2656988

Link: http://www.sqlskills.com/blogs/paul/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it

Scope: global or session

Trace Flag: 3028

Function: Enables a hotfix for a problem encountered when backing up to tape with specific backup options

Link: None

Trace Flag: 3031

Function: SQL 9 - Will turn the NO_LOG and TRUNCATE_ONLY options into checkpoints in all recovery modes

Trace Flag: 3034

Function: Overrides the server default, and thus always forces backup compression unless the backup command had the no_compression clause explicitly present.

Link: https://blogs.msdn.microsoft.com/ialonso/2012/02/24/vdi-backups-and-backup-compression-default

Trace Flag: 3035

Function: Overrides the server default to always avoid compression, unless the backup command explicitly uses the compression clause. If both 3034 and 3035 are enabled, 3035 takes precedence

Link: https://blogs.msdn.microsoft.com/ialonso/2012/02/24/vdi-backups-and-backup-compression-default

Trace Flag: 3039

Function: As long as the SQL edition supports backup compression, this will allow VDI backups to be affected by the default compression setting just as non-VDI BACKUP commands are affected.

Link: https://blogs.msdn.microsoft.com/ialonso/2012/02/24/vdi-backups-and-backup-compression-default

Trace Flag: 3042

Function: 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).

Link: https://support.microsoft.com/help/2001026/inf-space-requirements-for-backup-devices-in-sql-server

Link: https://blogs.msdn.microsoft.com/psssql/2011/08/11/how-compressed-is-your-backup/

Link: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server

Link: https://sqlstudies.com/2017/03/16/compressed-backup-errors-and-tf-3042/

Scope: global only

Trace Flag: 3051

Link: https://blogs.msdn.microsoft.com/psssql/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus/

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-3213/

Scope: global or session

Trace Flag: 3216

Function: Prints quite a lot of info about RESTORE internals. Only seems to print to the error log (TF 3605 is required).

Link: http://jamessql.blogspot.ru/2013/07/trace-flag-for-backup-and-restore.html

Trace Flag: 3222

Function: Disables the read ahead that is used by the recovery operation during roll forward operations

Link: TECHNET List Of SQL Server Trace Flags

Trace Flag: 3226

Function: By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic. 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.

Link: http://www.sqlskills.com/blogs/paul/fed-up-with-backup-success-messages-bloating-your-error-logs

Link: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/10/30/when-is-too-much-success-a-bad-thing

Link: Important Trace Flags That Every DBA Should Know

Scope: global only

Trace Flag: 3231

Function: SQL 8/9 - Will turn the NO_LOG and TRUNCATE_ONLY options into no-ops in FULL/BULK_LOGGED recovery mode, and will clear the log in SIMPLE recovery mode. When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG do not allow a log backup to run if the database's recovery model is FULL or BULK_LOGGED.

Link: http://www.sqlskills.com/blogs/paul/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it

Link: http://www.sqlskills.com/blogs/kimberly/understanding-backups-and-log-related-trace-flags-in-sql-server-20002005-and-2008

Scope: ?

Trace Flag: 3282

Undocumented trace flag

Function: SQL 6.5 - Used after backup restoration fails

Scope: ?

Trace Flag: 3400

Function: Prints the recovery timings

Link: https://connect.microsoft.com/SQLServer/feedback/details/392158/recovery-portion-of-sql-2008-restore-takes-much-longer-than-normal-when-restoring-from-sql-2005-backup

Trace Flag: 3408

Function: This forces SQL Server startup to use a single thread when recovering all DBs at SQL Server startup, instead of running through its algorithm for determining how many threads to allocate to DB recovery

Link: https://blogs.msdn.microsoft.com/ialonso/2012/10/08/how-much-is-crash-recovery-parallelized-in-which-order-are-databases-recovered/

Trace Flag: 3412

Undocumented trace flag

Function: The KB article refers to SQL 6.5, but it is possible that the TF still prints out info to the SQL error log, so leaving it here for now. KB: “...reports when each transaction is rolled forward or back [examine the error log for progress]. However, you will not see any progress if SQL Server is rolling a large transaction forward or back. Additionally, this trace flag duplicates the sp_configure setting Recovery flags..."

Link: None

Trace Flag: 3422

Function: Cause auditing of transaction log records as they're read (during transaction rollback or log recovery). This is useful because there is no equivalent to page checksums for transaction log records and so no way to detect whether log records are being corrupted e careful with these trace flags - I don't recommend using them unless you are experiencing corruptions that you can't diagnose. Turning them on will cause a big CPU hit because of the extra auditing that's happening.

Link: https://support.microsoft.com/help/215458

Link: http://www.sqlskills.com/blogs/paul/how-to-tell-if-the-io-subsystem-is-causing-corruptions

Link: http://technet.microsoft.com/en-au/library/cc917726.aspx

Link: Important Trace Flags That Every DBA Should Know

Scope: ?

Trace Flag: 3427

Function: Enables fix for issue when many consecutive transactions inserting data into temp table in SQL Server 2016 consume more CPU than in SQL Server 2014. Another change in SQL Server 2016 behavior that could impact tempdb-heavy workloads has to do with Common Criteria Compliance (CCC), also known as C2 auditing. We introduced functionality to allow for transaction-level auditing in CCC which can cause some additional overhead, particularly in workloads that do heavy inserts and updates in temp tables. Unfortunately, this overhead is incurred whether you have CCC enabled or not. In SQL Server 2016 you can enable trace flag 3427 to bypass this overhead starting with SP1 CU2. Starting in SQL Server 2017 CU4, we automatically bypass this code if CCC is disabled.

Scope: global only

Trace Flag: 3499

Undocumented trace flag

Function: Provides a workaround for doing a rolling upgrade from SQL 2005 to SQL 2008 with a DB that has a full-text index

Link: None

Trace Flag: 3502

Function: Writes info about checkpoints to error log.

Link: Important Trace Flags That Every DBA Should Know

Link: https://blogs.msdn.microsoft.com/joaol/2008/11/20/sql-server-checkpoint-problems/

Link: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions/

Scope: session only

Trace Flag: 3503

Function: Indicates whether the checkpoint at the end of automatic recovery was skipped for a database (this applies only to read-only databases)

Link: http://www.sql-server-performance.com/2002/traceflags/

Trace Flag: 3504

Function: For internal testing. Will raise a bogus log-out-of-space condition from checkpoint

Link: https://blogs.msdn.microsoft.com/joaol/2008/11/20/sql-server-checkpoint-problems/

Link: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1530-checkpoint-only-writes-pages-from-committed-transactions/

Trace Flag: 3505

Function: Disables automatic checkpoints. Setting trace flag 3505 may increase recovery time and can prevent log space reuse until the next checkpoint is issued. Make sure to issue manual checkpoints on all read/write databases at appropriate time intervals. "For high availability systems, such as clusters, Microsoft recommends that you do not change the recovery interval because it may affect data safety and availability."

Link: http://www.sqlskills.com/blogs/paul/benchmarking-1-tb-table-population-part-2-optimizing-log-block-io-size-and-how-log-io-works/

Link: Important Trace Flags That Every DBA Should Know

Scope: ?

Trace Flag: 3601

Function: Appears to disable CPU instruction prefetching. The Blog post to the right uses it, in concert with 3603, to enable SQL 2000 to run on a machine with a # of processors that is not a power of 2

Link: https://blogs.msdn.microsoft.com/sqlserverfaq/2009/05/27/info-sql-2000-msde-installation-will-fail-if-you-have-number-of-cpus-on-a-box-which-is-not-in-power-of-2

Trace Flag: 3602

Function: Records all error and warning messages sent to the client

Link: https://support.microsoft.com/help/199037/

Trace Flag: 3603

Function: Disables “Simultaneous Multithreading Processor check”. Used in concern with TF 3601 in the blog post to the right to enable SQL 2000 to run on a machine with a # of processors that is not a power of 2

Link: https://blogs.msdn.microsoft.com/sqlserverfaq/2009/05/27/info-sql-2000-msde-installation-will-fail-if-you-have-number-of-cpus-on-a-box-which-is-not-in-power-of-2

Trace Flag: 3604

Function: Enables the output from a large number of trace flags and DBCC commands to be sent back to the client. The Connect issue notes that problems can occur when using 3604 with a query that executes across a linked server. This CSS page points out that 3604 is necessary for DBCC DumpTrigger(‘display’)

Link: http://blogs.msdn.com/b/askjay/archive/2011/01/21/why-do-we-need-trace-flag-3604-for-dbcc-statements.aspx

Link: Internals of the Seven SQL Server Sorts – Part 1

Link: https://connect.microsoft.com/SQLServer/feedback/details/306380/trace-flag-issue-7300-3604

Link: How to Find the Statistics Used to Compile an Execution Plan

Link: https://blogs.msdn.microsoft.com/askjay/2011/01/21/why-do-we-need-trace-flag-3604-for-dbcc-statements/

Link: Undocumented Trace Flags: Inside the Restore Process

Link: What’s CHECKDB doing in my database restore?

Scope: session only

Trace Flag: 3605

Function: Sends a variety of types of information to the SQL Server error log instead of to the user console. Often referenced in KB and blog articles in the context of other trace flags (e.g. 3604).

Link: Undocumented Trace Flags: Inside the Restore Process

Link: What’s CHECKDB doing in my database restore?

Scope: session only

Trace Flag: 3607

Function: Skip recovery on startup

Link: http://sqlkbs.blogspot.se/2008/01/trace-flag.html

Link: https://blogs.msdn.microsoft.com/ialonso/2012/10/24/why-does-restoring-a-database-needs-tempdb/

Trace Flag: 3608

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

Note: Do not use during normal operation.

Link: Importance of Performing DBCC CHECKDB on all SQL Server Databases

Link: https://blogs.msdn.microsoft.com/ialonso/2012/10/24/why-does-restoring-a-database-needs-tempdb

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-3608/

Scope: global only

Trace Flag: 3609

Function: Recovering all databases, but not clearing tempdb

Link: http://basitaalishan.com/2012/02/20/essential-trace-flags-for-recovery-debugging/

Link: Importance of Performing DBCC CHECKDB on all SQL Server Databases

Link: https://blogs.msdn.microsoft.com/ialonso/2012/10/24/why-does-restoring-a-database-needs-tempdb

Scope: global only

Trace Flag: 3610

Function: SQL 9 - Divide by zero to result in NULL instead of error

Link: None

Trace Flag: 3614

Function: Modifies the order of startup operations so that SQL Server can successfully start up even if many user connections are being attempted during SQL startup

Link: None

Trace Flag: 3625

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

Scope: global only

Trace Flag: 3626

Function: Turns on tracking of the CPU data for the sysprocesses table.

Link: None

Trace Flag: 3628

Function: CSS’s mysterious description: “Includes ‘other errors’ in the dump based on a severity.”

Trace Flag: 3629

Function: CSS: A memory dump will “include messages marked to include with this trace flag enabled.”

Link: https://blogs.msdn.microsoft.com/selvar/2010/07/13/delete-operation-from-a-reporting-service-2005-report-manager-fails-with-internalcatalogexception-and-throws-watson-mini-dump

Trace Flag: 3635

Function: Print diagnostic information. Trace Flag 3635 Diagnostics are written to the console that started it. There are not written to the errorlog, even if 3605 is turned on.

Link: None

Trace Flag: 3640

Function: Eliminates sending DONE_IN_PROC messages to client for each statement in stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way.

Trace Flag: 3651

Undocumented trace flag

Function: Can cause stack dumps. For one query, this resulted in a parallel plan significantly more expensive than the naturally occurring serial plan.

Link: https://blogs.msdn.microsoft.com/psssql/2012/11/12/how-can-reference-counting-be-a-leading-memory-scribbler-cause

Scope: ?

Trace Flag: 3654

Function: Apparently increases info found in the sys.dm_os_memory_allocations DMV (which appears to have replaced the DBCC MEMOBJLIST command) Bob Ward also discusses it in his PASS 2013 session, saying that it turns on tracing for all memory allocations done by “Memory Objects” (a specific SQLOS memory term). This flag will have a significant impact on system performance.

Link: https://blogs.msdn.microsoft.com/slavao/2005/08/30/talking-points-around-memory-manager-in-sql-server-2005

Link: https://support.microsoft.com/help/2888658/

Trace Flag: 3656

Function: Enables resolve of all call stacks in extended events

Link: http://sqlcat.com/sqlcat/b/msdnmirror/archive/2010/05/11/resolving-dtc-related-waits-and-tuning-scalability-of-dtc.aspx

Link: http://www.sqlskills.com/blogs/paul/determine-causes-particular-wait-type

Trace Flag: 3659

Link: https://blogs.msdn.microsoft.com/sqlserverfaq/2011/05/11/inf-hey-my-sql-server-service-is-not-starting-what-do-i-do

Link: https://blogs.msdn.microsoft.com/psssql/2008/09/05/sql-server-2005-setup-fails-in-wow-x86-on-computer-with-more-than-32-cpus

Link: Upgrading an expired SQL Server 2016 Evaluation Edition

Trace Flag: 4013

Function: Trace flag 4013 write entries in error log whenever a new connection established. These entries contain login name and SPID also.

Link: http://sqlkbs.blogspot.se/2008/01/trace-flag.html

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-4013/

Scope: global or session

Trace Flag: 4020

Function: Boot without recover

Link: None

Trace Flag: 4022

Function: Directs the SQL instance to ignore stored procedures that have been configured as “auto-start” procedures. Their auto-start configuration is not affected, so the next time the instance is started w/o this flag they will return to their normal behavior.

Link: https://blogs.msdn.microsoft.com/sqlserverfaq/2011/05/11/inf-hey-my-sql-server-service-is-not-starting-what-do-i-do/

Link: Upgrading an expired SQL Server 2016 Evaluation Edition

Link: Change SQL Server Collation – Back to Basics

Scope: global only

Trace Flag: 4029

Function: Logs extended errors to errorlog when network disconnect occurs, turned off by default. Will dump out the socket error code this can sometimes give you a clue as to the root cause.

Link: https://blogs.msdn.microsoft.com/sql_protocols/2005/12/19/vss-sql-server-does-not-exist-or-access-denied

Trace Flag: 4030

Function: Prints both a byte and ASCII representation of the receive buffer. Used when you want to see what queries a client is sending to SQL Server. You can use this trace flag if you experience a protection violation and want to determine which statement caused it. Typically, you can set this flag globally or use SQL Server Enterprise Manager. You can also use DBCC INPUTBUFFER.

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-4030/

Scope: global only

Trace Flag: 4031

Function: Prints both a byte and ASCII representation of the send buffers (what SQL Server sends back to the client). You can also use DBCC OUTPUTBUFFER.

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-4031/

Scope: global only

Trace Flag: 4032

Function: Traces the SQL commands coming in from the client. When enabled with 3605 it will direct those all to the error log.

Link: https://support.microsoft.com/help/199037/

Function: Bugfix for error: parallel query returning different results every time The trace flag disables an optimization in the query optimizer. The optimization caused the issue described in the KB article when you try to insert into a table by selecting from the table itself. As turning on the trace flag could result in a perf degradation, you only should use it if you run into the issue described in the KB article.

Link: https://support.microsoft.com/help/2546901

Link: http://sql-sasquatch.blogspot.se/2014/04/whaddayaknow-bout-sqlserver-trace-flag.html

Link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ea718c2-e0e0-40cf-b12b-3269130448b7/trace-flag-4135-sql-server-2008?forum=sqldatabaseengine

Trace Flag: 4135

Function: Bugfix for error inserting to temp table

Link: https://support.microsoft.com/help/960770

Link: https://connect.microsoft.com/SQLServer/feedback/details/541352/tempdb-errors-during-statistics-auto-update

Trace Flag: 4136

Function: Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR value is used. To accomplish this at the database level, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). To accomplish this at the query level, add the OPTIMIZE FOR UNKNOWN query hint. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT query hint instead of using this trace flag. Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

Link: http://blogs.msdn.com/b/axinthefield/archive/2010/11/04/sql-server-trace-flags-for-dynamics-ax.aspx

Link: New Features in SQL Server 2016 Service Pack 1

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-4136/

Link: http://kejser.org/trace-flag-4136-2/

Link: http://www.sqlservergeeks.com/sql-server-did-you-know-about-trace-flag-4136/

Scope: global or session or query

Trace Flag: 4137

Function: Causes SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation, under the query optimizer cardinality estimation model of SQL Server 2012 and earlier versions

Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT query hint instead of using this trace flag. Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

Link: https://support.microsoft.com/help/2658214

Link: New Features in SQL Server 2016 Service Pack 1

Scope: global or session or query

Trace Flag: 4138

Function: Causes SQL Server to generate a plan that does not use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords

Link: https://support.microsoft.com/help/2667211

Link: New Features in SQL Server 2016 Service Pack 1

Link: https://answers.sqlperformance.com/questions/1609/trying-to-figure-out-how-to-resolve-the-data-skew.html

Link: http://dba.stackexchange.com/questions/55198/huge-slowdown-to-sql-server-query-on-adding-wildcard-or-top

Scope: global or session or query

Trace Flag: 4139

Function: 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

Link: https://support.microsoft.com/help/2952101

Link: SQL Server - estimates outside of the histogram - half-baked draft

Link: Parallelism in Hekaton (In-Memory OLTP)

Link: Important Trace Flags That Every DBA Should Know

Link: https://support.microsoft.com/help/974006

Scope: global or session or query

Trace Flag: 4199

Function: Enables query optimizer (QO) changes released in SQL Server Cumulative Updates and Service Packs. QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 enabled.

The following table summarizes the behavior when using specific database compatibility levels and trace flag 4199:

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

Database compatibility level

TF 4199

QO changes from previous database compatibility levels

QO changes for current version post-RTM

100 to 120

Off

Disabled

Enabled

Disabled

Enabled

130

Off

Enabled

Disabled

Enabled

140

Off

Enabled

Disabled

Enabled

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

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

Link: https://support.microsoft.com/help/974006

Link: New Features in SQL Server 2016 Service Pack 1

Link: https://sqlworkbooks.com/2017/04/selectively-enabletrace-flag-4199-and-query_optimizer_hotfixes-in-sql-server-2016/

Link: https://support.microsoft.com/help/974006/

Link: https://sqlworkbooks.com/2017/04/trace-flag-4199-no-per-session-override-if-you-enable-it-globally/

Link: http://www.sqlservergeeks.com/sql-server-2016-database-scoped-configuration-and-trace-flag-4199/

Scope: global or session or query

Trace Flag: 4606

Function: Over comes SA password by startup. Disables password policy check during server startup.

Link: https://blogs.msdn.microsoft.com/sqlserverfaq/2011/05/11/inf-hey-my-sql-server-service-is-not-starting-what-do-i-do

Link: https://blogs.msdn.microsoft.com/sqlserverfaq/2008/07/31/upgrade-of-sql-server-2000-instance-to-sql-server-2005-fails-with-error-similar-to-enforce-password-policy

Trace Flag: 4610

Function: When you use trace flag 4618 together with trace flag 4610, the number of entries in the cache store is limited to 8,192. When the limit is reached, SQL 2005 removes some entries from the TokenAndPermUserStore cache store.

Link: https://support.microsoft.com/help/959823

Link: https://blogs.msdn.microsoft.com/psssql/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache/

Scope: global only

Trace Flag: 4612

Function: Disable the ring buffer logging - no new entries will be made into the ring buffer

Link: http://blogs.msdn.com/b/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx

Trace Flag: 4613

Function: Generate a minidump file whenever an entry is logged into the ring buffer

Link: http://blogs.msdn.com/b/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx

Trace Flag: 4614

Function: Enables SQL Server authenticated logins that use Windows domain password policy enforcement to log on to the instance even though the SQL Server service account is locked out or disabled on the Windows domain controller.

Link: https://support.microsoft.com/help/925744

Trace Flag: 4616

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

Link: https://support.microsoft.com/help/906549/

Scope: global only

Trace Flag: 4618

Function: Limits number of entries per user cache store to 1024. It may incur a small CPU overhead as when removing old cache entries when new entries are inserted. It performs this action to limit the size of the cache store growth. However, the CPU overhead is spread over time. When used together with trace flag 4610 increases the number of entries in the TokenAndPermUserStore cache store to 8192

Link: https://connect.microsoft.com/SQLServer/feedback/details/467661/sql-server-2008-has-incorrect-cache-names-in-sys-dm-os-memory-cache-counters

Link: https://support.microsoft.com/help/933564

Link: https://support.microsoft.com/help/959823/

Scope: global only

Trace Flag: 4620

Function: According to the Connect item, causes permission checking to be done on a global cache instead of the per-user caches that were introduced in SQL 2008. The thread includes some interesting information on the cache stores, especially as they relate to TokenPermAndUserStore.

Trace Flag: 4621

Function: SQL 9 – After 4610 & 4618 you can still customize the quota for TokenAndPermUserStore cache store that is based on the current workload

Link: https://support.microsoft.com/help/959823

Trace Flag: 5004

Function: 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.

Link: http://dba.fyicenter.com/Interview-Questions/SYBASE/What_is_Trace_Flag_Definitions_in_Sybase.html#1.3.4#1.3.4

Scope: global only

Trace Flag: 5101

Function: Forces all I/O requests to go through engine 0. This removes the contention between processors but could create a bottleneck if engine 0 becomes busy with non-I/O tasks.

Trace Flag: 5102

Function: Prevents engine 0 from running any non-affinitied tasks.

Link: http://dba.fyicenter.com/Interview-Questions/SYBASE/What_is_Trace_Flag_Definitions_in_Sybase.html#1.3.4#1.3.4

Trace Flag: 5302

Function: Alters default behavior of select…INTO (and other processes) that lock system tables for the duration of the transaction. This trace flag disables such locking during an implicit transaction.

Link: https://support.microsoft.com/help/153096/

Trace Flag: 6498

Function: Enables more than one large query compilation to gain access to the big gateway when there is sufficient memory available. It is based on the 80 percentage of SQL Server Target Memory, and it allows for one large query compilation per 25 gigabytes (GB) of memory.

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

Link: https://support.microsoft.com/help/3024815

Link: http://blogs.msdn.com/b/sql_server_team/archive/2015/10/09/query-compile-big-gateway-policy-changes-in-sql-server.aspx

Scope: global only

Trace Flag: 6527

Function: Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration. By default, SQL Server generates a small memory dump on the first occurrence of an out-of-memory exception in the CLR. The behaviour of the trace flag is as follows: If this is used as a startup trace flag, a memory dump is never generated. However, a memory dump may be generated if other trace flags are used. If this trace flag is enabled on a running server, a memory dump will not be automatically generated from that point on. However, if a memory dump has already been generated due to an out-of-memory exception in the CLR, this trace flag will have no effect.

Link: https://blogs.msdn.microsoft.com/psssql/2013/11/19/spatial-indexing-from-4-days-to-4-hours

Scope: global only

Trace Flag: 6530

Function: Enables a hotfix for “ FIX: Slow performance in SQL Server 2012 or SQL Server 2014 when you build an index on a spatial data type of a large table”

Link: https://support.microsoft.com/help/2896720/

Trace Flag: 6531

Function: Enables adjustment in the SQLOS scheduling layer to handle queries that issue many short-duration calls to spatial data (which is implemented via CLR functions): “ This fix introduces the trace flag 6531 to indicate to the SQLOS hosting layer that the spatial data type should avoid preemptive protections. This can reduce the CPU consumption and improve the overall performance for spatial activities. Only use this trace flag if the individual, spatial method invocations (per row and column) take less than ~4ms. Longer invocations without preemptive protection could lead to scheduler concurrency issues and SQLCLR punishment messages logged to the error log.”

Link: https://support.microsoft.com/help/3005300/

Trace Flag: 6532

Function: Enables performance improvement of query operations with spatial data types in SQL Server 2012 and SQL Server 2014. The performance gain will vary, depending on the configuration, the types of queries, and the objects.

Link: KB3107399

Undocumented trace flag

Function: Disable table lock promotion for text columns

Link: None

Trace Flag: 7300

Function: Outputs extra info about linked server errors

Link: https://support.microsoft.com/help/314530

Link: https://support.microsoft.com/help/280106/

Link: https://support.microsoft.com/help/280102/

Link: https://connect.microsoft.com/SQLServer/feedback/details/306380/trace-flag-issue-7300-3604

Trace Flag: 7301

Function: Fixes a problem in SQL 6.5 where SELECT INTO queries with text/image types were not bulk-logged.

Link: None

Trace Flag: 7311

Function: Offers a new alternative to handling the tricky problem of converting Oracle NUMBER types (across OLEDB linked server queries) with unknown precision/scale to a valid SQL Server data type, by treating all such types as NUMERIC(38,10).

Link: https://support.microsoft.com/help/3051993/

Trace Flag: 7314

Function: Forces NUMBER values with unknown precision/scale to be treated as double values with OLE DB provider

Link: http://sqlblog.com/blogs/paul_white/archive/2013/08/31/sql-server-internals-nested-loops-prefetching.aspx

Link: https://support.microsoft.com/help/3051993

Scope: global or session

Trace Flag: 7352

Function: Show the optimizer output and the post-optimization rewrite in action (After Post Optimization Rewrite)

Link: Internals of the Seven SQL Server Sorts – Part 1

Link: http://www.queryprocessor.com/batch-sort-and-nested-loops

Link: Few Outer Rows Optimization

Related to 8607 trace flag

Scope: session only

Trace Flag: 7356

Undocumented trace flag

Function: Added a probe residual to an adaptive join. Full effect unknown.

Link: http://www.queryprocessor.com/hash-join-execution-internals

Scope: ?

Trace Flag: 7357

Function: Outputs info re: hashing operators, including role reversal, recursion levels, whether the Unique Hash optimization could be used, info about the hash-related bitmap, etc. Dima’s article is a must-read. For parallel query plans, 7357 does NOT send output to the console window. However, output to the SQL Server error log can be enabled by enabling 3605.

Trace Flag: 7359

Function: Disables the bitmap associated with hash matching. This bitmap is used for “bit-vector filtering” and can reduce the amount of data written to TempDB during hash spills.

Link: www.queryprocessor.com/hash-join-execution-internals

Trace Flag: 7398

Undocumented trace flag

Function: Changed a nested loop join to have ordered prefetch.

Scope: ?

Trace Flag: 7470

Function: Fixes a problem where under certain (unknown) conditions, a sort spill occurs for large sorts

Link: https://support.microsoft.com/help/3088480/

Trace Flag: 7412

Function: Enables the lightweight query execution statistics profiling infrastructure. unless your server is already CPU bound, like you’re running all the time with 95% CPU, unless you are at that point, turn on this trace flag at any server you have. This would be my advice here because this enables that lightweight profiling infrastructure there and then you’ll see in a few minutes what it unleashes here. So one thing that happens when I enable the lightweight profiling is that the sys.dm_exec_query_profiles DMV, which is something that actually populates the live query stats ability or feature of SSMS, now also is also populated with this lightweight profiling, which means that for all essence, we are now able to run a live query stats on all fashions at any given point in time, and this is extremely useful for let’s say a production DBA that someone calls and says, “Hey, you have a problem. To tap into running system and look at what it’s doing.”

Link: https://www.brentozar.com/archive/2017/10/get-live-query-plans-sp_blitzwho/

Link: https://support.microsoft.com/help/3170113

Link: https://groupby.org/conference-session-abstracts/enhancements-that-will-make-your-sql-database-engine-roar-2016-sp1-edition/

Link: https://www.scarydba.com/2018/06/11/plan-metrics-without-the-plan-trace-flag-7412/

Scope: global only

Trace Flag: 7470

Function: Fix for sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct

Link: https://support.microsoft.com/help/3088480

Trace Flag: 7471

Trace Flag: 7745

Function: Forces Query Store to not flush data to disk on database shutdown.

Note: Using this trace may cause Query Store data not previously flushed to disk to be lost in case of shutdown. For a SQL Server shutdown, the command SHUTDOWN WITH NOWAIT can be used instead of this trace flag to force an immediate shutdown.

Link: Query Store Trace Flags

Scope: global only

Trace Flag: 7752

Function: Enables asynchronous load of Query Store.

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

Link: Query Store Trace Flags

Scope: global only

Trace Flag: 7806

Function: Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express.

Link: https://msdn.microsoft.com/en-us/library/ms189595.aspx

Link: https://sqlperformance.com/2012/08/sql-memory/test-your-dac-connection

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-7806/

Scope: global only

Trace Flag: 7826

Function: Disable Connectivity ring buffer

Link: http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

Trace Flag: 7827

Function: Record connection closure info in ring buffer

Link: http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

Link: https://connect.microsoft.com/SQLServer/feedback/details/518158/-packet-error-a-fatal-error-occurred-while-reading-the-input-stream-from-the-network

Trace Flag: 7833

Function: SQL 2012 SP2 CU8 introduced a fix for a “silent error” condition in the sqlcmd tool. The CU also included this flag to allow customers to revert to pre-CU fix behavior.

Link: https://support.microsoft.com/help/3082877/

Trace Flag: 8001

Function: Khen2005, p2: “For SQL Server 2005, the SQL Server product team opted not to include some wait types that fall under one of the following three categories:

Wait types that are never used in SQL Server 2005; note that some wait types not excluded are also never used.
Wait types that can occur only at times when they do not affect user activity, such as during initial server startup and shutdown, and are not visible to users.
Wait types that are innocuous but have caused concern among users because of their high occurrence or duration The complete list of wait types is available by enabling trace flag 8001. The only effect of this trace flag is to force sys.dm_os_wait_stats to display all wait types.”
Link: None

Trace Flag: 8002

Function: Changes CPU Affinity behaviour

Link: https://blogs.msdn.microsoft.com/psssql/2011/11/11/sql-server-clarifying-the-numa-configuration-information

Trace Flag: 8004

Function: SQL server to create a mini-dump once you enable 2551 and a out of memory condition is hit

Link: https://connect.microsoft.com/SQLServer/feedback/details/342691/not-enough-memory-was-available-for-trace-error-when-attempting-to-profile-sql-2008

Trace Flag: 8008

Undocumented trace flag

Function: Force the scheduler hint to be ignored. Always assign to the scheduler with the least load (pool based on SQL 2012 EE SKU or Load Factor for previous versions and SKUs.)

Link: How It Works: SQL Server 2012 Database Engine Task Scheduling

Link: https://blogs.msdn.microsoft.com/psssql/2013/08/13/how-it-works-sql-server-2012-database-engine-task-scheduling

Link: http://www.stillhq.com/sqldownunder/archives/msg05089.html

Trace Flag: 8009

Undocumented trace flag

Function: Enables the “idle state behavior” (see IO Basics, Chapter 2 document) that a SQL instance can enter under certain conditions.

Link: https://technet.microsoft.com/en-us/library/cc917726.aspx

Trace Flag: 8010

Function: Disables the “idle state” behavior that a SQL instance can enter (see TF 8009). Fixes problem that SQL Server services can not be stopped

Link: https://support.microsoft.com/help/2633271 Link: https://technet.microsoft.com/en-us/library/cc917726.aspx

Trace Flag: 8011

Function: Trace flag 8011 disables the collection of additional diagnostic information for Resource Monitor. You can use the information in this ring buffer to diagnose out-of-memory conditions. 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.

Link: KB920093

Link: http://www.sqlservergeeks.com/sql-server-ring-buffer-trace-flag-8011/

Scope: global only

Trace Flag: 8012

Function: Disable the ring buffer for schedulers. SQL Server 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 SQL Server stops responding. Trace flag 8012 disables recording of events for schedulers. You can turn on trace flag 8012 only at startup. 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.

Link: KB920093

Link: http://www.sqlservergeeks.com/sql-server-ring-buffer-trace-flag-8012/

Scope: global only

Trace Flag: 8015

Function: Disable auto-detection and NUMA setup

Link: http://sql-sasquatch.blogspot.se/2013/04/startup-trace-flags-i-love.html

Link: Upgrading an expired SQL Server 2016 Evaluation Edition

Scope: global only

Trace Flag: 8016

Undocumented trace flag

Function: Force load balancing to be ignored. Always assign to the preferred scheduler.

Link: How It Works: SQL Server 2012 Database Engine Task Scheduling

Trace Flag: 8017

Function: Upgrade version conflict

Link: http://social.msdn.microsoft.com/Forums/eu/sqlexpress/thread/dd6fdc16-9d8d-4186-9549-85ba4c322d10

Link: http://connect.microsoft.com/SQLServer/feedback/details/407692/indicateur-de-trace-8017-reported-while-upgrading-from-ssee2005-to-ssee2008

Link: http://dba.stackexchange.com/questions/48580/trace-flag-and-which-need-to-be-turned-off-and-why

Trace Flag: 8018

Function: Disables the creation of the ring buffer, and no exception information is recorded. Disabling the exception ring buffer makes it more difficult to diagnose problems that are related to internal server errors.

Link: KB920093

Link: http://www.sqlservergeeks.com/sql-server-ring-buffer-trace-flag-8018/

Link: http://www.sqlservergeeks.com/sql-server-ring-buffer-trace-flag-8019/

Scope: global only

Trace Flag: 8019

Function: Disable stack collection for the exception ring buffer Disables stack collection during the record creation. Trace flag 8019 has no effect if trace flag 8018 is turned on.

Link: KB920093

Link: http://www.sqlservergeeks.com/sql-server-ring-buffer-trace-flag-8019/

Scope: global only

Trace Flag: 8020

Function: Disable working set monitoring. SQL Server uses the size of the working set when SQL Server interprets the global memory state signals from the operating system. Trace flag 8020 removes the size of the working set from consideration when SQL Server interprets the global memory state signals. If you use this trace flag incorrectly, heavy paging occurs, and the performance is poor. Therefore, contact Microsoft Support before you turn on trace flag 8020.

Link: KB920093

Link: https://blogs.msdn.microsoft.com/psssql/2011/11/11/sql-server-clarifying-the-numa-configuration-information/

Scope: global only

Trace Flag: 8021

Function: On some lower end hardware we used to get reported that each CPU has its own NUMA node. This was usually incorrect and when we detected only a single CPU per NODE we would assume NO NUMA. Trace flag 8021 disables this override.

Trace Flag: 8022

Function: This flag gives more information about the conditions when a non-yielding scheduler/situation was encountered. The whitepaper linked to on the right gives example output for this flag

Link: None

Trace Flag: 8024

Function: When this TF is on, it affects the mini-dump generation logic for the 1788* errors: "To capture a mini-dump, one of the following checks must also be met.

The non-yielding workers CPU utilization must be > 40 percent.
The SQL Server process is not starved for overall CPU resource utilization. Additional check #1 is targeted at runaway CPU users. Additional check #2 is targeted at workers with lower utilizations that are probably stuck in an API call or similar activity."
Link: How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888

Trace Flag: 8025

Function: SQL on NUMA normally does most of its allocation on Node 1, because usually Windows and other programs will allocate from Node 0. However, if you want SQL to do its resource allocation on the default node (node 0), turn on this flag.

Link: https://blogs.msdn.microsoft.com/psssql/2011/11/11/sql-server-clarifying-the-numa-configuration-information

Trace Flag: 8026

Function: SQL Server will clear a dump trigger after generating the dump once

Link: KB917825

Trace Flag: 8030

Function: Fix for performance bug

Link: https://support.microsoft.com/help/917035

Trace Flag: 8032

Function: Alters cache limit settings

Warning: Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.

Link: https://blogs.msdn.microsoft.com/psssql/2007/08/19/sql-server-2005-rdtsc-truths-and-myths-discussed

Scope: global only

Trace Flag: 8033

Function: Suppresses messages of the form “The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs” from being placed in the SQL Error log when CPU drift is noticed

Warning: SQL 9 - Disable the reporting of CPU Drift errors in the SQL Server error log like time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.

Link: https://support.microsoft.com/help/931279/

Trace Flag: 8038

Function: Will drastically reduce the number of context switches when running SQL 2005 or 2008

Link: KB972767

Link: http://forum.proxmox.com/threads/15844-Win7-x64-guest-with-SQLServer-2012-High-CPU-usage

Link: TECHNET List Of SQL Server Trace Flags

Trace Flag: 8040

Function: Disables Resource Governor

Link: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/64/sql-server-disabling-resource-governor-permanently-somewhat

Trace Flag: 8048

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

Function: Converts NUMA partitioned memory objects into CPU partitioned

Link: http://sql-sasquatch.blogspot.se/2013/04/startup-trace-flags-i-love.html

Link: https://support.microsoft.com/help/2809338

Link: http://blogs.msdn.com/b/psssql/archive/2012/12/20/how-it-works-cmemthread-and-debugging-them.aspx

Link: http://blogs.msdn.com/b/psssql/archive/2011/09/01/sql-server-2008-2008-r2-on-newer-machines-with-more-than-8-cpus-presented-per-numa-node-may-need-trace-flag-8048.aspx

Link: Hidden Performance & Manageability Improvements in SQL Server 2012 / 2014

Related to: 8015, 9024

Scope: global only

Trace Flag: 8049

Function: SQL 9+ Startup only – Allows use of 1ms times even when patched. Check 8038 for details.

Link: KB972767

Link: https://blogs.msdn.microsoft.com/psssql/2010/08/18/how-it-works-timer-outputs-in-sql-server-2008-r2-invariant-tsc

Trace Flag: 8050

Function: Causes "optional" wait types (see the CSS article) to be excluded when querying sys.dm_os_wait_stats

Link: https://blogs.msdn.microsoft.com/psssql/2009/11/02/the-sql-server-wait-type-repository/

Trace Flag: 8075

Function: Enables a fix (after applying the appropriate CU) for x64 VAS exhaustion.

Link: https://support.microsoft.com/help/3074434/

Trace Flag: 8079

Function: Allows SQL Server 2014 SP2 to interrogate the hardware layout and automatically configure Soft-NUMA on systems reporting 8 or more CPUs per NUMA node. The automatic Soft-NUMA behavior is Hyperthread (HT/logical processor) aware. The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling and network and encryption capabilities. When Trace Flag 8079 is enabled during startup, SQL Server 2012 SP4 will interrogate the hardware layout and automatically configure Soft NUMA on systems reporting 8 or more CPUs per NUMA node. It is recommended to first test the performance of workload with Auto-Soft NUMA before it is turned ON in production. Note: This trace flag applies to SQL Server 2014 SP2 and SQL Server 2012 SP4. Beginning with SQL Server 2016 this behavior is controlled by the engine and trace flag 8048 has no effect.

Link: KB972767

Link: https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2012-service-pack-4-sp4-released/

Link: Hidden Performance & Manageability Improvements in SQL Server 2012 / 2014

Scope: global only

Trace Flag: 8202

Function: Used to replicate UPDATE as DELETE/INSERT pair at the publisher. i.e. UPDATE commands at the publisher can be run as an "on-page DELETE/INSERT" or a "full DELETE/INSERT". If the UPDATE command is run as an "on-page DELETE/INSERT," the Logreader send UDPATE command to the subscriber, If the UPDATE command is run as a "full DELETE/INSERT," the Logreader send UPDATE as DELETE/INSERT Pair. If you turn on trace flag 8202, then UPDATE commands at the publisher will be always send to the subscriber as DELETE/INSERT pair.

Link: None

Trace Flag: 8203

Function: Display statement and transaction locks on a deadlock error

Link: None

Trace Flag: 8206

Function: SQL 8 - Supports stored procedure execution with a user specified owner name for SQL Server subscribers or without owner qualification for heterogeneous subscribers

Link: None

Trace Flag: 8207

Function: Enables singleton updates for Transactional Replication. Updates to subscribers can be replicated as a DELETE and INSERT pair. This might not meet business rules, such as firing an UPDATE trigger. With trace flag 8207 an update to a unique column that affects only one row (a singleton update) is replicated as an UPDATE and not as a DELETE or INSERT pair. If the update affects a column on which has a unique constraint or if the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair.

Link: https://blogs.msdn.microsoft.com/psssql/2009/11/02/the-sql-server-wait-type-repository/

Link: https://social.msdn.microsoft.com/forums/sqlserver/en-US/00250311-7991-47b0-b788-7fae2e102254/trace-flag-8295

Scope: global only

Trace Flag: 8209

Function: Output extra information to error log regarding replication of schema changes in SQL Server Replication

Link: None

Trace Flag: 8218

Function: Determine whether trace flag to bypass proc generation has been set. Referenced in the system procedure [master].[sys].[sp_cdc_vupgrade]

Link: None

Trace Flag: 8295

Function: Creates a secondary index on the identifying columns on the change tracking side table at enable time

Link: https://support.microsoft.com/help/2476322/

Link: https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking

Link: https://blogs.technet.microsoft.com/smartinez/2013/03/06/sql-server-for-configmgr-2012-ebook-and-top-10-database-issues

Thanks to: Wilfred van Dijk

Trace Flag: 8446

Function: Databases in SQL 8 do not have a Service Broker ID. If you restore these databases on SQL 9 by using the WITH NORECOVERY option, these databases will not be upgraded causing mirroring & log-shipping configurations to fail.

Link: https://support.microsoft.com/help/959008

Trace Flag: 8501

Function: Writes detailed information about Ms-DTC context & state changes to the log

Link: None

Trace Flag: 8599

Function: Allows you to use a save-point within a distributed transaction

Link: None

Trace Flag: 8602

Function: This trace flag is used to ignore all the index hints specified in query or stored procedure. We can use this trace flag to troubleshooting the query performance without changing index hints.

Link: http://download.microsoft.com/download/6/e/5/6e52bf39-0519-42b7-b806-c32905f4a066/eim_perf_flowchart_final.pdf

Link: http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/26/lost-without-a-trace.aspx

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-8602/

Scope: global only

Demo: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Trace_Flag/Trace_Flag_8602.sql

Trace Flag: 8605

Undocumented trace flag

Function: Displays logical and physical trees used during the optimization process

Link: More Undocumented Query Optimizer Trace Flags

Trace Flag: 8606

Undocumented trace flag

Function: Show LogOp Trees

Link: Cardinality Estimation Framework 2014 First Look

Trace Flag: 8607

Function: Displays the optimization output tree during the optimization process (Before Post Optimization Rewrite).

Link: Internals of the Seven SQL Server Sorts – Part 1

Link: More Undocumented Query Optimizer Trace Flags

Link: http://www.queryprocessor.ru/optimizer-part-3-full-optimiztion-optimization-search0

Link: Few Outer Rows Optimization

Scope: session only

Trace Flag: 8608

Function: Shows the initial Memo structure

Link: http://www.benjaminnevarez.com/2012/04/inside-the-query-optimizer-memo-structure

Link: http://sqlblog.com/blogs/paul_white/archive/2012/04/29/query-optimizer-deep-dive-part-3.aspx

Trace Flag: 8609

Function: PWhite: “Task and operation type counts”.

Link: http://sqlblog.com/blogs/paul_white/archive/2013/06/11/hello-operator-my-switch-is-bored.aspx

Link: http://www.queryprocessor.ru/good-enough-plan

Scope: session only

Trace Flag: 8612

Function: Add Extra Info to the Trees Output

Link: Cardinality Estimation Framework 2014 First Look

Trace Flag: 8615

Undocumented trace flag

Function: Display the final memo structure

Link: http://www.benjaminnevarez.com/2012/04/inside-the-query-optimizer-memo-structure/

Link: http://www.somewheresomehow.ru/optimizer-part-3-full-optimiztion-optimization-search0/

Link: http://sqlblog.com/blogs/paul_white/archive/2013/02/06/incorrect-results-with-indexed-views.aspx

Scope: session only

Trace Flag: 8619

Undocumented trace flag

Function: Show Applied Transformation Rules

Link: Cardinality Estimation Framework 2014 First Look

Scope: session only

Trace Flag: 8620

Undocumented trace flag

Function: Add memo arguments to trace flag 8619

Scope: session only

Trace Flag: 8621

Undocumented trace flag

Function: Rule with resulting tree

Link: https://blogfabiano.com/2012/07/03/statistics-used-in-a-cached-query-plan

Scope: session only

Trace Flag: 8628

Undocumented trace flag

Function: Prevents the SQL Server optimizer from using a Hash Match Team operator

Link: None

Trace Flag: 8688

Undocumented trace flag

Function: Disables parallel scans.

Link: https://blogs.msdn.microsoft.com/psssql/2015/12/15/spool-operator-and-trace-flag-8690/

Scope: ?

Trace Flag: 8690

Undocumented trace flag

Function: Disable the spool on the inner side of nested loop. Spools improve performance in majority of the cases. But it’s based on estimates. Sometimes, this can be incorrect due to unevenly distributed or skewed data, causing slow performance. But in vast majority of situations, you don’t need to manually disable spool with this trace flag.

Link: http://dba.stackexchange.com/questions/52552/index-not-making-execution-faster-and-in-some-cases-is-slowing-down-the-query

Link: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=453982

Trace Flag: 8691

Undocumented trace flag

Function: 'performance spool' optimization to the RegEx execution, reduces the number of executions of the RegEx function.

Link: Splitting Strings Based on Patterns

Scope: ?

Trace Flag: 8692

Function: Force optimizer to use an Eager Spool for Halloween Protection

Link: http://www.sqlperformance.com/2013/02/sql-plan/halloween-problem-part-4

Link: https://sqlperformance.com/2016/03/sql-plan/changes-to-a-writable-partition-may-fail

Trace Flag: 8719

Function: In SQL 2000, apparently would show IO prefetch on loop joins and bookmarks. I (Aaron) was unable to replicate the query plan behavior on SQL 2012 using the same test, so this flag may be obsolete.

Link: http://www.hanlincrest.com/SQLServerLockEscalation.htm

Trace Flag: 8720

Function: In SQL 2000, apparently would have the same effect as OPTION(KEEPFIXED PLAN)

Link: http://www.hanlincrest.com/SQLserverStoredProcRecompiles.htm

Trace Flag: 8721

Function: Reports to the error log when auto-update statistics executes

Link: https://support.microsoft.com/help/195565

Link: http://www.sqlservergeeks.com/sql-server-trace-flag-8721/

Scope: global only

Trace Flag: 8722

Function: Disable all hints except locking hints

Link: http://sqlmag.com/sql-server/investigating-trace-flags

Trace Flag: 8738

Function: (Apparently) disables an optimization where rows are sorted before a Key Lookup operator. (The optimization is meant to promote Sequential IO rather than the random nature of IO from Key Lookups). Note that the context in which this flag is described means that the above description may not be very precise, or even the only use of this flag.

Link: https://answers.sqlperformance.com/questions/603/why-is-the-sort-operator-needed-in-this-plan.html

Trace Flag: 8739

Function: Group Optimization Information

Link: http://www.queryprocessor.ru/good-enough-plan

Trace Flag: 8741

Function: Disable all locking hints

Link: http://sqlmag.com/sql-server/investigating-trace-flags

Trace Flag: 8757

Function: Skip trivial plan optimization and force a full optimization

Link: More Undocumented Query Optimizer Trace Flags

Link: http://sqlblog.com/blogs/paul_white/archive/2012/04/28/query-optimizer-deep-dive-part-1.aspx

Trace Flag: 8758

Function: “A [workaround to the MERGE bug described] is to apply Trace Flag 8758 –unfortunately this disables a number of optimisations, not just the one above, so it’s not really recommended for long term use.” “Disable rewrite to a single operator plan (CPhyOp_StreamUpdate::PqteConvert)”

Link: http://sqlblog.com/blogs/paul_white/archive/2010/08/04/another-interesting-merge-bug.aspx

Link: http://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx

Trace Flag: 8759

Undocumented trace flag

Function: Detect and write part of the query to the error log when it has been autoparameterized.

Link: https://github.com/ktaranov/sqlserver-kit/issues/146#issue-358855110

Scope: ?

Trace Flag: 8765

Function: Allows use of variable length data, from ODBC driver; fixes the issue of a field returning the wrong data length

Link: http://jacob.steelsmith.org/content/sql-server-and-ole-db

Scope: global or session

Trace Flag: 8780

Function: Give the optimizer more time to find a better plan

Link: http://www.queryprocessor.ru/optimizer_unleashed_1

Link: http://www.sqlservice.se/sql-server-trace-flag-8780/

Scope: global or session

Trace Flag: 8783

Function: Allows DELETE, INSERT, and UPDATE statements to honor the SET ROWCOUNT ON setting when enabled

Link: None

Trace Flag: 8790

Function: PWhite: “Undocumented trace flag 8790 forces a wide update plan for any data-changing query (remember that a wide update plan is always possible)”

Link: https://support.microsoft.com/help/956718/

Link: http://sqlblog.com/blogs/paul_white/archive/2012/12/10/merge-bug-with-filtered-indexes.aspx

Link: https://sqlperformance.com/2014/06/sql-plan/filtered-index-side-effect

Trace Flag: 8795

Function: PWhite: “Disable DML Request Sort (CUpdUtil::FDemandRowsSortedForPerformance)”

Link: http://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx

Link: https://sqlperformance.com/2014/10/t-sql-queries/performance-tuning-whole-plan

Trace Flag: 8799

Undocumented trace flag

Function: Forces unordered scans.

Link: https://blogs.msdn.microsoft.com/psssql/2012/11/12/how-can-reference-counting-be-a-leading-memory-scribbler-cause/

Scope: ?

Trace Flag: 8809

Function: Extended Page Heap Activities. Referenced in passing in the CSS article in relation to debugging memory scribbler problems.

Trace Flag: 8816

Function: Logs every two-digit year conversion to a four-digit year

Link: None

Trace Flag: 8901

Function: Enables new (in 7.0) code to correct a problem with the SHRINK command and empty text or image extents

Link: None

Trace Flag: 8903

Function: Allows SQL Server to use a specific API (SetFileIoOverlappedRange) when Locked Pages in Memory is enabled.

Link: https://blogs.msdn.microsoft.com/psssql/2012/03/20/setfileiooverlappedrange-can-lead-to-unexpected-behavior-for-sql-server-2008-r2-or-sql-server-2012-denali

Link: https://support.microsoft.com/help/2679255/

Link: https://blogs.msdn.microsoft.com/psssql/2013/10/16/every-time-i-attach-database-sql-logs-error-1314-for-setfileiooverlappedrange

Trace Flag: 9024

Function: Converts a global log pool memory object into NUMA node partitioned memory object

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

Link: https://support.microsoft.com/help/2809338

Link: http://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx

Scope: ?

Trace Flag: 9115

Function: PWhite: “Disable prefetch (CUpdUtil::FPrefetchAllowedForDML)” Dima: “Disables both [NLoop Implicit Batch Sort {TF 2340} and NL Prefetching {TF 8744}], and not only on the Post Optimization, but the explicit Sort also”

Link: http://www.hanlincrest.com/SQLServerLockEscalation.htm

Link: http://www.queryprocessor.com/batch-sort-and-nested-loops

Trace Flag: 9130

Function: Disables the particular copy out stage rewrite from Filter + (Scan or Seek) to (Scan or Seek) + Residual Predicate. Enabling this flag retains the Filter in the final execution plan, resulting in a SQL Server 2008+ plan that mirrors the 2005 version.

Link: http://sqlblog.com/blogs/paul_white/archive/2012/10/15/cardinality-estimation-bug-with-lookups-in-sql-server-2008-onward.aspx

Link: http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/12/06/my-new-favourite-traceflag.aspx

Link: http://sqlblog.com/blogs/paul_white/archive/2013/06/11/hello-operator-my-switch-is-bored.aspx

Link: https://connect.microsoft.com/SQLServer/feedback/details/767395/cardinality-estimation-error-with-pushed-predicate-on-a-lookup

Link: http://www.theboreddba.com/Categories/FunWithFlags/Revealing-Predicates-in-Execution-Plans-(TF-9130).aspx

Trace Flag: 9134

Function: SQL 8 - Does additional reads to test if the page is allocated & linked correctly this checks IAM & PFS. Fixes error 601 for queries under Isolation level read uncommitted. In case performance is affected (because of a bug) apply SP4.

Function: Cardinality estimates for literals that are outside the histogram range are very low

Link: None Related to: 9205

Trace Flag: 9204

Function: Output Statistics used by Query Optimizer. When enabled and a plan is compiled or recompiled there is a listing of statistics which is being fully loaded & used to produce cardinality and distribution estimates for some plan alternative or other.

Link: How to Find the Statistics Used to Compile an Execution Plan

Scope: ?

Trace Flag: 9251

Undocumented trace flag

Function: Change in cardinality estimates for some queries. It might only work with the legacy CE. Full effect unknown.

Scope: ?

Trace Flag: 9259

Function: Disables Project Normalization step

Note: Please, don’t use TF 9259 that disables Project Normalization step in a real production system, besides it is undocumented and unsupported, it may hurt your performance.

Link: http://www.queryprocessor.com/sudf-ce/

Trace Flag: 9260

Undocumented trace flag

Function: Adds an explicit sort before creation of an index spool. Almost doesn’t change the total estimated cost. Might be identical plans with just more detail shown at that step.

Scope: ?

Trace Flag: 9268

Function: SQL 8 - When SQL Server runs a parameterized query that contains several IN clauses, each with a large number of values, SQL Server may return the following error message after a minute or more of high CPU utilization: KB 325658. Server: Msg 8623, Level 16, State 1. Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.

Undocumented trace flag

Function: Appears to disable batch mode window aggregates.

Scope: ?

Trace Flag: 9347

Function: Disables batch mode for sort operator. SQL Server 2016 introduces a new batch mode sort operator that boosts performance for many analytical queries.

Link: https://support.microsoft.com/help/3172787

Link: Niko Neugebauer Columnstore Indexes – part 86

Scope: global only

Trace Flag: 9348

Function: Sets a row limit (based on cardinality estimates) that controls whether a bulk insert is attempted or not (assuming conditions are met for a bulk insert). Introduced as a workaround for memory errors encountered with bulk insert.

Link: https://support.microsoft.com/help/2998301/

Trace Flag: 9349

Function: Disables batch mode for top N sort operator. SQL Server 2016 introduces a new batch mode top sort operator that boosts performance for many analytical queries.

Link: Niko Neugebauer Columnstore Indexes – part 86

Link: https://support.microsoft.com/help/3172787

Scope: global or session or query

Trace Flag: 9354

Undocumented trace flag

Function: Disable aggregate pushdown operations for columnstore indexes. The number of rows aggregated at the level of the scan is displayed in the new property plan Actual Number Of Locally Aggregated Rows. TF 9354 can be used to disable the push of aggregation, the difference can be observed by the runtime, according to the number of rows in the plan Actual Number Of Locally Aggregated Rows and number Actual Number Of Rows output from the scan operator.

Example:

use AdventureworksDW2016CTP3;
set nocount on;
go
-- Undocumented TF 9354 disables this optimization, run to see Aggregation Pushdown Performance Gain
set statistics xml, time on;
select count_big(*) from dbo.FactResellerSalesXL_CCI;
select count_big(*) from dbo.FactResellerSalesXL_CCI option(querytraceon 9354); -- undocumented/unsupported TF 9354 to disable aggregate pushdown
set statistics xml, time off;

Trace Flag: 9358

Function: Disable batch mode sort operations in a complex parallel query. For example, this flag could apply if the query contains merge join operations.

Link: Niko Neugebauer Columnstore Indexes – part 86

Link: https://support.microsoft.com/help/3171555

Trace Flag: 9384

Undocumented trace flag

Function: Very slightly changed the memory grant of a query with a batch mode window aggregate.

Scope: ?

Trace Flag: 9389

Function: Enables dynamic memory grant for batch mode operators. If a query does not get all the memory it needs, it spills data to tempdb, incurring additional I/O and potentially impacting query performance. If the dynamic memory grant trace flag is enabled, a batch mode operator may ask for additional memory and avoid spilling to tempdb if additional memory is available.

Link: Niko Neugebauer Columnstore Indexes – part 86

Scope: global or session

Trace Flag: 9390

Undocumented trace flag

Function: Resulted in plan changes including parallelism for queries that shouldn’t have been eligible for parallelism based on CTFP. Full effect unknown.

Link: https://orderbyselectnull.com/2017/12/05/the-referential-integrity-operator/

Scope: ?

Undocumented trace flag

Function: Disables the referential integrity operator.

Trace Flag: 9453

Function: Disables Batch Mode in Parallel Columnstore query plans. (Note that a plan using batch mode appears to require a recompile before the TF takes effect) Sunil Agarwal also used this trace flag in demo scripts for a PASS 2014 session on column store indexing

Link: Niko Neugebauer Columnstore Indexes – part 35

Link: What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 1

Trace Flag: 9471

Function: Causes SQL Server to generate a plan using minimum selectivity for single-table filters, under the query optimizer cardinality estimation model of SQL Server 2014 through SQL Server 2016 versions. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT query hint instead of using this trace flag.

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

Link: New Features in SQL Server 2016 Service Pack 1

Link: https://sqlperformance.com/2014/01/sql-plan/cardinality-estimation-for-multiple-predicates

Scope: global or session or query

Trace Flag: 9472

Function: Assumes independence for multiple WHERE predicates in the SQL 2014 cardinality estimation model. Predicate independence was the default for versions prior to SQL Server 2014, and thus this flag can be used to more closely emulate pre-SQL 2014 cardinality estimate behavior in a more specific fashion than TF 9481.

Link: https://connect.microsoft.com/SQLServer/feedback/details/801908/sql-server-2014-cardinality-estimation-regression

Trace Flag: 9473

Undocumented trace flag

Function: Allowing the outer join to keep a zero-row inner-side estimate (instead of raising to one row) (so all outer rows qualify) gives a 'bug-free' join estimation with either calculator. If you're interested in exploring this, the undocumented trace flag is 9473 (alone).

Link: https://dba.stackexchange.com/a/141533/107045

Scope: ?

Trace Flag: 9474

Undocumented trace flag

Function: Change in cardinality estimates for some joins in certain queries. Full effect unknown.

Scope: ?

Trace Flag: 9476

Function: Causes SQL Server to generate a plan using the Simple Containment assumption instead of the default Base Containment assumption, under the query optimizer cardinality estimation model of SQL Server 2014 through SQL Server 2016 versions. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT query hint instead of using this trace flag. Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.

Link: https://support.microsoft.com/help/3189675

Link: New Features in SQL Server 2016 Service Pack 1

Scope: global or session or query

Trace Flag: 9477

Undocumented trace flag

Function: Slight change in ratio of EstimateRebinds and EstimateRewinds was observed. Full effect unknown.

Scope: ?

Trace Flag: 9478

Undocumented trace flag

Function: Change in cardinality estimates for some joins in certain queries. Full effect unknown.

Scope: ?

Trace Flag: 9479

Undocumented trace flag

Function: Forces the optimizer to use Simple Join [estimation] even if a histogram is available. Will force optimizer to use a simple join estimation algorithm, it may be CSelCalcSimpleJoinWithDistinctCounts, CSelCalcSimpleJoin or CSelCalcSimpleJoinWithUpperBound, depending on the compatibility level and predicate comparison type.

Link: Statistics and Cardinality Estimation

Scope: ?

Trace Flag: 9480

Undocumented trace flag

Function: Reduced the selectivity of a bitmap filter from 0.001 to 0.000001. Full effect unknown.

Link: https://sqlserverscotsman.wordpress.com/2016/11/28/a-guide-on-forcing-the-legacy-ce/

Scope: ?

Trace Flag: 9481

Function: Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. To accomplish this at the database level, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). To accomplish this at the query level, add the QUERYTRACEON query hint

Link: New Features in SQL Server 2016 Service Pack 1

Link: http://www.sqlservergeeks.com/sql-server-2014-trace-flags-9481/

Link: KB2801413

Scope: global or session or query

Function: Disables SELECT permission for DBCC SHOW_STATISTICS

Link: https://support.microsoft.com/help/2683304

Link: http://www.benjaminnevarez.com/2013/02/dbcc-show_statistics-works-with-select-permission

Scope: global only

Trace Flag: 9488

Undocumented trace flag

Function: Implements a “model variation” in the SQL 2014 cardinality estimator. This flag reverts the estimation behavior for multi-statement TVFs back to 1 row (instead of the 100-row estimate behavior that was adopted in SQL 2014).

Link: http://www.queryprocessor.com/ce_mtvf

Trace Flag: 9489

Undocumented trace flag

Function: Implements a “model variation” in the SQL 2014 cardinality estimator and turns off the new logic that handles ascending keys.

Link: http://www.queryprocessor.com/ce_asckey

Trace Flag: 9490

Undocumented trace flag

Function: Change in cardinality estimate. Full effect unknown.

Scope: ?

Trace Flag: 9494

Undocumented trace flag

Function: The behaviour of the join cardinality estimation with CSelCalcExpressionComparedToExpression can also be modified to not account for ``bId` with another undocumented variation flag (9494)

Link: https://dba.stackexchange.com/a/141533/107045

Scope: ?

Trace Flag: 9495

Function: Disables parallelism during insertion for INSERT...SELECT operations and it applies to both user and temporary tables

Link: https://support.microsoft.com/help/3180087

Link: http://connect.microsoft.com/SQLServer/feedback/details/682581/denali-hadron-read-only-routing-url-is-not-yet-implemente

Scope: global or session

Trace Flag: 9532

Function: SQL 11 CTP3 - to get more than 1 availability group replica in CTP3 Scope Startup

Trace Flag: 9559

Undocumented trace flag

Function: For AGs, “when enabled on the secondary ignores the redo target provided from the primary progress message and always set the redo target at the Max LSN value.”

Link: https://blogs.msdn.microsoft.com/alwaysonpro/2013/12/04/recovery-on-secondary-lagging-shared-redo-target

Trace Flag: 9567

Function: Enables compression of the data stream for availability groups during automatic seeding. Compression can significantly reduce the transfer time during automatic seeding and will increase the load on the processor.

Link: https://www.mssqltips.com/sqlservertip/4537/sql-server-2016-availability-group-automatic-seeding/

Link: https://msdn.microsoft.com/en-us/library/mt735149.aspx

Link: Tune compression for availability group

Scope: global or session

Trace Flag: 9576

Function: Revert to the original (SQL Server 2016) implementation of database level health detection using TF 9576 as either a startup parameter or enabled using DBCC TRACEON command. This new implementation is currently only available for SQL Server running on Windows and will be ported to SQL Server 2017 on Linux in an upcoming cumulative update.

Link: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-availability-groups-enhanced-database-level-failover/

Scope: global only

Trace Flag: 9591

Function: Disables log block compression in Always On Availability Groups. Log block compression is the default behavior used with both synchronous and asynchronous replicas in SQL Server 2012 and SQL Server 2014. In SQL Server 2016, compression is only used with asynchronous replica.

Link: http://gsl.azurewebsites.net/Portals/0/Users/dewitt/talks/HekatonWhitePaper.pdf

Scope: global or session

Trace Flag: 9592

Function: Enables log stream compression for synchronous availability groups. This feature is disabled by default on synchronous availability groups because compression adds latency.

Undocumented trace flag

Function: For testing purposes, you might want to turn off automatic merging of files, so that you can more readily explore this metadata. You can do that by turning on the undocumented trace flag 9851. And of course, be sure to turn off the trace flag when done testing.

Trace Flag: 9929

Function: Enables an update that reduces the “disk footprint [of In-Memory OLTP] by reducing the In-Memory checkpoint files to 1 MB (megabytes) each.”

Link: https://support.microsoft.com/help/3147012/

Trace Flag: 9939

Function: Disables merge/recompress during columnstore index reorganization. In SQL Server 2016, when a columnstore index is reorganized, there is new functionality to automatically merge any small compressed rowgroups into larger compressed rowgroups, as well as recompressing any rowgroups that have a large number of deleted rows.

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

Link: https://connect.microsoft.com/SQLServer/feedback/details/795360/secondary-db-gets-suspect-when-i-add-in-memory-table-to-db-which-is-part-of-alwayson-availability-group

Link: Parallelism in Hekaton (In-Memory OLTP) Scope: global or session

Trace Flag: 9989

Function: In CTP2, enabled functionality for reading in-memory tables on a readable secondary

Trace Flag: 10202

Undocumented trace flag

Function: According to demo scripts from a Sunil Agarwal session at PASS 2014, enables a new DMV named sys.dm_db_column_store_row_group_physical_stats. This DMV is not in SQL 2014 RTM and Sunil did not perform this demo during the session, so this DMV appears to be in a future (or internal) version of SQL Server.

Link: None

Trace Flag: 10204

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

Link: https://blogs.msdn.microsoft.com/sqlreleaseservices/partial-results-in-a-query-of-a-clustered-columnstore-index-in-sql-server-2014

Scope: global or session

Trace Flag: 10207

Function: When a Clustered Columnstore index has corrupted segments, turning on this flag suppresses errors 5288 and 5289 and allows a scan of a clustered columns store to skip corrupt segments and complete (though with results that do not include the corrupted segment(s)). This flag is helpful when attempting to copy-out data in a corrupt CCI.

Link: https://support.microsoft.com/help/3067257/

Trace Flag: 10213

Undocumented trace flag

Function: Enables the option to configure compression delay in columnstore indexes in SQL Server 2016

Link: http://www.nikoport.com/2016/02/04/columnstore-indexes-part-76-compression-delay/

Scope: session only

Trace Flag: 10264

Undocumented trace flag

Function: Polybase mode enabled for SqlComposable.

Link: Bad Idea Jeans: Finding Undocumented Trace Flags

Trace Flag: 10316

Function: Enables creation of additional indexes on internal memory-optimized staging temporal table, beside the default one. If you have specific query pattern that includes columns which are not covered by the default index you may consider adding additional ones.

Note: System-versioned temporal tables for Memory-Optimized Tables are designed to provide high transactional throughput. Please be aware that creating additional indexes may introduce overhead for DML operations that update or delete rows in the current table. With the additional indexes you should aim to find the right balance between performance of temporal queries and additional DML overhead.

Link: https://blogs.msdn.microsoft.com/sqlcat/2016/12/08/improve-query-performance-on-memory-optimized-tables-with-temporal-using-new-index-creation-enhancement-in-sp1/

Link: https://support.microsoft.com/help/3198846

Scope: global or session

Trace Flag: 10809

Undocumented trace flag

Function: Force stream Aggregates for scalar aggregation in batch mode.

Scope: ?

Trace Flag: 11001

Undocumented trace flag

Function: Results in a different join order for some queries. Full effect unknown.

Scope: ?

Trace Flag: 11023

Function: Disables the use of the last persisted sample rate, for all subsequent statistics update where a sample rate is not specified explicitly as part of the UPDATE STATISTICS statement.

Link: https://support.microsoft.com/help/4039284

Scope: global or session

Trace Flag: 11024

Function: In Microsoft SQL Server 2017, when incremental statistics are built on the top of partitioned tables, the sum of modification counts of all partitions is stored as the modification count of the root node. When the modification count of the root node exceeds a threshold, the auto update of statistics is triggered. However, if the modification count of any single partition does not exceed the local threshold, the statistics are not updated. Additionally, the modification count of the root node is reset to zero. This may cause delay in the auto update of incremental statistics. When trace flag 11024 is enabled, the modification count of the root node is kept as the sum of modification counts of all partitions.

Note: This trace flag applies to SQL Server 2017 CU3 and higher builds.

Link: https://support.microsoft.com/help/4041811

Scope: global or session

Trace Flag: 11029

Undocumented trace flag

Function: Prevents new information about row goals from getting logged to the plan cache.