sql server 跟踪_SQL Server跟踪标志指南；从-1到840

最新推荐文章于 2024-11-14 18:10:48 发布

culuo4781

最新推荐文章于 2024-11-14 18:10:48 发布

阅读量527

点赞数

文章标签：数据库 python linux mysql java

原文链接：https://www.sqlshack.com/sql-server-trace-flags-guide-from-1-to-840/

版权

本文详细介绍了SQL Server的跟踪标志，这些标志用于改变SQL Server的行为和特性，以协助故障排查、性能优化和高级调试。这些标志包括但不限于-1、101、102、139、174等，每个标志都有特定的应用场景和范围，例如-1用于全局设置，101和102涉及合并复制，174则与计划缓存有关。使用跟踪标志时需要注意，不应长时间开启，且应在非生产环境中测试其影响。

摘要由CSDN通过智能技术生成

sql server 跟踪

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跟踪标志是可用于启用或禁用特定SQL Server特性或更改特定SQL Server行为的配置句柄。它是一种高级SQL Server机制，允许深入研究隐藏的高级SQL Server功能，以确保更有效的故障排除和调试，对SQL Server行为的高级监视和性能问题的诊断，或打开和关闭各种SQL Server功能。

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

SQL Server跟踪标志应作为临时帮助使用，通常，不应将跟踪长时间打开。由于这是SQL Server的高级功能，因此必须非常谨慎地使用它，因此在使用每个标志之前，应在非生产环境中对其进行测试。仅在SQL Server产品支持指示的情况下，才应长时间使用或无限期使用标志。

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

下面的列表描述了各种SQL Server版本中可用SQL Server跟踪标志。

Trace flag number:

跟踪标志号：

