Updated: Microsoft SQL Server Trace Flag list

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

Updated 2021-11-14 Updated with TF 147 + some more

Updated 2020-12-16 Updated with TF 146

We have written a lot about Microsoft SQL Server Trace Flags in our blog, so I thought it was about time to start making a list , of known (and unknown) Trace Flags. The goal is of course to create the best SQL Server Trace Flag list in the world. And the most complete SQL Server Trace Flag list in the world.

First some info to get you started:

What are Microsoft SQL Server Trace Flags?

Trace Flags are settings that in some way or another alters the behaviour of various SQL Server functions.

How do I turn Trace Flags on and off?

– You can use the DBCC TRACEON and DBCC TRACEOFF commands

– You can use the -T option in the startup configuration fo the SQL Server Service

-You can also use the hint QUERYTRACEON in your queries: <querytraceon_hint> ::={ QUERYTRACEON trace_flag_number }

How do I know what Trace Flags are turned on at the moment?

– You can use the DBCC TRACESTATUS command

 

REMEMBER: Be extremely careful with trace flags, test in your test environment first. And consult professionals first if you are the slightest uncertain about the effects of your changes.

 

General Links:

SQL Server Central TF List: http://www.sqlservercentral.com/articles/trace+flags/70131/

MSDN TF list: http://sqlserverpedia.com/wiki/Trace_Flags

Albert van der Sel TF list: http://antapex.org/traceflags_sqlserver.txt

Technet Wiki TF list: http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

Amit Banerjee TF list: http://troubleshootingsql.com/2012/07/01/sql-server-2008-trace-flags/

Paul Randal discussing TF Pro’s and Con’s: http://www.sqlskills.com/blogs/paul/the-pros-and-cons-of-trace-flags/

Some trace flags needs to be specified with “t” rather than with “T” in startup options! : http://technet.microsoft.com/en-us/library/ms190737(v=sql.110).aspx

 

Thanks to:

Lars Utterström

Martin Höglund

Håkan Winther

Toine Rozemeijer

Robert L Davis aka @sqlsoldier

sql_handle aka @sql_handle

Andrzej Kukuła

Wilfred van Dijk

Stephen Morris

 

So, here is the list, but it is just the beginning. We will continue to add more flags, and more information as we go along

 

Trace Flag : 101

Function: Verbose Merge Replication logging output for troubleshooting Merger repl performance

Link : http://support.microsoft.com/kb/2892633

 

Trace Flag : 102

Function: Verbose Merge Replication logging to msmerge_history table for troubleshooting Merger repl performance

Link : http://support.microsoft.com/kb/2892633

 

Trace Flag : 105

Function: Join more than 16 tables in SQL server 6.5

Link : http://www.databasejournal.com/features/mssql/article.php/1443351/SQL-Server-65-Some-Useful-Trace-Flags.htm

 

Trace Flag : 106

Function: This enables you to see the messages that are sent to and from the Publisher, if you are using Web Synchronization

Link : http://technet.microsoft.com/en-us/library/ms151872(v=sql.105).aspx

 

Trace Flag : 107

Function: Alter input rules for decimal numbers

Link : http://support.microsoft.com/kb/203787

Thanks to: http://www.sqlservercentral.com

 

Trace Flag : 146

Function: Disable encrypted TDS (To troubleshoot connection issues for example, makes network sniffing easier)

Link : https://docs.microsoft.com/en-us/archive/blogs/bobsql/sql-mysteries-sql-server-login-timeouts-a-debugging-story

 

Trace Flag : 168

Function: Bugfix in ORDER BY

Link : http://support.microsoft.com/kb/926292


Trace Flag : 174

Function: Increase the number of buckets in the SQL Server plan cache for 64 bit systems to avoid heavy contention for the SOS_CACHESTORE

Link: SQL Server Trace flag 174

 

Trace Flag : 205

Function: Log usage of AutoStat/Auto Update Statistics

Link : http://support.microsoft.com/kb/195565

 

Trace Flag : 253

