Nikola Dimitrijevic

SQL Server trace flags guide; from -1 to 840

March 4, 2019 by

SQL Server trace flags are configuration handles that can be used to enable or disable a specific SQL Server characteristic or to change a specific SQL Server behavior. It is an advanced SQL Server mechanism that allows drilling down into a hidden and advanced SQL Server features to ensure more effective troubleshooting and debugging, advanced monitoring of SQL Server behavior and diagnosing of performance issues, or turning on and off various SQL Server features

SQL Server trace flags should be used as an ad-hoc help, and generally, one should not leave tracing turned on for prolonged periods of time. As this is an advanced feature of SQL Server it has to be used with the utmost caution, each flag should be tested in a non-production environment before use. Any prolonged or indefinite use of flags should be done only in situations when that is instructed by SQL Server Product Support

The below list describes the SQL Server trace flags available in various SQL Server versions.

Trace flag number:

-1

Application: Ensures that SQL Server trace flags are set for all connections versus for a single connection. When flags are set via the command line with the “-T” option, they apply to all connection automatically. Therefore this particular flag should be used to set trace flag via DBCC TRACEON and DBCC TRACEOFF.

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

101

Application: Allows logging all steps of the merge replication Replication. Used for troubleshooting of the synchronization process of the merge replication. Usually used along with merge agent logging

Additional research: How to troubleshoot merge replication performance issues by using trace flag 101

Scope: Global

102

Application: Same functionality as trace flag 101, but forces log data to be written in the <Distribution server>..msmerge_history table

Additional research: How to troubleshoot merge replication performance issues by using trace flag 101

Scope: Global

106

Application: Used for Web Synchronization. When running Replmerg.exe, the SQL Server trace flag 106 ensures messages sent to Publishes and from Publisher to be visible. The agent stores the input messages into an ExchangeID(guid).IN.XML file, and the output messages into an ExchangeID(guid).OUT.XML file. The guid in the file name is actually the Exchange Server session GUID. Both files are stored in the same directory as Replmerg.exe.

  • Note: delete files for security reasons when work is completed.

Additional research: Replication Agents (Troubleshooting)

139

Application: This SQL Server trace flag force conversion semantics to be correct for DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKCONSTRAINTS command, for the purpose of the improved precision and conversion logic analysis. It is introduced with database compatibility level 130 and is valid for specific data types only, and used for a database with a compatibility level lower than 130.

  • Note: For SQL Server 2016 RTM CU3, SQL Server 2016 SP1 and newer versions only.

    Warning: Do not leave enabled in a production environment. Use only to perform a validation check of databases as described in this Microsoft Support article. Disable immediately on completing validation checks.

Additional research:

Scope: Global

144

Application: Used for the legacy application to ensure forced server side bucketization, in situations where switching to client-side code is not possible. For legacy applications where a change to the client-side code is not an option or where the queries executed by the application are not parametrized correctly.

Additional research: 6.0 Best Programming Practices

Scope: Not documented

168

Application: Part of the hotfix to resolve the issue where ORDER BY clause when used with the SELECT command against the Views in SQL Server 2005 or SQL Server 2008, not to return the data in random order. Trace flag 168 must be enabled manually after applying the hotfix, and it has to be set before database migration to SQL Server 2005. Otherwise, the hotfix does not have any effects ,and result stays unsorted

Additional research: FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order

Scope: Not documented

174

Application: Enables the change of the SQL Server plan cache buckets to count from 40,009 to 190,001 on x64 systems. Change allows the plan cache to store up to 640,004 query plans

Note: SQL Server trace flag 174 requires detailed testing before applying into a production server.

Additional research:

Scope: Global

176

Application: Whwn this SQL Server trace flag is enabled it activates the hotfix that addresses online partitions rebuilding errors for tables with a computed partitioning column.

Additional research:

Scope: Global/session

204

Application: Uses as a switch for backward compatibility for SQL Server 6.5 to allows non-ANSI standard behavior. Fix ignoring blanks in the LIKE clause. Thus grants aggregated functions to use items via the group by clause not contained in the select list.

Scope: Not documented

205

Application: Allows writing the message in the error log when auto-update statistics triggered recompiling of a statistics-dependent stored procedure occurs.

Additional research:

Scope: Global

210

Application: Enable this SQL Server trace flag to fix SQL Server 2005 error “An error occurred while executing batch” that occurs when running a query against the view

Scope: Not documented

260

Application: Logs the error message “Error 8131: Extended stored procedure DLL ‘%’ does not export __GetXpVersion()” in the error log file in situations where __GetXpVersion() is not supported by the extended stored procedure DLL

Additional research:

Scope: Global/session

460

Application: Change the error message 8152 with 2628 that occurs when storing the string and/or binary data that exceeding the size of the column in Microsoft SQL Server, as the string and binary data is truncated. The new message adds the information about what column and at what row the truncation occurred

Additional research:

Scope: Global/session

SQL Server Version: 2017 CU12 and newer

610

Application: When turned on ensures control to log inserts into tables that contain indexes minimally.

Additional research:

Scope: Global/session

617

Application: When enabled, SQL Server trace flag allows logging all lock escalation in the error log file including the SQL Server handle number and reverts the SQL Server 2012 to old behavior that prevents that uncommitted read queries bypass the ‘lock wait list’.

