Geekly Articles each Day
📜 ⬆️ ⬇️

Full list of Microsoft SQL Server trace flags

Microsoft SQL Server Trace Flags


A complete list of Microsoft SQL Server trace flags (359 current trace flags).


REMEMBER: Be extremely careful with the trace flags, check their effect first in the test environment.


A github version in markdown with periodic updates in English is available at the link: SQL Server Trace Flag


Updates


Update 2016-12-12 : 15 trace flags added, information on more than 40 flags updated
Update 2016-11-04 : 26 trace flags added, thank you very much @EzhovEA
Update 2016-09-09 : added trace flag 9851 , thanks @JeStoneDev
Update 2016-08-11 : the trace flag 6533 is added


Also, you might be interested in other articles on Microsoft SQL Server:



Headlines:



Sources:



Thank you very much:



What is the trace flag in Microsoft SQL Server?


Tracing flags are used to temporarily set certain server characteristics or disable a specific mode.
Trace flags are often used to diagnose performance problems, debug stored procedures, or complex computer systems.
MSDN: https://msdn.microsoft.com/ru-ru/library/ms188396(v=sql.120).aspx


How to enable or disable tracing flags?



How to determine which trace flags are currently on?


You can use DBCC TRACESTATUS command


The following example shows the status of all trace flags that are globally enabled for the current moment:


DBCC TRACESTATUS(-1); GO

The following example shows the status of trace flags 2528 and 3205:


 DBCC TRACESTATUS (2528, 3205); GO

The following example shows for trace flag 3205 whether it is globally involved or not:


 DBCC TRACESTATUS (3205, -1); GO

The following example shows a list of all trace flags enabled for the current session:


 DBCC TRACESTATUS (); GO

What are Optimizer rules?


Every time a SQL Server executes a query, it builds an execution plan that translates logical operations, such as joins and predicates, into physical operations that are implemented in the source code of SQL Server.
This conversion is based on certain rules known as optimizer rules. They define, for example, how to make an internal connection.
When we write a simple select statement from an inner join, the query optimizer chooses, based on statistics, indexes, and the rules involved, how the join is made (like Merge Join, nested loop or hashing), and whether the join can use the commutative property of joins.
Mathematically, the connection of A and B is equivalent to the connection of B and A, but the computational costs, as a rule, can differ significantly.


List of all available rules


In order to get a list of rules in your version of SQL Server, you must use the undocumented DBCC commands SHOWONRULES and SHOWOFFRULES .
These commands display enabled and disabled rules for the entire server. The number of rules varies among versions.


 USE master; GO DBCC TRACEON(3604); GO DBCC SHOWONRULES; GO DBCC SHOWOFFRULES; GO

RuleDescription
JNtoNLJoin to Nested Loop
JntohsJoin to Hash
JNtoSMJoin to Sort Merge
LOJNtoNLLeft Outer Join to Nested Loop
LSJNtoHSLeft Semi Join to Hash
LASJNtoSMLeft Anti Semi Join to Sort Merge

List of trace flags


Total: 359 trace flags


List of trace flags


Total: 359 trace flags


Trace flag: -1
Purpose: Sets trace flags for all client connections, rather than for a single client connection. Because trace flags are set, it can be used when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.
Link: http://www.sql-server-performance.com/2002/traceflags/


Trace Flag: 101
Purpose: Verbose Merge Replication logging output for troubleshooting
Merger repl performance
Link: http://support.microsoft.com/kb/2892633


Trace flag: 102
Purpose: Verbose Merge Replication logging to msmerge_history table
for troubleshooting merger repl performance
Link: http://support.microsoft.com/kb/2892633


Trace Flag: 105
Purpose: 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
Purpose: If you are using Web Synchronization
Link: http://technet.microsoft.com/en-us/library/ms151872(v=sql.105).aspx


Trace Flag: 107
Purpose: SQL 6.5 / 7/8 - Interprets numbers with a float instead of decimal
Link: http://support.microsoft.com/kb/203787
Link: https://support.microsoft.com/en-us/kb/155714


Trace flag: 110
Purpose: SQL 6.5 - Turns off ANSI select characteristics
Link: https://support.microsoft.com/en-us/kb/152032


Trace flag: 144
Purpose: Force server side bucketization.
It is not an option and
When this application has been asked for that parameter, this flag flag forces server side bucketization.
Link: http://blogs.msdn.microsoft.com/sqlprogrammability/2007/01/13/6-0-best-programming-practices


Trace Flag: 146
Purpose: Consider using SQL 8.0 when it is replaying against SQL 8.0.
Link: No


Trace Flag: 168
Purpose: Bugfix in ORDER BY
Link: http://support.microsoft.com/kb/926292


Trace Flag: 174
Purpose: Increases the SQL Server database engine plan cache from 40.009 to 160.001 on 64-bit systems.
Note: Please ensure that this is a rolling environment.
Link: https://support.microsoft.com/en-us/kb/3026083
Reference: MSDN ms188396
Method of use: global only


Trace Flag: 204
Purpose: SQL 6.5 - Backward compatibility switch that enables non-ansi standard behavior. Previously, the SQL server ignored the list.
Link: No


Trace Flag: 205
Purpose: Reports for the error log when a statistics-dependent stored procedure is being recompiled.
Link: https://support.microsoft.com/en-us/kb/195565
Reference: MSDN ms188396
Method of use: global only


Trace Flag: 206
Purpose: SQL 6.5 - Provides backward compatibility for the set user statement. KB 160732
Link: No


Trace Flag: 208
Purpose: SET QUOTED IDENTIFIER ON
Link: No


Trace flag: 210
Purpose: SQL 9 - Error when you run a query against a view: "An error occurred while executing batch"
Link: https://support.microsoft.com/en-us/kb/945892


Trace Flag: 212
Assignment: SQL 9 - Query may run much slower when compared to SQL 8
Link: https://support.microsoft.com/en-us/kb/951184


Trace Flag: 237
Purpose: Tells SQL Server for correlated sub-queries in non-ANSI standard backward compatibility mode
Link: No


Trace Flag: 242
Purpose: Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired
Link: No


Trace Flag: 243
Purpose: Provides backward compatibility for nullability behavior. When set, SQL Server has the same nullability violation (it can be detected in the NOT NULL field); It is a rule that there is a number of times when it comes to the command line. transaction process.
Link: No


Trace Flag: 244
Purpose: Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. The interim constraint is a constraint. SQL Server checks for interim constraints for self-referencing DELETE statements, INSERT, and multi-row UPDATE statements. This checking requires more work tables. In this case, you can disallow interim constraint violations, thus reducing the number of work tables.
Link: No


Trace flag: 246
Purpose: it raises an error. This flag avoids that.
Link: No


Trace Flag: 253
Purpose: Prevents ad-hoc query plans to stay in cache
Link: http://www.sqlservercentral.com/Forums/Topic837613-146-1.aspx


Trace Flag: 257
Purpose: Will the XML output the result?
Link: No