Function: Prevent adhoc query plans from staying in cache (SQL 2005)

Link : http://www.sqlservercentral.com/Forums/Topic837613-146-1.aspx

 

Trace Flag : 260

Function: Prints Extended stord proc DLL versioning info

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 272

Function: Grenerates a log record per identity increment. Can be users to convert SQL 2012 back to old style Indetity behaviour

Link : http://www.big.info/2013/01/how-to-solve-sql-server-2012-identity.html

Link: https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

 

Trace Flag : 302

Function: Output Index Selection info

Link : http://www.databasejournal.com/features/mssql/article.php/1443351/SQL-Server-65-Some-Useful-Trace-Flags.htm

 

Trace Flag : 310

Function: Outputs info about actual join order

Link : http://www.databasejournal.com/features/mssql/article.php/1443351/SQL-Server-65-Some-Useful-Trace-Flags.htm

 

Trace Flag : 323

Function: Outputs detailed info about updates

Link : http://www.databasejournal.com/features/mssql/article.php/1443351/SQL-Server-65-Some-Useful-Trace-Flags.htm

 

Trace Flag : 345

Function: Changes join order selection logic in SQL Server 6.5

Link : http://www.databasejournal.com/features/mssql/article.php/1443351/SQL-Server-65-Some-Useful-Trace-Flags.htm

 

Trace Flag : 445

Function: Prints “compile issued” message in the errorlog for each compiled statement, when used together with 3605

Link: N/A

 

Trace Flag : 460

Function: Changes the “String or binary would be truncated” error message to a much more descriptive one, giving table and column name for the offending data, and includes the actual string. Works in SQL Server 2019 CTP 2.1 and 2.2

Link: https://www.databasejournal.com/features/mssql/new-truncation-error-message-in-sql-server-2019.html

 

Trace Flag : 610

Function: Minimally logged inserts to indexed tables

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

 

Trace Flag : 617

Function: Revert to old style “lock wait list” behaviour in SQL 2012

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

Thanks to: Håkan Winther

 

Trace Flag: 634

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.

 

Trace Flag : 652

Function: Disable page pre-fetching scans

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 661

Function:  Disable the ghost record removal process

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 662

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

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

 

Trace flag: 692

Function: Disables fast inserts while bulk loading data into heap or clustered index. With fast inserts, bulk loads with small batch sizes can lead to increased unused space consumed by objects hence it is recommended to use large batchsize for each batch to fill the extent completely. If increasing batchsize is not feasible, this trace flag can help reduce unused space reserved at the expense of performance.

Scope: Global or Session

 

Trace Flag : 806

Function:  Turn on Page Audit functionality, to verify page validity

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

 

Trace Flag : 818

Function:  Turn on ringbuffer to store info about IO write operations. Used to troubleshoot IO problems

Link : http://support.microsoft.com/kb/826433

 

Trace Flag : 830

Function: Disable diagnostics for stalled and stuck I/O operations

Link : https://support.microsoft.com/sv-se/kb/897284

 

Trace Flag : 834

Function: Large Page Allocations

Link : https://sqlservice.se/sv/start/blogg/nagra-trace-flags-for-sql-server.aspx

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 836 

Function: Use the max server memory option for the buffer pool

Link : http://support.microsoft.com/kb/920093

 

Trace Flag: 715

Function: Enables table lock for bulk load operations into a heap with no nonclustered indexes.

Scope: Global or Session

 

Trace Flag: 818

Function: Enables additional I/O diagnostics to check for Lost Write or Stale Read conditions during file I/O operations.

Applies to : SQL Server 2016 and earlier

Scope: Global or Session

 

Trace Flag : 845

Function: Enable Lock pages in Memory on Standard Edition

Link : https://sqlservice.se/sv/start/blogg/sql-server-performance-with-dynamics-axapta.aspx

 

Trace Flag : 902

Function: Bypass Upgrade Scripts

Link : http://www.sqlservice.se/sql-server-2012-cu1-upgrade-step–msdb110_upgrade-sql–encountered-error-547/

 