-1 ( -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.

应用程序：确保为所有连接（而不是单个连接）设置SQL Server跟踪标志。当通过命令行使用“ -T”选项设置标志时，它们将自动应用于所有连接。因此，应使用此特定标志通过DBCC TRACEON和DBCC TRACEOFF设置跟踪标志。

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

其他研究： http : //www.sql-server-performance.com/2002/traceflags/

101 ( 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

其他研究： 如何通过使用跟踪标志101解决合并复制性能问题

Scope: Global

范围：全球

102 (102)

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

应用程序：与跟踪标志101相同的功能，但是强制将日志数据写入<Distribution server> .. msmerge_history表中

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

其他研究： 如何通过使用跟踪标志101解决合并复制性能问题

Scope: Global

范围：全球

106 (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.

应用程序：用于Web同步。运行Replmerg.exe时，SQL Server跟踪标志106确保发送到Publishes和从Publisher的消息是可见的。代理将输入消息存储到ExchangeID（guid）.IN.XML文件中，并将输出消息存储到ExchangeID（guid）.OUT.XML文件中。文件名中的guid实际上是Exchange Server会话GUID。这两个文件与Replmerg.exe存储在同一目录中。

Note: delete files for security reasons when work is completed.
注意： 出于安全原因，完成工作后删除文件。

Additional research: Replication Agents (Troubleshooting)

其他研究： 复制代理（故障排除）

139 ( 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.

应用程序：此SQL Server跟踪标志强制转换语义对于DBCC CHECKDB ， DBCC CHECKTABLE和DBCC CHECKCONSTRAINTS命令正确，目的是提高精度和转换逻辑分析。它是在数据库兼容级别130中引入的，仅对特定数据类型有效，并且用于兼容级别低于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.
注意：仅 对于SQL Server 2016 RTM CU3，SQL Server 2016 SP1和更高版本。

警告： 请勿在生产环境中保持启用状态。 仅用于执行此Microsoft支持文章中所述的数据库验证检查。 完成验证检查后立即禁用。

Additional research:

其他研究：

Scope: Global

范围：全球

144 (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

补充研究： 6.0最佳编程实践

Scope: Not documented

范围：未记录

168 ( 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

应用程序：该修补程序的一部分，用于解决针对SQL Server 2005或SQL Server 2008中的视图将SELECT命令与ORDER BY子句一起使用时，不按随机顺序返回数据的问题。在应用此修复程序后，必须手动启用跟踪标志168，并且必须在将数据库迁移到SQL Server 2005之前将其设置。否则，此修复程序不会产生任何影响，并且结果保持未排序状态

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

附加研究： FIX：当您通过使用SQL Server 2008中的ORDER BY子句的视图进行查询时，结果仍然以随机顺序返回

Scope: Not documented

范围：未记录

174 (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

应用程序：在x64系统上，启用对SQL Server计划缓存存储区的更改，使其计数从40,009变为190,001。更改允许计划缓存存储多达640,004个查询计划

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

注意：SQL Server跟踪标志174在应用于生产服务器之前需要进行详细的测试。

Additional research:

其他研究：

Scope: Global

范围：全球

176 (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.

应用程序：启用此SQL Server跟踪标志后，它将激活修复程序，该修复程序解决具有计算的分区列的表的联机分区重建错误。

Additional research:

其他研究：

Scope: Global/session

范围：全球/会议

204 ( 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.

应用程序：用作向后兼容SQL Server 6.5的开关，以允许非ANSI标准行为。修复忽略LIKE子句中的空格的问题。因此，通过选择列表中未包含的group by子句，可以授予聚合函数使用项的权限。

Scope: Not documented

范围：未记录

205 ( 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 ( 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

应用程序：启用此SQL Server跟踪标志可修复对视图运行查询时发生SQL Server 2005错误“执行批处理时发生错误”

Scope: Not documented

范围：未记录

260 (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

应用程序：在扩展存储过程DLL不支持__GetXpVersion（）的情况下，在错误日志文件中记录错误消息“错误8131：扩展存储过程DLL'％'不导出__GetXpVersion（） ”

Additional research:

其他研究：

Scope: Global/session

范围：全球/会议

460 (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

应用程序：将字符串和/或二进制数据存储超过Microsoft SQL Server中列大小的字符串和/或二进制数据时，由于字符串和二进制数据被截断，因此将错误消息8152更改为2628 。新消息添加有关截断发生在哪一列和哪一行的信息

Additional research:

其他研究：

Scope: Global/session

范围：全球/会议

SQL Server Version: 2017 CU12 and newer

SQL Server版本： 2017年CU12及更高版本

610 ( 610)

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

应用程序：启用后，可确保控制将插入日志记录到包含索引的表中的最少性。

Additional research:

其他研究：

Scope: Global/session

范围：全球/会议

617 (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’.

应用程序：启用后，SQL Server跟踪标志允许在错误日志文件中记录所有锁升级，包括SQL Server句柄号，并将SQL Server 2012恢复为旧行为，以防止未提交的读取查询绕过“锁等待列表”。

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

其他研究： SQL Server 2014中的新功能–第3部分–低优先级等待

Scope: Not documented

范围：未记录

634 (634)

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

应用程序：启用SQL Server跟踪标志后，它将禁用列存储压缩的后台任务

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

附加研究： DBCC TRACEON –跟踪标志（Transact-SQL）

Scope: Global

范围：全球

646 (646)

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

应用程序：将有关Query Optimiser消除的Columnstores的准确信息存储在错误日志文件中

Additional research: Verifying Columnstore Segment Elimination

附加研究： 验证消除列存储段

Scope: Not documented

范围：未记录

647 (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.

应用程序：在SQL Server启动过程中启用此SQL Server跟踪标志时，它将阻止在表中添加新列时执行的新SQL 2012数据检查，以防止该操作持续很长时间。

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

附加研究： FIX：当行大小超过最大允许大小时，将很长时间将新列添加到表中

Scope: Not documented

范围：未记录

652 (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.

应用程序：启用后，它可以防止在扫描过程中进行页面预取，并且如果扫描以前未使用数据库页面，则SQL Server可以将数据库页面存储到缓冲池中。注意：启用后，预期依赖于页面预取的查询会导致性能下降。

Additional research:

其他研究：

Scope: Global/session

范围：全球/会议

661 ( 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.

应用程序：用于防止重影记录删除过程。只要启用了此SQL Server跟踪标志，就不会删除由于删除操作而生成的Ghost记录。 SQL Server跟踪标志会增加存储空间消耗并降低扫描操作的性能。

Additional research:

其他研究：

Scope: Global/session

范围：全球/会议

669 (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.

应用程序：此SQL Server跟踪标志不允许用户查询将对幻影清理过程的请求排队。它用于以下情况的变通方法：用户查询在初始化镜像清理过程之前尝试在SQL Server启动期间使用镜像清理过程。

Additional research: Error 17066 or 17310 during SQL Server startup

其他研究： SQL Server启动过程中的错误17066或17310

Scope: Not documented

范围：未记录

692 (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.

应用程序：禁用用于将数据存储在堆或聚集索引中的批量加载操作的快速插入。如果不能增加批处理大小，则此SQL Server跟踪标志将以性能为代价减少保留的未使用空间。

Note: available in SQL Server 2016 RTM and newer versions

注意：在SQL Server 2016 RTM和更高版本中可用

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

其他研究： SQL Server 2016，最小日志记录和批量加载操作中批处理大小的影响

Scope: Global/session

范围：全球/会议

715 ( 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.

使用数据批量复制到表中时，批量加载操作可以获得BU锁，这允许并行线程同时将数据更新到同一表中，但阻止其他非批量加载进程访问该表。

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

其他研究： DBCC TRACEON –跟踪标志（Transact-SQL）

Scope: Global/session

范围：全球/会议

806 (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.

应用程序： SQL Server跟踪标志允许对页面进行DBCC审核检查，以测试逻辑一致性问题。 DBCC审核检查用于检测从磁盘读取的操作没有错误但返回的数据集无效的情况，并对从磁盘读取的每个页面执行页的审核检查。

Use only if data stability is of higher priority than performance

仅在数据稳定性优先于性能时使用

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

其他研究： SQL Server I / O基础知识– Microsoft下载中心

Scope: Not documented

范围：未记录

815 (815)

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

应用程序：允许对SQL Server 8和SQL Server 9实施闩锁，以启用对n内存数据页中所做更改的检测。

Additional research:

其他研究：

Scope: Not documented

范围：未记录

818 ( 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.

应用程序：允许使用内存中环形缓冲区，该缓冲区可以存储最近成功执行的2,046个I / O写操作。它可以诊断成功但从未真正写入硬盘的情况。

Additional research:

其他研究：

Scope: Not documented

范围：未记录

828 (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

应用程序：确保检查点忽略目标恢复间隔以确保稳定的I / O，否则将恢复间隔的设置用作检查点要花费的时间的目标

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

其他研究： 工作原理：SQL Server检查点（FlushCache）出色的I / O目标

Scope: Not documented

范围：未记录

830 (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

应用程序：通过在SQL Server启动时执行停滞或卡死的I / O检测，防止在SQL Server错误日志中记录由CPU漂移引起SQL Server错误日志中的错误

Additional research:

其他研究：

Scope: Not documented

范围：未记录

834 (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.

应用程序：通过允许SQL Server内存管理器为缓冲池分配Windows的大页面来提高x64系统的性能，从而提高了性能。这提高了转换后备缓冲器（TLB）的效率。

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.

注意：对于启用了“列存储索引”功能SQL Server 2012、2014和2016，建议不要打开此SQL Server跟踪标志。

Additional research:

其他研究：

Scope: Global

范围：全球

836 (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.

应用程序：如果在SQL Server启动时启用了此SQL Server跟踪标志，则它将强制缓冲池的缩放取决于最大服务器内存值选项，而不是取决于最大物理内存大小。在这种情况下，SQL Server跟踪标志会减少在服务器启动时以32位地址窗口扩展（AWE）模式移动的缓冲区描述符数目。

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

注意：此SQL Server跟踪标志仅对启用AWE分配的32位版本SQL Server有效。

Additional research:

其他研究：

Scope: Global

范围：全球

839 (839)

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

应用程序：强制缓冲池将所有NUMA内存视为一个节点（平面内存模型）。

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

其他研究： 工作原理：软NUMA，I / O完成线程，懒惰的编写器工作程序和内存节点

Scope: Global

范围：全球

840 ( 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

总结了《 SQL Server跟踪标志》指南的这一部分。请参阅目录以获取更多文章