Trace Flag: 260
Purpose: Prints versioning information about the extended stored procedure dynamic-link libraries (DLLs).
For more information about GetXpVersion (), see Creating Extended Stored Procedures .
Link: http://msdn.microsoft.com/en-us/library/ms164627.aspx
Reference: MSDN ms188396
Method of use: global or session


Trace Flag: 262
Purpose: SQL 7 - Trailing spaces are no longer truncated from literal strings in CASE statements
Link: https://support.microsoft.com/en-us/kb/891116


Trace Flag: 272
Purpose: Generates a log record per identity increment. Can be users
to convert SQL 2012 back to old style
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
Purpose: 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
Purpose: 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: 320
Purpose: Disables join-order heuristics used in ANSI joins. To see the join-order heuristics use flag 310. SQL Server use the join join order.
Link: No


Trace Flag: 323
Purpose: 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: 325
Purpose: ORDER BY clause
Link: No


Trace Flag: 326
Purpose: Prints information about estimated & actual costs of sorts. We recommend you to use statistics and statistics when calculating density. Useful for building building If you’re registering for a table or index, you can use skewed data.
Link: No


Trace flag: 330
Purpose: Enables full output when using the SHOWPLAN_ALL option, which gives detailed information about joins
Link: No


Trace Flag: 342
Purpose: Disables the cost of pseudo-merge joins, thus significantly reducing the amount of large-scale, multi-table joins. It can be used to make it possible to use the clause.
Link: No


Trace Flag: 345
Purpose: 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
Purpose: Prints "compile issued" message for the compiled statement, when used together with 3605
Link: No


Trace Flag: 506
Purpose: Enforces SQL-92 standards values ​​for comparisons between variables and parameters. Any results always contain a NULL.
Link: No


Trace Flag: 610
Purpose: Controls minimally logged inserts into indexed tables.
Reference: 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
Reference: MSDN ms188396
Method of use: global or session


Trace flag: 611
Assignment: SQL 9 - When it comes to 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: No


Trace flag: 617
Assignment: SQL 9 - When it comes to the SQL Server handle number.
As long as there is no lock request
lockout list will be bypassed by statements statement in uncommitted read transaction isolation level.
If there is a SCH_M lock request in the 'lock wait list'
will not be allowed to go through the lockout list.
In order behind the lock list. As a result of the request for such a request
SCH_M lock request is not included.
Link: https://blogs.msdn.microsoft.com/saponsqlserver/2014/01/17/new-functionality-in-sql-server-2014-part-3-low-priority-wait/


Trace Flag: 634
Purpose: Disables the background columnstore compression task. SQL Server periodically runs the background task for the columnstore index rowgroups with uncompressed data, one such rowgroup at a time.
But not the system resources.
TRAINEX RELAXANIZE or ALTER INDEX at a time of your choice.
Reference: Niko Neugebauer Columnstore Indexes - part 35
Reference: MSDN ms188396
Method of use: global only


Trace Flag: 646
Purpose: Serves for getting detailed information on which columns were eliminated by the Query Optimizer right into the error log.
Reference: Niko Neugebauer Columnstore Indexes - part 35


Trace Flag: 652
Purpose: Disable page pre-fetching scans
Reference: KB920093 ]
Reference: MSDN ms188396
Method of use: global only


Trace Flag: 653
Purpose: Disables read ahead for the current connection
Link: No


Trace flag: 661
Purpose: Disables the ghost record removal process.
Reference: KB920093
Reference: MSDN ms188396
Method of use: global only


Trace Flag: 662
Purpose: Prints detailed information
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: 698
Assignment: SQL 9 - Comparison of SQL 8
Link: https://support.microsoft.com/en-gb/kb/940545


Trace flag: 699
Purpose: Turn off transaction logging for the entire SQL dataserver
Link: No


Trace flag: 715
Purpose: Enables table lock for non-clustered indexes.
When this trace flag is used, it is enabled;
Bulk update (BU) locks up to the same table.
It is a hindrance that allows you to access the table.
However, this trace flag is enabled, this behavior becomes the database changes.
Reference: MSDN ms188396
Method of use: global or session


Trace flag: 806
Purpose: Turn on Page Audit functionality, to verify page validity
Link: http://technet.microsoft.com/en-au/library/cc917726.aspx


Trace Flag: 809
Purpose: SQL 8 - Limits the amount of Lazy write activity
Link: No


Trace flag: 815
Purpose: SQL 8/9 - Enables latch enforcement. SQL Server 8 (with service pack 4) and SQL Server 9. If the database is updated, it should be noted that it has been updated. It is a mini-dump in the SQL Server installation of the LOGO directory. Microsoft support can verify the contents of these mini-dumps to determine the cause of the exception. In this case, you need to modify the latch. Once it has been modified, it has been changed to read-write. Once the modification has been released, it is released.
Link: No


Trace flag: 818
Purpose: Turn on ringbuffer to store info about IO write operations.
Used to troubleshoot IO problems
Link: http://support.microsoft.com/kb/826433


Trace flag: 828
Assignment: SQL 8 - Check that will be taken.
Link: https://support.microsoft.com/en-gb/kb/906121


Trace flag: 830
Assignment: SQL 9 - Disabled by SQL Server I have encountered problems for more than 15 seconds to complete
Link: https://support.microsoft.com/en-us/kb/897284


Trace Flag: 831
Purpose: Protect unchanged pages corruptions
Link: No


Trace Flag: 834
Purpose: Uses Microsoft Windows large-page allocations for the buffer pool.
Note: If you are using the SQL Server 2012 to SQL Server 2016, you can use the flag 834.
Link: http://www.sqlservice.se/sv/start/blogg/nagra-trace-flags-for-sql-server.aspx
Link: https://support.microsoft.com/en-us/kb/920093
Link: https://support.microsoft.com/en-us/kb/3210239
Reference: MSDN ms188396
Method of use: global only


Trace flag: 835
Purpose: SQL 9/10 - On 64 bit
Link: No


Trace flag: 836
Purpose: Use the max memory server option
Reference: KB920093


Trace flag: 840
Assignment: SQL Server 9 “When you turn back to the pool” when SQL Server starts this buffer pool. Additionally, the SQL Server starts.
Link: https://support.microsoft.com/en-gb/kb/912322


Trace flag: 842
Purpose: Use sys.dm_os_memory_node_access_stats to verify local vs. turning on this flag
Link: No


Trace Flag: 845
Purpose: Enable Lock pages in Memory on Standard Edition
Link: http://www.sqlservice.se/sv/start/blogg/sql-server-performance-with-dynamics-axapta.aspx
Link: https://support.microsoft.com/en-gb/kb/970070


Trace Flag: 902
Purpose: Bypasses the Cumulative Update or Service Pack.
Microsoft SQL Customer Service and Support (CSS) for further guidance.
Warning: This trace flag is not always supported.
in a production environment. Cumulative Updates and Service Packs.
SQL Server instance.
Link: http://www.sqlservice.se/sv/start/blogg/sql-server-2012-cu1-upgrade-step--msdb110_upgrade-sql--encountered-error-547.aspx
Link: https://support.microsoft.com/en-us/kb/2163980
Reference: MSDN ms188396
Method of use: global only