Trace Flag : 1117

Function: Simultaneous Autogrowth in Multiple-file database

Link : https://sqlservice.se/sv/start/blogg/nagra-trace-flags-for-sql-server.aspx

Link : http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2012/02/09/sql-server-2008-trace-flag-t-1117.aspx

 

Trace Flag : 1118

Function: Force Uniform Extent Allocation

Link : https://sqlservice.se/sv/start/blogg/nagra-trace-flags-for-sql-server.aspx

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

 

Trace Flag : 1119

Function: Turns of mixed extent allocation (Similar to 1118?)

Link : http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

 

Trace Flag : 1140

Function: Fix for growing tempdb in special cases

Link : http://support.microsoft.com/kb/2000471

 

Trace Flag : 1200

Function: Prints detailed lock information

Link : http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

 

Trace Flag : 1124

Function: Unknown. Has been reportedly found turned on in some SQL Server instances running Dynamics AX. Also rumored to be invalid in public builds of SQL Server

Link: N/A

 

Trace Flag : 1204

Function: Returns info about deadlocks

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 1211

Function: Disables Lock escalation caused by mem pressure

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 1222

Function: Returns Deadlock info in XML format

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 1224

Function: Disables lock escalation based on number of locks

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 1236

Function: Fixes performance problem in scenarios with high lock activity in SQL 2012 and SQL 2014

Link : http://support.microsoft.com/kb/2926217

 

Trace Flag : 1264

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

Link : http://support.microsoft.com/kb/2630458/en-us

 

Trace Flag : 1448

Function: Alters replication logreader functionality

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 1449

Function: Allow FailoverPartner connections to the former Mirror with Database Mirroring being off

Link: https://blogs.msdn.microsoft.com/saponsqlserver/2012/03/29/sql-server-2012-alwayson-part-5-preparing-to-build-an-alwayson-availability-group/#Preconditions
Link: https://blogs.msdn.microsoft.com/saponsqlserver/2007/09/26/what-did-we-learn-using-database-mirroring-over-the-last-two-years-in-our-sap-erp-system-second-revision/

Thanks to: Wilfred van Dijk

 

Trace Flag : 1462

Function: Disable Mirroring Log compression

Link : http://sqlcat.com/sqlcat/b/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx

 

Trace Flag : 1717

Function: MSShipped bit will be set automatically at Create time when creating stored procedures

Link: None

 

Trace Flag : 1800

Function: Performance fix for misaligned IO’s

Link: http://quadtree-ab.com/blog/4590930995/SLOW-RESTORES-when-migrating-SQL-Server-from-old-to-new-hardware/10487597
Link: https://support.microsoft.com/en-us/kb/3009974

 

Trace Flag : 1806

Function: Disable Instant File Initialization

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

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

 

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

 

Trace Flag : 2301

Function: Enable advanced decision support optimizations

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 2312

Function: Forces the query optimizer to use the SQL Server 2014 version of the cardinality estimator when creating the query plan when running SQL Server 2014 with database
compatibility level 110

Link : http://support.microsoft.com/kb/2801413

 

Trace Flag : 2330

Function: Disable collection of missing indexes statistics

Must be enabled at startup. Can help insert performance, and be useful if you have OPT_IDX_STATS spinlock contention

Link : Secret Scale Sauce : Heavy Singleton INSERT spinlocks – Fighting Bad Data Modeling (kejser.org)

 

Trace Flag : 2335

Function: Generates Query Plans optimized for less memory

Link : http://support.microsoft.com/kb/2413549

 

Trace Flag : 2340

Function: Disable specific SORT optimization in Query Plan

Link : http://support.microsoft.com/kb/2009160

 

Trace Flag : 2371

Function: Change threshold for auto update stats

Link : http://www.sqlservice.se/sv/start/blogg/sql-server–auto-update-stats-part-2.aspx

 

Trace Flag : 2372

Function: Displays memory utilization during the optimization process