Additional research: New functionality in SQL Server 2014 – Part 3 – Low Priority Wait

Scope: Not documented

634

Application: When SQL Server trace flag is turned on it disables the background task of the columnstore compression

Additional research: DBCC TRACEON – Trace Flags (Transact-SQL)

Scope: Global

646

Application: Stores the precise information in the error log file about the Columnstores that Query Optimiser eliminates

Additional research: Verifying Columnstore Segment Elimination

Scope: Not documented

647

Application: When this SQL Server trace flag is enabled during the SQL Server start, it prevents a new SQL 2012 data check that executes when adding a new column in a table, to prevent the operation from lasting for a long time.

Additional research: FIX: It takes a long time to add new columns to a table when the row size exceeds the maximum allowed size

Scope: Not documented

652

Application: When enabled it prevents page pre-fetching to occurs during the scan and prevents SQL Server to store database pages into the buffer pool if those pages are not previously used by the scans. Note: When turned on, it is expected that queries that rely on the page pre-fetching to experience performance degradation.

Additional research:

Scope: Global/session

661

Application: Use to prevents the ghost record removal process. Ghost records that are generated as a consequence of a delete operation will never be removed as long as this SQL Server trace flag is enabled. SQL Server trace flag increases storage space consumption and decreases the performance of scan operations.

Additional research:

Scope: Global/session

669

Application: This SQL Server trace flag doesn’t allow user queries to queue requests to the ghost cleanup process. It is used as a workaround for a situation where user queries tries to use the ghost cleanup process during the SQL Server startup before the ghost cleanup process is initialized.

Additional research: Error 17066 or 17310 during SQL Server startup

Scope: Not documented

692

Application: Disables fast inserts for bulk load operations of storing data in a heap or clustered index. If batch size cannot be increased this SQL Server trace flag will reduce reserved unused space at the cost of performance.

Note: available in SQL Server 2016 RTM and newer versions

Additional research: SQL Server 2016, Minimal logging and Impact of the Batch size in bulk load operations

Scope: Global/session

715

Application: Allows table lock to be acquired by the bulk load operation on a heap without a non-clustered index.

The bulk load operations can acquire BU locks when using data bulk-copy into a table, which allows parallel threads to update data into the same table simultaneously, but preventing other non-bulk loading processes access to the table.

Additional research: DBCC TRACEON – Trace Flags (Transact-SQL)

Scope: Global/session

806

Application: SQL Server trace flag grants executing of DBCC audit checks against pages for testing for problems of the logical consistency. DBCC audit checks are used to detect situations where the operation of reading from a disk is executed without errors but the returned data set is invalid, and audit checks of pages are performed for every page read from disk.

Use only if data stability is of higher priority than performance

Additional research: SQL Server I/O Basics – Microsoft Download Center

Scope: Not documented

815

Application: Allows latch enforcement for SQL Server 8 and SQL Server 9 to enable detection of changes made in n-memory data pages.

Additional research:

Scope: Not documented

818

Application: Allows use of in-memory ring buffer that can store last 2,046 successfully executed I/O write operations . It allows diagnosing situations where successful but never written to hard disk for real.

Additional research:

Scope: Not documented

828

Application: Ensures that checkpoint ignores the target recovery interval to ensures stable I/O, or else the setting of the recovery interval is used as a target for the time to be taken by checkpoint

Additional research: How It Works: SQL Server Checkpoint (FlushCache) Outstanding I/O Target

Scope: Not documented

830

Application: prevents the logging of errors in the SQL Server error log file caused by CPU Drift in the SQL Server error log via stalled or stuck I/O detection to be performed when SQL Server starts

Additional research:

Scope: Not documented

834

Application: Improves performance by allowing SQL Server memory manager to allocate Windows’ large pages for the buffer pool to improve the performance of x64 systems. That increase the translation look-aside buffer (TLB) efficiency.

Note: For SQL Server 2012, 2014 and 2016 where the Columnstore Index feature is active, it is not advisable to turn on this SQL Server trace flag.

Additional research:

Scope: Global

836

Application: If this SQL Server trace flag is enabled at SQL Server startup, it forces the scaling of the buffer pool to depends on the max server memory value option instead of depending on the maximal physical memory size. SQL Server trace flag in this case reduces the buffer descriptors number that is at the server startup moved in 32-bit Address Windowing Extensions (AWE) mode.

NOTE: This SQL Server trace flag is valid for 32-bit versions of SQL Server only, where the AWE allocation is enabled.

Additional research:

Scope: Global

839

Application: Forces buffer pool to treat all NUMA memory as a single node (flat memory model).

Additional research: How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes

Scope: Global

840

Application: Enables prefetching mechanism that allows the buffer pool to convert all single-page read request from disk into a request that reads the entire extent that contains the page requested initially

Additional research: The Read Ahead that doesn’t count as Read Ahead

Scope: Global

This concludes this section of SQL Server Trace Flags guide. See the TOC for more articles

Nikola Dimitrijevic
Latest posts by Nikola Dimitrijevic (see all)

Related posts:

  1. SQL Server 2016 Trace flags modifications
  2. SQL Server 2017: SQL Sort, Spill, Memory and Adaptive Memory Grant Feedback
  3. Concept and basics of DBCC Commands in SQL Server
  4. Enlighten World Flag Slicer in Power BI Desktop
  5. Top SQL Server Books
168 Views