Trace flag: 1106
Use: SQL 9 - Used space in tempdb.
Link: https://support.microsoft.com/en-gb/kb/947204


Trace flag: 1117
Purpose: When a file is in the filegroup, it meets the filegroup grow.br />
Note: Beginning with SQL Server 2016 this behavior is controlled by the AUTOGROW_SINGLE_FILE and the AUTOGROW_ALL_FILES option of the ALTER DATABASE, and the trace flag 1117 has no affect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).
Link: http://www.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
Reference: MSDN ms188396
Method of use: global only


Trace flag: 1118
Purpose: Removes the congestion on the SGAM page.
If you’re a new object, it’s created by each other.
Afterwards, those are allocated from those same pages.
It is a bottleneck when it comes to the page.
This trace flag allocates the SGAM page.
Note: Beginning with SQL Server 2016 This flag 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://www.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
: http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/
: https://support.microsoft.com/en-us/kb/328551
: MSDN ms188396
: global only


: 1119
: Turns of mixed extent allocation (Similar to 1118?)
: TECHNET List Of SQL Server Trace Flags


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


: 1140
: Fix for growing tempdb in special cases
: http://support.microsoft.com/kb/2000471


: 1180
: SQL 7 — Forces allocation to use free pages for text or image data and maintain efficiency of storage. Helpful in case when DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns.
:


: 1197
: Applies only in the case of SQL 7 – SP3, similar with trace flag 1180
:


: 1200
: Prints detailed lock information as every request for a lock is made (the process ID and type of lock requested)
: TECHNET List Of SQL Server Trace Flags


: 1202
: Insert blocked lock requests into syslocks
:


: 1204
: Returns the resources and types of locks participating in a deadlock and also the current command affected.
: https://support.microsoft.com/en-us/kb/832524
: MSDN ms188396
: global only


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


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


: 1211
: 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.
: MSDN ms188396
: global or session


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


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


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



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.
: Lock escalation to the table- or HoBT-level granularity can also be controlled by using the LOCK_ESCALATION option of the ALTER TABLE statement.
: MSDN ms188396
: global or session


: 1228
: 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.
: Trace Flag 1228 and 1229
: Microsoft SQL Server 2005 TPC-C Trace Flags


: 1229
: 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.
: Trace Flag 1228 and 1229
: Microsoft SQL Server 2005 TPC-C Trace Flags


: 1236
: Enables database lock partitioning. Fixes performance problem in scenarios with high lock activity in SQL 2012 and SQL 2014.
: 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.
: https://support.microsoft.com/en-us/kb/2926217
: MSDN ms188396
: global only


: 1260
: 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."
: A Topical Collection of SQL Server Flags v6
: How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888
: MSDN ms188396
: global only


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


: 1262
: 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.
: A Topical Collection of SQL Server Flags v6
: How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888


: 1264
: Collect process names in non-yielding scenario memory dumps
: A Topical Collection of SQL Server Flags v6
: http://support.microsoft.com/kb/2630458/en-us


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


: 1439
: Trace database restart and failover messages to SQL Errorlog for mirrored databases
: Trace flags in sql server from trace flag 902 to trace flag 1462


: 1448
: 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.
: https://support.microsoft.com/en-us/kb/937041
: MSDN ms188396
: global only


: 1449
: 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".
: https://support.microsoft.com/en-gb/kb/936179


: 1462
: Disables log stream compression for asynchronous availability groups.
This feature is enabled by default on asynchronous availability groups in order to optimize network bandwidth.
: http://sqlcat.com/sqlcat/b/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx
: MSDN mt736907
: MSDN ms188396
: global only


: 1504
: Dynamic memory grant expansion can also help with parallel index build plans where the distribution of rows across threads is uneven.
The amount of memory that can be consumed this way is not unlimited, however.
SQL Server checks each time an expansion is needed to see if the request is reasonable given the resources available at that time.
Some insight to this process can be obtained by enabling undocumented trace flag 1504, together with 3604 (for message output to the console)
or 3605 (output to the SQL Server error log). If the index build plan is parallel, only 3605 is effective because parallel workers cannot send trace messages cross-thread to the console.
: Internals of the Seven SQL Server Sorts – Part 1


: 1603
: Use standard disk I/O (ie turn off asynchronous I/O)
:


: 1604
: Once enabled at start up makes SQL Server output information regarding memory allocation requests
:


: 1609
: Turns on the unpacking and checking of RPC information in Open Data Services. Used only when applications depend on the old behavior.
:


: 1610
: Boot the SQL dataserver with TCP_NODELAY enabled
:


: 1611
: If possible, pin shared memory — check errorlog for success/failure
:


: 1613
: Set affinity of the SQL data server engine's onto particular CPUs — usually pins engine 0 to processor 0, engine 1 to processor 1...
:


: 1704
: Prints information when a temporary table is created or dropped
:


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


: 1800
: 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.
: https://support.microsoft.com/en-us/kb/3009974
: MSDN ms188396
: global only


: 1802
: SQL 9 — After detaching a database that resides on network-attached storage, you cannot reattach the SQL Server database
: https://support.microsoft.com/en-us/kb/922804


: 1806
: Disable Instant File Initialization
: http://technet.microsoft.com/en-au/library/cc917726.aspx


: 1807
: Enable option to have database files on SMB share for SQL Server 2008 and 2008R2
: http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx


: 1810
: Prints the file create/open/close timings
:


: 1903
: 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
: https://support.microsoft.com/en-us/kb/911678


: 2301
: Enable advanced decision support optimizations
: KB920093
: MSDN ms188396
: global and session and query


: 2312
: 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.
: KB2801413
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session or query


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


: 2330
: Query performance decreases when sys.dm_db_index_usage_stats has large number of rows
: https://support.microsoft.com/en-us/kb/2003031
: http://www.brentozar.com/archive/2015/11/trace-flag-2330-who-needs-missing-index-requests/


: 2335
: 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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
: https://support.microsoft.com/en-us/kb/2413549
: MSDN ms188396
: global or session or query


: 2340
: 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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
: https://support.microsoft.com/en-us/kb/2009160
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session or query


: 2363
: TF Selectivity
: Cardinality Estimation Framework 2014 First Look


: 2371
: Changes the fixed auto update statistics threshold to dynamic auto update statistics threshold.
: Beginning with SQL Server 2016 this behavior is controlled by the engine and trace flag 2371 has no effect.
: http://www.sqlservice.se/sv/start/blogg/sql-server--auto-update-stats-part-2.aspx
: https://support.microsoft.com/en-us/kb/2754171
: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
: MSDN ms188396
: global only


: 2372
: Displays memory utilization during the optimization process. Memory for Phases.
: More Undocumented Query Optimizer Trace Flags
: Cardinality Estimation Framework 2014 First Look


: 2373
: Displays memory utilization during the optimization process. Memory for Deriving Properties.
: More Undocumented Query Optimizer Trace Flags
: Cardinality Estimation Framework 2014 First Look