Link : http://www.benjaminnevarez.com/2012/04/more-undocumented-query-optimizer-trace-flags/

 

Trace Flag : 2373

Function: Displays memory utilization during the optimization process

Link : http://www.benjaminnevarez.com/2012/04/more-undocumented-query-optimizer-trace-flags/

 

Trace Flag : 2388

Function: Change DBCC SHOW_STATISTICS output to show stats history and lead key type such as known ascending keys

Link: http://www.benjaminnevarez.com/2013/02/statistics-on-ascending-keys

 

Trace Flag : 2389

Function: Enable auto-quick-statistics update for known ascending keys

Link : https://sqlservice.se/sv/start/blogg/sql-server-statistics–traceflags-2389–2390.aspx

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

 

Trace Flag : 2390

Function: Enable auto-quick-statistics update for all columns

Link : https://sqlservice.se/sv/start/blogg/sql-server-statistics–traceflags-2389–2390.aspx

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

 

Trace Flag : 2422

Function: When the flag REQUEST_MAX_CPU_TIME_SEC is set in Resource Govenor, this trace flag forces the request to be aborted if it exceeds the max cpu limit, and throws error 10961

SQL Versions: 2016 (Probably) and 2017

Link : https://support.microsoft.com/en-us/help/4038419/add-cpu-timeout-to-resource-governor-request-max-cpu-time-sec

 

Trace Flag : 2430

Function: Fixes performance problem when using large numbers of locks

Link : http://support.microsoft.com/kb/2754301/en-us

 

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.

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

 

Trace Flag : 2470

Function: Fixes performance problem when using AFTER triggers on partitioned tables

Link : http://support.microsoft.com/kb/2606883/en-us

 

Trace Flag : 2505

Function: Suppress DBCC TRACEON/OFF messages in the Error Log

Link: https://support.microsoft.com/en-gb/kb/243352

Thanks to: Håkan Winther

 

Trace Flag : 2514

Function: Verbose Merge Replication logging to msmerge_history table for troubleshooting Merger repl performance

Link : http://sqlblog.com/blogs/argenis_fernandez/archive/2012/05/29/ghost-records-backups-and-database-compression-with-a-pinch-of-security-considerations.aspx

 

Trace Flag : 2528

Function: Disables parallellism in CHECKDB etc.

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 2529

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

Link: N/A

 

Trace Flag : 2537

Function: Allows you to see inactive records in transactionlog using fn_dblog

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

 

Trace Flag : 2540

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2541

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2542

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2543

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2544

Function: Produces a full memory dump

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2545

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2546

Function: Dumps all threads for SQL Server in the dump file

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2547

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

Link : http://support.microsoft.com/kb/917825/en-us

 

 

Trace Flag : 2548

Function: Shrink will run faster with this trace flag if there are LOB pages that need conversion and/or compaction, because that actions will be skipped.

Link : http://blogs.msdn.com/b/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx

Thanks to: Andrzej Kukula

 

 

Trace Flag : 2549

Function: Faster CHECKDB

Link: https://sqlservice.se/sv/start/blogg/faster-dbcc-checkdb-by-using-trace-flag-2562-and-2549.aspx

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

Link : http://support.microsoft.com/kb/2634571

Link: http://support.microsoft.com/kb/2732669/en-us

 

Trace Flag : 2550

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2551

Function: Produces a filtered memory dump

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2552

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2553

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2554

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2555

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2556

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2557

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2558

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2559

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 2562

Function: Faster CHECKDB

Link: https://sqlservice.se/sv/start/blogg/faster-dbcc-checkdb-by-using-trace-flag-2562-and-2549.aspx

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

Link : http://support.microsoft.com/kb/2634571

Link: http://support.microsoft.com/kb/2732669/en-us

 

 

Trace Flag : 2588

Function: Get more information about undocumented DBCC commands

Link : https://sqlservice.se/sv/start/blogg/trace-flag–undocumented-commands.aspx

 

Trace Flag : 2861

Function: Keep zero cost plans in cache

Link : http://support.microsoft.com/kb/325607

 

 