: 2388
: Change DBCC SHOW_STATISTICS output to show stats history and lead key type such as known ascending keys
: http://www.benjaminnevarez.com/2013/02/statistics-on-ascending-keys


: 2389
: Enable automatically generated quick statistics for ascending keys (histogram amendment).
If trace flag 2389 is set, and a leading statistics column is marked as ascending, then the histogram used to estimate cardinality will be adjusted at query compile time.
: KB2801413
: http://www.sqlservice.se/sv/start/blogg/sql-server-statistics--traceflags-2389--2390.aspx
: http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582227.aspx
: http://www.sqlmag.com/article/tsql3/making-the-most-of-automatic-statistics-updating--96767
: http://sqlperformance.com/2016/07/sql-statistics/trace-flag-2389-new-cardinality-estimator
: https://www.sswug.org/sswugresearch/community/trace-flag-2389-and-the-new-cardinality-estimator/
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session or query


: 2390
: Enable automatically generated quick statistics for ascending or unknown keys (histogram amendment).
If trace flag 2390 is set, and a leading statistics column is marked as ascending or unknown, then the histogram used to estimate cardinality will be adjusted at query compile time
: http://www.sqlservice.se/sv/start/blogg/sql-server-statistics--traceflags-2389--2390.aspx
: http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582227.aspx
: KB2801413
: http://www.sqlmag.com/article/tsql3/making-the-most-of-automatic-statistics-updating--96767
: MSDN ms188396
: global or session or query


: 2430
: Fixes performance problem when using large numbers of locks
: https://support.microsoft.com/en-us/kb/2754301


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


: 2453
: 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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
: http://sqlperformance.com/2014/06/t-sql-queries/table-variable-perf-fix
: https://support.microsoft.com/en-us/kb/2952444
: MSDN ms188396
: global or session or query


: 2470
: Fixes performance problem when using AFTER triggers on partitioned tables
: https://support.microsoft.com/en-us/kb/2606883


: 2505
: SQL 7 — Prevents DBCC TRACEON 208, SPID 10 errors from appearing in the error log
: https://support.microsoft.com/en-us/kb/243352


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


: 2509
: Used with DBCC CHECKTABLE to see the total count of forward records in a table
:


: 2514
: Verbose Merge Replication logging to msmerge_history table for troubleshooting Merger repl performance
: http://sqlblog.com/blogs/argenis_fernandez/archive/2012/05/29/ghost-records-backups-and-database-compression-with-a-pinch-of-security-considerations.aspx


: 2520
: Forces DBCC HELP to return syntax of undocumented DBCC statements. If 2520 is not turned on, DBCC HELP will refuse to give you the syntax stating: "No help available for DBCC statement 'undocumented statement'". dbcc help ('?')
:


: 2521
: SQL 7 SP2 — Facilitates capturing a Sqlservr.exe user-mode crash dump for postmortem analysis
:


: 2528
: Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE.
By default, the degree of parallelism is automatically determined by the query processor.
The maximum degree of parallelism is configured just like that of parallel queries.
For more information, see Configure the max degree of parallelism Server Configuration Option .
Parallel DBCC should typically be left enabled.
For DBCC CHECKDB, the query processor reevaluates and automatically adjusts parallelism with each table or batch of tables checked.
Sometimes, checking may start when the server is almost idle.
An administrator who knows that the load will increase before checking is complete may want to manually decrease or disable parallelism.
Disabling parallel checking of DBCC can cause DBCC to take much longer to complete and if DBCC is run with the TABLOCK feature enabled and parallelism set off, tables may be locked for longer periods of time.
: MSDN ms188396
: global or session


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


: 2536
: Allows you to see inactive records in transaction log using fn_dblog.
Similar to trace flag 2537 for older version than SQL Server 2008.
: http://www.sqlsoldier.com/wp/sqlserver/day19of31daysofdisasterrecoveryhowmuchlogcanabackuplog


: 2537
: Allows you to see inactive records in transaction log using fn_dblog
: http://www.sqlsoldier.com/wp/sqlserver/day19of31daysofdisasterrecoveryhowmuchlogcanabackuplog


: 2540
: Unknown, but related to controlling the contents of a memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2541
: Unknown, but related to controlling the contents of a memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2542
: Unknown, but related to controlling the contents of a memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2543
: Unknown, but related to controlling the contents of a memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2544
: Produces a full memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2545
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2546
: Dumps all threads for SQL Server in the dump file
: http://support.microsoft.com/kb/917825/en-us


: 2547
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


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


: 2549
: 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.
: 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.
: http://www.sqlservice.se/sv/start/blogg/faster-dbcc-checkdb-by-using-trace-flag-2562-and-2549.aspx
: http://blogs.msdn.com/b/saponsqlserver/archive/2011/12/22/faster-dbcc-checkdb-released-in-sql-2008-r2-sp1-traceflag-2562-amp-2549.aspx
: http://support.microsoft.com/kb/2634571
: https://support.microsoft.com/en-us/kb/2732669
: MSDN ms188396
: global only


: 2550
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2551
: Produces a filtered memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2552
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2553
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2554
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2555
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2556
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2557
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2558
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2559
: Unknown, but related to controlling the contents of a
memory dump
: http://support.microsoft.com/kb/917825/en-us


: 2562
: Runs the DBCC CHECKDB command in a single "batch" regardless of the number of indexes in the database.
By default, the DBCC CHECKDB command tries to minimize tempdb resources by limiting the number of indexes or "facts" that it generates by using a "batches" concept.
This trace flag forces all processing into one batch.
One effect of using this trace flag is that the space requirements for tempdb may increase.
Tempdb may grow to as much as 5% or more of the user database that is being processed by the DBCC CHECKDB command.
: 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.
: http://www.sqlservice.se/sv/start/blogg/faster-dbcc-checkdb-by-using-trace-flag-2562-and-2549.aspx
: http://blogs.msdn.com/b/saponsqlserver/archive/2011/12/22/faster-dbcc-checkdb-released-in-sql-2008-r2-sp1-traceflag-2562-amp-2549.aspx
: http://support.microsoft.com/kb/2634571
: http://support.microsoft.com/kb/2732669/en-us
: MSDN ms188396
: global only


: 2566
: Runs the DBCC CHECKDB command without data purity check unless DATA_PURITY option is specified.
: 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.
: https://support.microsoft.com/en-us/kb/945770
: MSDN ms188396
: global only


: 2588
: Get more information about undocumented DBCC commands
: http://www.sqlservice.se/sv/start/blogg/trace-flag--undocumented-commands.aspx


: 2701
: SQL 6.5 — Sets the @@ERROR system function to 50000 for RAISERROR messages with severity levels of 10 or less. When disabled, sets the @@ERROR system function to 0 for RAISERROR messages with severity levels of 10 or less
:


: 2861
: Keep zero cost plans in cache. Tip: Avoid Using Trace Flag 2861 to Cache Zero-Cost Query Plan
: http://support.microsoft.com/kb/325607