Trace Flag : 3004

Function: Returns more info about Instant File Initialization

Link : http://victorisakov.files.wordpress.com/2011/10/sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf

 

 

Trace Flag : 3014

Function: Returns more info about backups to the errorlog

Link : http://victorisakov.files.wordpress.com/2011/10/sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf

 

Trace Flag : 3023

Function: Enable the CHECKSUM option if backup utilities do not expose the option

Link : https://support.microsoft.com/en-us/kb/2656988

Thanks to: Wilfred van Dijk

 

Trace Flag : 3042

Function: Alters backup compression functionality

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 3101

Function: Fix performance problems when restoring database with CDC

Link : http://support.microsoft.com/kb/2567366/en-us

 

Trace Flag : 3205

Function: Disable HW compression for backup to tape drives

Link :  http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 3213

Function: Output buffer info for backups to ERRORLOG

Link : http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

 

Trace Flag : 3226

Function: Turns off “Backup Successful” messages in errorlog

Link :  http://msdn.microsoft.com/en-us/library/ms188396.aspx

Thanks to: @lwiederstein /https://twitter.com/lwiederstein)

 

 

Trace Flag : 3422

Function: Log record auditing

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

 

Trace Flag : 3459

Function: Disable parallell REDO in Always On Availability Groups

Link : https://blog.sqlauthority.com/2018/05/24/sql-server-parallel-redo-on-alwayson-secondary-dirty_page_table_lock/

 

Trace Flag : 3502

Function: Writes info about checkpoints to the errorlog

Link : http://victorisakov.files.wordpress.com/2011/10/sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf

 

Trace Flag : 3504

Function: Writes info about long checkpoints to the errorlog. Long checkpoints are when the chekpoint exceeds the configured recovery interval. Is only needed prior to SQL 2012, as this functionality is enabled by default after that.

Link : https://blogs.msdn.microsoft.com/psssql/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log/

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

 

Trace Flag : 3505

Function: Disables automatic checkpointing

Link : http://support.microsoft.com/kb/815436

 

Trace Flag : 3604

Function: Redirect DBCC command output to query window

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

Link: https://sqlservice.se/sv/start/blogg/querytraceon.aspx

 

Trace Flag : 3605

Function: Directs the output of some Trace Flags to the Errorlog

Link : http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

 

Trace Flag : 3607

Function: Skip recovery on startup

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

 

 

Trace Flag : 3608

Function: Recover only Master db at startup

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

 

Trace Flag : 3609

Function: Do not create tempdb at startup

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

 

 

Trace Flag : 3625

Function: Masks some errormessages

Link :  http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 3656

Function: Enables resolve of all callstacks in extended events

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

 

Trace Flag : 3659

Function:  Enables logging all errors to errorlog during server startup

Link : http://spaghettidba.com/2011/05/20/trace-flag-3659/

 

Trace Flag : 3688

Function: Removes messages to errorlog about traces started and stopped

Link : http://support.microsoft.com/kb/922578/en-us

 

Trace Flag : 3801

Function: Prohibits use of USE DB statement

Link : None

Trace Flag : 3923

Function: Let SQL Server throw an exception to the application when the 3303 warning message is raised.

Link : https://support.microsoft.com/kb/3014867/en-us

Trace Flag : 3924

Function: Clean up orphaned DTC transactions. Originally introduced in KB 3145492 which seems to have been removed. The fix have been fixed several times, see more info in the link below. Applies to SQL Server 2016, 2017 and 2019

Link : https://support.microsoft.com/en-us/help/4519668/fix-access-violation-occurs-when-you-enable-tf-3924-to-clean-orphaned

Trace Flag : 4013

Function: Log each new connection the errorlog

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

 

 

Trace Flag : 4022

Function: Bypass Startup procedures

Link : https://sqlservice.se/sv/start/blogg/sql-server-2012-cu1-upgrade-step–msdb110_upgrade-sql–encountered-error-547.aspx

 

 

Trace Flag : 4130

Function: XML performance fix

Link : http://support.microsoft.com/kb/957205

 

Trace Flag : 4134

Function: Bugfix for error: parallell query returning different results every time

Link : http://support.microsoft.com/kb/2546901

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

 

Trace Flag : 4135

Function: Bugfix for error inserting to temp table

Link : http://support.microsoft.com/kb/960770

 

Trace Flag : 4136

Function: Parameter Sniffing behaviour alteration

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

Link : https://sqlservice.se/sv/start/blogg/nagra-trace-flags-for-sql-server.aspx

 

Trace Flag : 4137

Function: Fix for bad performance in queries with several AND criteria

Link : http://support.microsoft.com/kb/2658214

 

Trace Flag : 4138

Function: Fixes performance probles with certain queries that use TOP statement

Link : http://support.microsoft.com/kb/2667211

 

Trace Flag : 4139

Function: Fix for poor cardinality estimation when the ascending key column is branded as stationary

Link : https://support.microsoft.com/en-us/kb/2952101

 

Trace Flag : 4199

Function: Turn on all optimizations

Link : https://sqlservice.se/sv/start/blogg/one-trace-flag-to-rule-them-all.aspx

 

Trace Flag : 4606

Function: Ignore domain policy about weak password

Link : None

 

Trace Flag : 4616

Function: Alters server-level metadata visibility

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 6498

Function: Increased query compilation scalability in SQL Server 2014

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

 

Trace Flag : 6527

Function: Alters mem dump functionality

Link :  http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 6534

Function: This fix updates the sorting algorithm to include angular vectorization techniques that significantly improve the LineString performance

Link : https://support.microsoft.com/en-us/kb/3054180

 

Trace Flag : 6545

Function: This traceflag enables CLR strict security, it can only be activated as a startup parameter

Link : https://support.microsoft.com/en-us/help/4018930/update-adds-the-clr-strict-security-feature-to-sql-server-2016

 

Trace Flag : 6559

Function: FIX: Orphaned CLR sessions cause blocking in SQL Server. Must be enabled in startup, can not be enabled using DBCC TRACEON

Link : https://support.microsoft.com/en-us/help/4517771/fix-orphaned-clr-sessions-cause-blocking-in-sql-server

 

Trace Flag : 7300

Function: Outputs extra info about linked server errors

Link :  http://support.microsoft.com/kb/314530

 

Trace Flag : 7412

Function: Enables the lightweight query execution statistics profiling infrastructure. A new Extended Event query_thread_profile was added in SQL Server 2014 SP2, to get actual execution info in a more lightweight way than getting the entire actual execution plan

Link :  https://support.microsoft.com/en-us/help/3170113/update-to-expose-per-operator-query-execution-statistics-in-showplan-x

 

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/en-us/kb/3088480

 

Trace Flag : 7502

Function: Disable cursor plan caching for extended stored procedures

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

 

Trace Flag : 7745

Function: Disable writing of in-memory Query Store data to disk during SQL Server shutdown. Migth cause loss of Query Store data. Can be desirable for fast failovers in AG/Clusters for example

Link :  https://www.sqlskills.com/blogs/erin/query-store-trace-flags/

Link: https://blogs.msdn.microsoft.com/vikas_rana/2017/10/20/query-store-best-practices-for-mission-critical-or-busy-sql-server/

 

Trace Flag : 7752

Function: Load Query Store data anyncronously at SQL Server startup. Lets you get faster to the point where you can start executin queries, but Query Store will be in read only mode until all it’s data is loaded. So Query Store might miss some queries at the very beginning of the startup. Will also potentionally give you faster failovers of your Availability Group/Cluster

Link :  https://www.sqlskills.com/blogs/erin/query-store-trace-flags/

Link: https://blogs.msdn.microsoft.com/vikas_rana/2017/10/20/query-store-best-practices-for-mission-critical-or-busy-sql-server/

 

Trace Flag : 7806

Function: Enables DAC on SQL Server Express

Link :  http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

 

Trace Flag : 7826