: 3001
: Stops sending backup entries into MSDB
:


: 3004
: Returns more info about Instant File Initialization. Shows information about backups and file creations use with 3605 to direct to error log.
: https://blogs.msdn.microsoft.com/psssql/2008/01/23/how-it-works-what-is-restorebackup-doing/
: http://victorisakov.files.wordpress.com/2011/10/sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf


: 3014
: Returns more info about backups to the errorlog
: http://victorisakov.files.wordpress.com/2011/10/sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf


: 3023
: Enables CHECKSUM option as default for BACKUP command
: 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) .
: https://support.microsoft.com/en-us/kb/2656988
: MSDN ms188396
: global and session


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


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


: 3051
: Enables SQL Server Backup to URL logging to a specific error log file.
: MSDN ms188396
: https://msdn.microsoft.com/en-us/library/jj919149.aspx
: global only


: 3101
: Fix performance problems when restoring database with CDC
: http://support.microsoft.com/kb/2567366/


: 3104
: Causes SQL Server to bypass checking for free space
:


: 3106
: Required to move sys databases
:


: 3111
: Cause LogMgr::ValidateBackedupBlock to be skipped during backup and restore operation
:


: 3117
: QL 9 — SQL Server 2005 tries to restore the log files and the data files in a single step which some third-party snapshot backup utilities do not support. Turing on 3117 does things the SQL 8 way multiple-step restore process.
: https://support.microsoft.com/en-us/kb/915385


: 3205
: Disable HW compression for backup to tape drives
: MSDN ms188396
: global


: 3213
: Output buffer info for backups to ERRORLOG
: http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx


: 3222
: Disables the read ahead that is used by the recovery operation during roll forward operations
: TECHNET List Of SQL Server Trace Flags


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


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


: 3422
: Log record auditing
: http://technet.microsoft.com/en-au/library/cc917726.aspx


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


: 3282
: SQL 6.5 — Used after backup restoration fails
: https://support.microsoft.com/en-us/kb/215458


: 3400
: Prints the recovery timings
:


: 3422
: 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.
: https://support.microsoft.com/en-us/kb/215458


: 3502
: Writes info about checkpoints to teh errorlog
: http://victorisakov.files.wordpress.com/2011/10/sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf


: 3503
: Indicates whether the checkpoint at the end of automatic recovery was skipped for a database (this applies only to read-only databases)
: http://www.sql-server-performance.com/2002/traceflags/


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


: 3505
: Disables automatic checkpointing
: http://support.microsoft.com/kb/815436


: 3601
: Stack trace when error raised. Also see 3603.
:


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


: 3603
: SQL Server fails to install on tricore, Bypass SMT check is enabled, flags are added via registry. Also see 3601.
:


: 3604
: Redirect DBCC command output to query window
: http://blogs.msdn.com/b/askjay/archive/2011/01/21/why-do-we-need-trace-flag-3604-for-dbcc-statements.aspx
: Internals of the Seven SQL Server Sorts – Part 1
: http://www.sqlservice.se/sv/start/blogg/querytraceon.aspx


: 3605
: Directs the output of some Trace Flags to the Errorlog
: Internals of the Seven SQL Server Sorts – Part 1
: http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx


: 3607
: Skip recovery on startup
: http://sqlkbs.blogspot.se/2008/01/trace-flag.html


: 3608
: 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.
: Do not use during normal operation.
: MSDN ms188396
: global only


: 3609
: Do not create tempdb at startup
: http://basitaalishan.com/2012/02/20/essential-trace-flags-for-recovery-debugging/


: 3610
: SQL 9 — Divide by zero to result in NULL instead of error
:


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


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


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


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


: 3654
Function:Allocations to stack
:


: 3656
: Enables resolve of all call stacks in extended events
: http://sqlcat.com/sqlcat/b/msdnmirror/archive/2010/05/11/resolving-dtc-related-waits-and-tuning-scalability-of-dtc.aspx


: 3659
: Enables logging all errors to error log during server startup
: http://spaghettidba.com/2011/05/20/trace-flag-3659/


: 3688
: Removes messages to error log about traces started and stopped
: http://support.microsoft.com/kb/922578/en-us


: 3689
: 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.
: http://support.microsoft.com/kb/922578/en-us


: 3801
: Prohibits use of USE DB statement
:


: 3913
: SQL 7/8 — SQL Server does not update the rowcnt column of the sysindexes system table until the transaction is committed. When turned on the optimizer gets row count information from in-memory metadata that is saved to sysindexes system table when the transaction commits.
:


: 3923
: Let SQL Server throw an exception to the application when the 3303 warning message is raised
: https://support.microsoft.com/kb/3014867/en-us


: 4001
: Very verbose logging of each login attempt to the error log. Includes tons of information
:


: 4010
: Allows only shared memory connections to the SQL Server. Meaning, you will only be able to connect from the server machine itself. Client connections over TCP/IP or named pipes will not happen.
:


: 4013
: Log each new connection the error log
: http://sqlkbs.blogspot.se/2008/01/trace-flag.html


: 4020
: Boot without recover
:


: 4022
: Bypass Startup procedures
: http://www.sqlservice.se/sv/start/blogg/sql-server-2012-cu1-upgrade-step--msdb110_upgrade-sql--encountered-error-547.aspx


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


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


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


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


: 4044
: SA account can be unlocked by rebooting server with trace flag. If sa (or sso_role) password is lost, add this to your RUN_serverfile. This will generate new password when server started.
:


: 4052
: SQL 9+ Prints TDS packets sent to the client (output) to console. Startup only.
:


: 4055
: SQL 9+ Prints TDS packets received from the client to console. Startup only.
:


: 4102
: SQL 9 — Query performance is slow if the execution plan of the query contains semi join operators Typically, semi join operators are generated when the query contains the IN keyword or the EXISTS keyword. Enable flag 4102 and 4118 to overcome this.
: https://support.microsoft.com/en-us/kb/940128


: 4104
: SQL 9 — Overestimating cardinality of JOIN operator. When additional join predicates are involved, this problem may increase the estimated cost of the JOIN operator to the point where the query optimizer chooses a different join order. When the query optimizer chooses a different join order, SQL 9 system performance may be slow.
: https://support.microsoft.com/en-us/kb/920346


: 4107
: SQL 9 — When you run a query that references a partitioned table, query performance may decrease
: https://support.microsoft.com/en-us/kb/923849


: 4116
: SQL 9 — Query runs slowly when using joins between a local and a remote table
: https://support.microsoft.com/en-us/kb/950880


: 4121
: SQL 9 — Query that involves an outer join operation runs very slowly. However, if you use the FORCE ORDER query hint in the query, the query runs much faster. Additionally, the execution plan of the query contains the following text in the Warnings column: NO JOIN PREDICATE.
:


: 4123
: Query that has many outer joins takes a long time to compile in SQL Server 2005
: https://support.microsoft.com/en-us/kb/943060


: 4125
: SQL 9 — Query may take more time to finish if using an inner join to join a derived table that uses DISTINCT keyword
: https://support.microsoft.com/en-us/kb/949854