Function: Disable Connectivity ringbuffer

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

Function: Changes CPU Affinity behaviour

Link : http://support.microsoft.com/kb/818769

 

Trace Flag : 8010

Function: Fixes problem that SQL Server services can not be stopped

Link : http://support.microsoft.com/kb/2633271/en-us

 

Trace Flag : 8011

Function: Disable the ring buffer for Resource Monitor

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 8012

Function: Disable the ring buffer for schedulers

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 8015

Function: Ignore NUMA functionality

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

Thanks to: @sql_handle (https://twitter.com/sql_handle)

 

Trace Flag : 8017

Function: Controls whether SQL Server creates schedulers for all logical processors, including those that are not available for SQL Server to use (according to the affinity mask)

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

 

Trace Flag : 8018

Function: Disable the exception ring buffer

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 8019

Function: Disable stack collection for the exception ring buffer

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 8020

Function: Disable working set monitoring

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 8026

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

Link : http://support.microsoft.com/kb/917825/en-us

 

Trace Flag : 8030

Function: Fix for performance bug

Link : http://support.microsoft.com/kb/917035

Link : https://sqlservice.se/sv/start/blogg/sql-server-2005-slowing-down-after-a-while.aspx

 

Trace Flag : 8032

Function: Alters cache limit settings

Link :  http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 8038

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

Link : http://forum.proxmox.com/threads/15844-Win7-x64-guest-with-SQLServer-2012-High-CPU-usage
Link : http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

 

Trace Flag : 8040

Function: Disables Resource Govenor

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

 

Trace Flag : 8048

Function: NUMA CPU based partitioning

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

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

Thanks to: @sql_handle (https://twitter.com/sql_handle)

Related to: 8015, 9024

 

Trace Flag : 8099

Function: Fix for severe spinlock contention in SQL Server 2019. Introduced in SQL 2019 CU2

Link : https://support.microsoft.com/en-us/help/4538688/fix-severe-spinlock-contention-occurs-in-sql-server-2019

 

Trace Flag : 8207

Function: Alters Transactional Replication behaviour of UPDATE statement

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 8209

Function: Output extra infomation to errorlog regarding replication of schema changes in SQL Server Replication

Link : http://support.microsoft.com/kb/916706/en-us

 

Trace Flag : 8218

Function: Bypass proc generation (Unclear what proc’s…) Referenced in the system procedure [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://social.msdn.microsoft.com/forums/sqlserver/en-US/00250311-7991-47b0-b788-7fae2e102254/trace-flag-8295

Thanks to: Wilfred van Dijk

 

Trace Flag : 8602

Function: Disable Query Hints

Link : http://www.sqlservice.se/sv/start/blogg/sql-server-trace-flag-8602.aspx

 

Trace Flag : 8605

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

Link : http://www.benjaminnevarez.com/2012/04/more-undocumented-query-optimizer-trace-flags/

 

Trace Flag : 8607

Function: Displays the optimization output tree during the optimization process

Link : http://www.benjaminnevarez.com/2012/04/more-undocumented-query-optimizer-trace-flags/

 

Trace Flag : 8649

Function: Set Cost Threshold for parallelism to 0

Link : http://www.sqlservice.se/sv/start/blogg/enable-parallellism-for-specific-query.aspx

 

Trace Flag : 8675

Function: Displays the query optimization phases for a specific optimization

Link : http://www.benjaminnevarez.com/2012/04/more-undocumented-query-optimizer-trace-flags/

 

Trace Flag : 8722

Function: Disable all hints exept locking hints

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

 

Trace Flag : 8744

Function: Disable pre-fetching for ranges

Link : http://support.microsoft.com/kb/920093

 

Trace Flag : 8755

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 : http://www.benjaminnevarez.com/2012/04/more-undocumented-query-optimizer-trace-flags/

 

Trace Flag : 8780

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

Link : https://sqlservice.se/sv/start/blogg/sql-server-trace-flag–8780.aspx

 

 

Trace Flag : 9185

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

Link : https://support.microsoft.com/en-us/kb/kbview/833406

Related to: 9205

 

Trace Flag : 9024

Function: Performance fix for AlwaysON log replication

Link : http://support.microsoft.com/kb/2809338/en-us

Related to: 8048

 

Trace Flag : 9204

Function: Output Statistics used by Query Optimizer

Link : http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx

Related to: 9292

 

Trace Flag : 9205

Function: Cardinality estimates for literals that are outside the histogram range are very low for tables that have parent-child relationships

Link : https://support.microsoft.com/en-us/kb/kbview/833406

Related to: 9185

 

Trace Flag : 9207

Function: Fixes that SQL Server underestimates the cardinality of a query expression and query performance may be slow

Link : https://support.microsoft.com/en-us/kb/831302

 

Trace Flag : 9292

Function: Output Statistics considered to be used by Query Optimizer

Link : http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx

Related to: 9204

 

Trace Flag : 9347

Function: Disables batch mode sort operator for Clustered Columnstore Index.

Link: https://support.microsoft.com/en-nz/kb/3172787
Link: http://www.nikoport.com/2016/07/29/columnstore-indexes-part-86-new-trace-flags-in-sql-server-2016/

 

Trace Flag : 9349

Function: Disables batch mode top sort operator. SQL Server 2016 Release Candidate (RC0) introduces a new batch mode top sort operator that boosts performance for many analytical queries.

Link: https://msdn.microsoft.com/en-us/library/ms188396.aspx
Link: http://www.nikoport.com/2016/07/29/columnstore-indexes-part-86-new-trace-flags-in-sql-server-2016/

 

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: https://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 9481

Function: Forces the query optimizer to use the SQL Server 2012 version of the cardinality estimator when creating the query plan when running SQL Server 2014 with the
default database compatibility level 120

Link : http://support.microsoft.com/kb/2801413

 

 

Trace Flag : 9485

Function: Disables SELECT permission for DBCC SHOW_STATISTICS.

Link : http://msdn.microsoft.com/en-us/library/ms188396.aspx

 

Trace Flag : 9567

Function: Enable on the AlwaysOn primary replica to enable compression of the data stream that gets send from the primary to the secondary during Direct Seeding.

Link: https://blogs.msdn.microsoft.com/alwaysonpro/2016/05/02/sql-server-2016-alwayson-availability-group-enhancements-initial-data-synchronization-without-database-and-log-backup/
Link: https://blogs.msdn.microsoft.com/saponsqlserver/2016/05/02/sql-server-2016-alwayson-for-sap/

Thanks to: Wilfred van Dijk

 

Trace Flag : 9806

Function: Unknown. Is turned on on SQL Server 2014 CTP1 standard installation in Windows Azure VM

Link : None!

 

Trace Flag : 9807

Function: Unknown. Is turned on on SQL Server 2014 CTP1 standard installation in Windows Azure VM

Link : None!

 

Trace Flag : 9808

Function: Unknown. Is turned on on SQL Server 2014 CTP1 standard installation in Windows Azure VM

Link : None!

 

Trace Flag : 10204

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.

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

 

Trace Flag : 10210

Function: Enables the option to configure compression delay in columnstore indexes in SQL Server 2016. It is unclear if it is only for the CTP versions, or if it is still required in the RTM product.

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

Link: https://msdn.microsoft.com/en-us/library/gg492153.aspx?f=255&MSPPError=-2147217396

Link: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational-analytics-compression-delay-option-for-nonclustered-columnstore-index-ncci/

Thanks to: Wilfred van Dijk

 

Trace Flag : 11024

Function: Fixes behavior in Auto Update of Incremental Statistics. In an partitioned table, enabling this flag keeps modification count of the root node is kept as the sum of modification counts of all partitions even after the auto update is triggered.

SQL versions: 2016, 2017

Link: https://support.microsoft.com/en-us/help/4041811/automatic-update-of-incremental-statistics-is-delayed-in-sql-server

 

Läs liknande inlägg