: 4127
: SQL 9 — Compilation time of some queries is very long in an x64-based version.
Basically its more than execution time because more memory allocations are necessary in the compilation process.
: https://support.microsoft.com/en-us/kb/953569


: 4130
: XML performance fix
: http://support.microsoft.com/kb/957205


: 4134
: Bugfix for error: parallel query returning different results every time
: http://support.microsoft.com/kb/2546901
: http://sql-sasquatch.blogspot.se/2014/04/whaddayaknow-bout-sqlserver-trace-flag.html


: 4135
: Bugfix for error inserting to temp table
: http://support.microsoft.com/kb/960770


: 4136
: 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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
: http://blogs.msdn.com/b/axinthefield/archive/2010/11/04/sql-server-trace-flags-for-dynamics-ax.aspx
: http://www.sqlservice.se/sv/start/blogg/nagra-trace-flags-for-sql-server.aspx
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session or query


: 4137
: Causes SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation, under the query optimizer cardinality estimation model of SQL Server 2012 and earlier versions
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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
: http://support.microsoft.com/kb/2658214
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session or query


: 4138
: 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
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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
: http://support.microsoft.com/kb/2667211
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session or query


: 4139
: Enable automatically generated quick statistics (histogram amendment) regardless of key column status.
If trace flag 4139 is set, regardless of the leading statistics column status (ascending, descending, or stationary), the histogram used to estimate cardinality will be adjusted at query compile time
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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
Link: https://support.microsoft.com/en-us/kb/2952101
: MSDN ms188396
: global or session or query


: 4199
: Controls query optimizer changes released in SQL Server Cumulative Updates and Service Packs.
Starting with SQL Server 2016, trace flag 4199 changes that are made to previous releases of SQL Server will become enabled under database compatibility level 130 without trace flag 4199 enabled
To enable this at the database level, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) .
: Starting with SQL Server 2016, customers are advised to remove trace flag 4199 after they migrate their databases to the latest compatibility level
because trace flag 4199 will be reused for future query optimizer changes that may not apply to your application and could cause unexpected plan performance changes on a production system.
This means that different trace flag 4199 changes are enabled for each compatibility level that is supported in a given product release.
: http://www.sqlservice.se/sv/start/blogg/one-trace-flag-to-rule-them-all.aspx
: https://msdn.microsoft.com/en-us/library/bb510411.aspx#TraceFlag
: https://support.microsoft.com/en-us/kb/974006
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session


: 4606
: Over comes SA password by startup. Disables password policy check during server startup.
: https://support.microsoft.com/en-us/kb/936892


: 4610
: 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.
: https://support.microsoft.com/en-us/kb/959823
: MSDN ms188396
: global only


: 4612
: Disable the ring buffer logging — no new entries will be made into the ring buffer
:


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


: 4614
: 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.
: https://support.microsoft.com/en-us/kb/925744


: 4616
: 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.
: MSDN ms188396
: global only


: 4618
: 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
: MSDN ms188396
: https://support.microsoft.com/en-us/kb/933564
: global only


: 4621
: SQL 9 – After 4610 & 4618 you can still customize the quota for TokenAndPermUserStore cache store that is based on the current workload
: https://support.microsoft.com/en-us/kb/959823


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


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


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


: 6498
: 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.
: Beginning with SQL Server 2014 SP2 and SQL Server 2016 this behavior is controlled by the engine and trace flag 6498 has no effect.
: https://support.microsoft.com/en-us/kb/3024815
: MSDN ms188396
: http://blogs.msdn.com/b/sql_server_team/archive/2015/10/09/query-compile-big-gateway-policy-changes-in-sql-server.aspx
: global only


: 6527
: 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.
: MSDN ms188396
: global


: 6532
: 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.
: KB3107399
: MSDN ms188396
: global and session


: 6533
: 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.
: KB3107399
: MSDN ms188396
: global and session


: 6534
: Enables performance improvement of query operations with spatial data types in SQL Server 2012, SQL Server 2014 and SQL Server 2016.
The performance gain will vary, depending on the configuration, the types of queries, and the objects.
: https://support.microsoft.com/en-us/kb/3054180
: KB3107399
: https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-native-spatial-implementations/
: MSDN ms188396
: global and session


: 7103
: Disable table lock promotion for text columns
: https://support.microsoft.com/en-us/kb/230044


: 7300
: Outputs extra info about linked server errors
: http://support.microsoft.com/kb/314530


: 7314
: Forces NUMBER values with unknown precision/scale to be treated as double values with OLE DB provider
: MSDN ms188396
: https://support.microsoft.com/en-us/kb/3051993
: global and session


: 7352
: Show the optimizer output and the post-optimization rewrite in action
: Internals of the Seven SQL Server Sorts – Part 1


: 7412
: Enables the lightweight query execution statistics profiling infrastructure
: MSDN ms188396
: https://support.microsoft.com/en-us/kb/3170113
: global only


: 7470
: 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
: https://support.microsoft.com/en-us/kb/3088480


: 7471
: Running multiple UPDATE STATISTICS for different statistics on a single table concurrently
: https://support.microsoft.com/en-us/kb/3156157
: http://sqlperformance.com/2016/05/sql-performance/parallel-rebuilds


: 7501
: Dynamic cursors are used by default on forward-only cursors.
Dynamic cursors are faster than in earlier versions and no longer require unique indexes.
This flag disables the dynamic cursor enhancements and reverts to version 6.0 behavior.
: https://support.microsoft.com/en-us/kb/152032


: 7502
: Disable cursor plan caching for extended stored procedures
: http://basitaalishan.com/2012/02/20/essential-trace-flags-for-recovery-debugging/


: 7505
: Enables version 6.x handling of return codes when calling dbcursorfetchex and the resulting cursor position follows the end of the cursor result set
:


: 7525
: SQL 8 — Reverts to ver 7 behavior of closing nonstatic cursors regardless of the SET CURSOR_CLOSE_ON_COMMIT state
:


: 7601
: Helps in gathering more information in full text search by turning on full text tracing which gathers information on indexing process using the error log. Also 7603, 7604, 7605 trace flags.
:


: 7608
: Performance fix for slow full text population with a composite clustered index
: https://support.microsoft.com/en-us/kb/938672


: 7613
: SQL 9 — Search results are missing when performing a full-text search operation on Win SharePoint Services 2.0 site after upgrading
: https://support.microsoft.com/en-us/kb/927643


: 7614
: SQL 9 — Full-text index population for the indexed view is very slow
: https://support.microsoft.com/en-us/kb/928537


: 7646
: SQL 10 — Avoids blocking when using full text indexing. An issue we experienced that full text can be slow when there is a high number of updates to the index and is caused by blocking on the docidfilter internal table.
:


: 7806
: SQL 9 — Enables a dedicated administrator connection on SQL Express, DAC resources are not reserved by default
: MSDN ms188396
: https://msdn.microsoft.com/en-us/library/ms189595.aspx
: global only


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


: 7827
: Record connection closure info in ring buffer
: http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx
: https://connect.microsoft.com/SQLServer/feedback/details/518158/-packet-error-a-fatal-error-occurred-while-reading-the-input-stream-from-the-network


: 8002
: Changes CPU Affinity behaviour
: http://support.microsoft.com/kb/818769


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


: 8010
: Fixes problem that SQL Server services can not be stopped
: http://support.microsoft.com/kb/2633271/en-us


: 8011
: Disable the ring buffer for Resource Monitor
: KB920093
: MSDN ms188396
: global and session


: 8012
: Disable the ring buffer for schedulers
: KB920093
: MSDN ms188396
: global only


: 8015
: Disable auto-detection and NUMA setup
: https://support.microsoft.com/en-us/kb/948450
: MSDN ms188396
: http://sql-sasquatch.blogspot.se/2013/04/startup-trace-flags-i-love.html
: global only


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


: 8017
: Upgrade version conflict
: http://social.msdn.microsoft.com/Forums/eu/sqlexpress/thread/dd6fdc16-9d8d-4186-9549-85ba4c322d10
: http://connect.microsoft.com/SQLServer/feedback/details/407692/indicateur-de-trace-8017-reported-while-upgrading-from-ssee2005-to-ssee2008


: 8018
: Disable the exception ring buffer
: KB920093
: MSDN ms188396
: global only


: 8019
: Disable stack collection for the exception ring buffer
: KB920093
: MSDN ms188396
: global only


: 8020
: Disable working set monitoring
: KB920093
: MSDN ms188396
: global only


: 8021
: 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.
: https://blogs.msdn.microsoft.com/psssql/2011/11/11/sql-server-clarifying-the-numa-configuration-information/


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


  1. The non-yielding workers CPU utilization must be > 40 percent.
  2. 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."
    : [How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888]

: 8026
: SQL Server will clear a dump trigger after generating the dump once
: http://support.microsoft.com/kb/917825/en-us


: 8030
: Fix for performance bug
: http://support.microsoft.com/kb/917035
: http://www.sqlservice.se/sv/start/blogg/sql-server-2005-slowing-down-after-a-while.aspx


: 8032
: Alters cache limit settings
: Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.
: MSDN ms188396
: global only


: 8033
: Alters cache limit settings
: 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.
:


: 8038
: Will drastically reduce the number of context switches when running SQL 2005 or 2008
: KB972767
: http://forum.proxmox.com/threads/15844-Win7-x64-guest-with-SQLServer-2012-High-CPU-usage
: TECHNET List Of SQL Server Trace Flags


: 8040
: Disables Resource Governor
: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/64/sql-server-disabling-resource-governor-permanently-somewhat


: 8048
: Converts NUMA partitioned memory objects into CPU partitioned
: http://sql-sasquatch.blogspot.se/2013/04/startup-trace-flags-i-love.html
: https://support.microsoft.com/en-us/kb/2809338
: http://blogs.msdn.com/b/psssql/archive/2012/12/20/how-it-works-cmemthread-and-debugging-them.aspx
: MSDN ms188396
: 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
: Beginning with SQL Server 2014 SP2 and SQL Server 2016 this behavior is controlled by the engine and trace flag 8048 has no effect.
: global only


Thanks to: @sql_handle ( https://twitter.com/sql_handle )
Related to: 8015, 9024


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


: 8050
: Causes "optional" wait types (see the CSS article) to be excluded when querying sys.dm_os_wait_stats
: https://blogs.msdn.microsoft.com/psssql/2009/11/02/the-sql-server-wait-type-repository/


: 8079
: 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.
: This trace flag applies to SQL Server 2014 SP2. Beginning with SQL Server 2016 this behavior is controlled by the engine and trace flag 8048 has no effect.
: KB972767
: MSDN ms188396
: global only


: 8202
: Used to replicate UPDATE as DELETE/INSERT pair at the publisher. ie
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.
:


: 8203
: Display statement and transaction locks on a deadlock error
:


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


: 8207
: 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.
: https://blogs.msdn.microsoft.com/psssql/2009/11/02/the-sql-server-wait-type-repository/
: MSDN ms188396
: https://support.microsoft.com/en-us/kb/302341
: global only


: 8209
: Output extra information to error log regarding replication of schema changes in SQL Server Replication
: http://support.microsoft.com/kb/916706/en-us


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


: 8446
: 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.
: https://support.microsoft.com/en-us/kb/959008


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


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


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


: 8605
: Displays logical and physical trees used during the optimization process
: More Undocumented Query Optimizer Trace Flags
: Yet another X-Ray for the QP


: 8606
: Show LogOp Trees
: Cardinality Estimation Framework 2014 First Look
: Yet another X-Ray for the QP


: 8607
: Displays the optimization output tree during the optimization process
: Internals of the Seven SQL Server Sorts – Part 1
: More Undocumented Query Optimizer Trace Flags
: Yet another X-Ray for the QP


: 8612
: Add Extra Info to the Trees Output
: Cardinality Estimation Framework 2014 First Look


: 8615
: Display the final memo structure
: http://www.benjaminnevarez.com/2012/04/inside-the-query-optimizer-memo-structure/
: http://www.somewheresomehow.ru/optimizer-part-3-full-optimiztion-optimization-search0/


: 8619
: Show Applied Transformation Rules
: http://sqlblog.com/blogs/paul_white/archive/2013/02/06/incorrect-results-with-indexed-views.aspx
: Cardinality Estimation Framework 2014 First Look
: Yet another X-Ray for the QP


: 8620
: Add memo arguments to trace flag 8619
: Query Optimizer Deep Dive — Part 4
: Yet another X-Ray for the QP


: 8621
: Rule with resulting tree
: Query Optimizer Deep Dive — Part 4
: Yet another X-Ray for the QP


: 8628
: When used with TF 8666, causes extra information about the transformation rules applied to be put into the XML showplan.
: Yet another X-Ray for the QP


: 8649
: Set Cost Threshold for parallelism from 1 to 0
: http://www.sqlservice.se/sv/start/blogg/enable-parallellism-for-specific-query.aspx


: 8666
: CQScanPartitionSortNew is one of only two sort classes that sets the Soft Sort property exposed when Sort operator execution plan properties are generated with undocumented trace flag 8666 enabled
: Internals of the Seven SQL Server Sorts – Part 1
: Yet another X-Ray for the QP


: 8675
: Displays the query optimization phases for a specific optimization
: More Undocumented Query Optimizer Trace Flags


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


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


: 8690
Undocumented trace flag
: 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.
: https://blogs.msdn.microsoft.com/psssql/2015/12/15/spool-operator-and-trace-flag-8690/
: http://dba.stackexchange.com/questions/52552/index-not-making-execution-faster-and-in-some-cases-is-slowing-down-the-query


: 8721
: Reports to the error log when auto-update statistics executes
: https://support.microsoft.com/en-us/kb/195565
: MSDN ms188396
: global only


: 8722
: Disable all hints except locking hints
: http://sqlmag.com/sql-server/investigating-trace-flags


: 8744
: Disable pre-fetching for the Nested Loop operator
: KB920093
: MSDN ms188396
: global only


: 8755
: Disable all locking hints
: http://sqlmag.com/sql-server/investigating-trace-flags


: 8757
: Skip trivial plan optimization and force a full optimization
: More Undocumented Query Optimizer Trace Flags


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


: 8780
: Give the optimizer more time to find a better plan
: http://www.sqlservice.se/sv/start/blogg/sql-server-trace-flag--8780.aspx


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


: 8809
: Extended Page Heap Activities.
Referenced in passing in the CSS article in relation to debugging memory scribbler problems.
: https://blogs.msdn.microsoft.com/psssql/2012/11/12/how-can-reference-counting-be-a-leading-memory-scribbler-cause/


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


: 9024
: Converts a global log pool memory object into NUMA node partitioned memory object
: 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.
: https://support.microsoft.com/en-us/kb/2809338
: MSDN ms188396
: global only
Related to: 8048


: 9059
: SQL 8 — Turns back behavior to SP3 after a SP4 installation, this allows to choose an index seek when comparing numeric columns or numeric constants that are of different precision or scale; else would have to change schema/code.
:


: 9082
: SQL 9 — Stored procedure using views, perform slow compared to ver 8 if views use JOIN operator and contain sub queries
: https://support.microsoft.com/en-us/kb/942906


: 9130
: 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.
: http://sqlblog.com/blogs/paul_white/archive/2012/10/15/cardinality-estimation-bug-with-lookups-in-sql-server-2008-onward.aspx
: http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/12/06/my-new-favourite-traceflag.aspx
: http://sqlblog.com/blogs/paul_white/archive/2013/06/11/hello-operator-my-switch-is-bored.aspx
: https://connect.microsoft.com/SQLServer/feedback/details/767395/cardinality-estimation-error-with-pushed-predicate-on-a-lookup
: http://www.theboreddba.com/Categories/FunWithFlags/Revealing-Predicates-in-Execution-Plans-(TF-9130).aspx


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


: 9185
: Cardinality estimates for literals that are outside the
histogram range are very low
: https://support.microsoft.com/en-us/kb/kbview/833406
Related to: 9205


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


: 9205
: Cardinality estimates for literals that are outside the histogram range are very low for tables that have parent-child relationships
: https://support.microsoft.com/en-us/kb/kbview/833406
Related to: 9185


: 9207
: Fixes that SQL Server underestimates the cardinality of a query expression and query performance may be slow
: https://support.microsoft.com/en-us/kb/831302


: 9259
: SQL 9/10 — An access violation occurs on running a query marked by the following message and a dump in the log folder: KB 970279 / 971490. Msg 0, Level 11, State 0, Line 0 — A severe error occurred on the current command. The results, if any, should be discarded.
:


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


: 9292
: Output Statistics considered to be used by Query Optimizer
: 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


: 9347
: Disables batch mode for sort operator. SQL Server 2016 introduces a new batch mode sort operator that boosts performance for many analytical queries.
: https://support.microsoft.com/en-us/kb/3172787
: MSDN ms188396
: Niko Neugebauer Columnstore Indexes – part 86
: global only


: 9349
: https://support.microsoft.com/en-us/kb/3172787
: 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.
: MSDN ms188396
: Niko Neugebauer Columnstore Indexes – part 86
: global or session or query


: 9358
: Disable batch mode sort operations in a complex parallel query. For example, this flag could apply if the query contains merge join operations.
: Niko Neugebauer Columnstore Indexes – part 86
: https://support.microsoft.com/en-us/kb/3171555


: 9389
: 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.
: Niko Neugebauer Columnstore Indexes – part 86
: MSDN ms188396
: global or session


: 9453
: 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
: Niko Neugebauer Columnstore Indexes – part 35
: http://sqlmag.com/sql-server/what-you-need-know-about-batch-mode-window-aggregate-operator-sql-server-2016-part-1


: 9471
: 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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session or query


: 9476
: 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.
: Please ensure that you thoroughly test this option, before rolling it into a production environment.
: https://support.microsoft.com/en-us/kb/3189675
: New Features in SQL Server 2016 Service Pack 1
: MSDN ms188396
: global or session or query


: 9481
: 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 QUERYTRACEONquery hint
: http://support.microsoft.com/kb/2801413
: New Features in SQL Server 2016 Service Pack 1
: https://sqlserverscotsman.wordpress.com/2016/11/28/a-guide-on-forcing-the-legacy-ce/
: MSDN ms188396
: global or session or query


: 9485
: Disables SELECT permission for DBCC SHOW_STATISTICS
: https://support.microsoft.com/en-us/kb/2683304
: MSDN ms188396
: global only


: 9495
: Disables parallelism during insertion for INSERT...SELECT operations and it applies to both user and temporary tables
: https://support.microsoft.com/en-us/kb/3180087
: MSDN ms188396
: global or session


: 9532
: SQL 11 CTP3 — to get more than 1 availability group replica in CTP3 Scope Startup
: http://connect.microsoft.com/SQLServer/feedback/details/682581/denali-hadron-read-only-routing-url-is-not-yet-implemente


: 9567
: 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.
: MSDN ms188396
: https://www.mssqltips.com/sqlservertip/4537/sql-server-2016-availability-group-automatic-seeding/
: https://msdn.microsoft.com/en-us/library/mt735149.aspx
: MSDN mt736907
: global or session


: 9592
: Enables log stream compression for synchronous availability groups.
This feature is disabled by default on synchronous availability groups because compression adds latency.
: MSDN ms188396
: MSDN mt736907
: global or session


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


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


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


: 9830
Undocumented trace flag
: Activate the trace flag before creating a natively compiled procedure.
If you now open up the SQL Server error log you should see the compilation process for the natively compiled procedure.
This is an undocumented trace flag so please don't use this on a production system.
: https://web.archive.org/web/20160327221828/http://speedysql.com/2015/10/28/new-trace-flag-for-in-memory-oltp-hekaton/


: 9851
Undocumented trace flag
: 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.
: http://gsl.azurewebsites.net/Portals/0/Users/dewitt/talks/HekatonWhitePaper.pdf


: 10204
: Disables merge/recompress during columnstore index reorganization.
In SQL Server 2016, when a columnstore index is reorganized, there is new functionality to automatically merge any small compressed rowgroups
into larger compressed rowgroups, as well as recompressing any rowgroups that have a large number of deleted rows.
: Trace flag 10204 does not apply to column store indexes which are created on memory-optimized tables.
: MSDN ms188396
: global or session


: 10213
: Enables the option to configure compression delay in columnstore indexes in SQL Server 2016
: http://www.nikoport.com/2016/02/04/columnstore-indexes-part-76-compression-delay/
: session


: 10316
: 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.
: 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.
: MSDN ms188396
: https://support.microsoft.com/en-us/kb/3198846
: 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/
: global or session


')

Source: https://habr.com/ru/post/303212/

More articles:


All Articles