Microsoft SQL Server 跟踪标志和使用细节

在这篇文章中，我们将探讨Microsoft SQL Server跟踪标志的各个方面，并且我们将学习如何使用它们。

简介

跟踪标志是高级配置选项，用于改变SQL Server的特定行为。追踪标志通常用于诊断Microsoft SQL Server的特定问题，但有时它们也可以成为解决与数据库引擎有关的特定问题的推荐做法。除此之外，一些跟踪标志可能会在SQL Server的新版本中被转化为一种功能。如，跟踪标志1117有助于解决一些特定情况下的tempdb数据库性能问题。然而，这个跟踪标志功能在Microsoft SQL Server 2016和更高版本中已经成为一个默认选项。

跟踪标志1117： Tempdb数据库数据文件与所有文件同时增长，因此每个文件都保持在同等大小。

Microsoft SQL Server 2016和更高版本的这个跟踪标志是作为tempdb数据库的一个默认设置来实现的。通过下面的查询，我们可以显示tempdb数据库的这个设置。

  USE tempdb
  GO
  SELECT
  DB_NAME() AS database_name
  ,[name]
  ,type_desc
  ,CASE WHEN is_autogrow_all_files = 0 THEN 'NO' ELSE 'YES'
   END AS is_autogrow_all_files
  FROM sys.filegroups

同时，我们不能改变tempdb数据库的这个设置。正如我们在下面看到的，当我们试图改变这个功能时，SQL Server将返回一个错误。

  ALTER DATABASE tempdb MODIFY FILEGROUP [PRIMARY]  AUTOGROW_SINGLE_FILE

关于标志的另一个显著特点是，有些标志可能没有被微软记录。在这种情况下，使用这些跟踪标志会对数据库的工作量产生负面影响，因此在非生产环境中测试这些标志将是一个好的做法。

追踪标志的范围

在Microsoft SQL Server中，可以通过不同的方法对不同的范围启用或禁用跟踪标志。我们可以通过命令手动启用以下范围的标志。

• 查询级
• 会话级
• 全局

如何启用查询级的跟踪标志

我们可以在查询中使用QUERYTRACEON查询提示来启用查询级的跟踪。默认情况下，查询优化器决定对以下查询使用自适应连接。

  SELECT  ProductID,SUM(LineTotal) AS LineTotal  ,
  SUM(UnitPrice) As TotalPrice , 
  SUM(UnitPriceDiscount) AS TotalPrice FROM 
  Sales.SalesOrderDetailEnlarged SOrderDet 
  INNER JOIN Sales.SalesOrderHeaderEnlarged  SalesOr
  ON SOrderDet.SalesOrderID = SalesOr.SalesOrderID
  GROUP  BY ProductID

追踪标志9398。 这个跟踪标志可以防止查询优化器使用自适应连接操作。这个跟踪标志可以在所有范围内启用。

现在，我们在查询中添加QUERYTRACEON提示，并启用9398标志。在这种情况下，优化器将放弃使用自适应连接操作。

 SELECT  ProductID,SUM(LineTotal) AS LineTotal  ,
 SUM(UnitPrice) As TotalPrice , 
 SUM(UnitPriceDiscount) AS TotalPrice FROM 
 Sales.SalesOrderDetailEnlarged SOrderDet 
 INNER JOIN Sales.SalesOrderHeaderEnlarged  SalesOr
 ON SOrderDet.SalesOrderID = SalesOr.SalesOrderID
 GROUP  BY ProductID
 OPTION(QUERYTRACEON 9398)

选择操作符包括TraceFlags属性，它列出了在执行过程中影响查询的跟踪标志。IsCompileTime属性显示为真，它意味着该标志在查询的编译过程中已经被启用。

在会话级别启用一个跟踪标志

我们可以使用DBCC TRACEON命令来启用会话级的标志。我们只在这个命令中传递跟踪标志的编号。下面的命令在会话级别上启用了一个跟踪标志。

DBCC TRACEON(9398)

当我们想禁用同一个标志时，我们需要在同一个会话上运行DBCC TRACEOFF命令。

DBCC TRACEOFF(9398)

在全局层面上启用一个跟踪标志

为了在全局层面上启用一个跟踪标志，我们需要把-1值作为第二个参数传入DBCC TRACEON命令。

DBCC TRACEON(9398,-1)

为了在全局层面上禁用同一个跟踪标志，我们可以执行下面的查询。

DBCC TRACEOFF(9398,-1)

当我们在全局级别启用一个标志来使用DBCC TRACEON语句时，它将一直有效，直到跟踪标志被禁用或重新启动Microsoft SQL Server。

在Microsoft SQL Server的启动参数中添加一个跟踪标志

在某些情况下，我们可以要求在数据库引擎启动时自动启用一个跟踪标志。在这种情况下，我们可以将跟踪标志添加到数据库启动参数中。我们可以通过使用SQL Server配置管理器来轻松实现跟踪标志的自动化。首先，我们将启动配置管理器，并在服务列表面板中右键点击SQL Server服务。在菜单中，我们选择属性。

我们点击启动参数，然后将带有-T开关的跟踪标志号码添加到指定启动参数文本框中。作为最后一步，我们点击应用按钮。

将跟踪标志添加到启动参数中后，它将在SQL Server服务重新启动后变得有效。

获取跟踪标志状态

DBCC TRACESTATUS命令帮助我们了解跟踪标志的状态。当我们用-1参数执行DBCC TRACESTATUS命令时，它将返回当前全局启用的所有跟踪标志的状态。

DBCC TRACESTATUS (-1)

TraceFlag列指定了跟踪标志的编号，其他列显示了特定级别中跟踪标志的状态。1值表示跟踪标志在启用，0值表示跟踪标志被禁用。当我们执行DBCC TRACESTATUS时，如果没有-1参数，它将返回当前会话的启用的跟踪标志。

监控跟踪标志的活动

Microsoft SQL Server扩展事件是一个轻量级的监控工具，为诊断问题提供了一个非常有效的解决方案。 trace_flag_changed 事件可以捕捉到跟踪标志状态的变化，因此我们可以使用这个事件来监控跟踪标志。首先，我们添加一个新的会话并给它一个名字。在常规选项卡中，我们点击下面的选项。

• 会话创建后立即启动事件会话
• 观察屏幕上的实时数据，因为它被捕获了

之后，我们点击事件标签，找到trace_flag_changed， 然后将其添加到选定的事件列表中。

点击配置按钮，选择需要的全局字段，然后点击确定。

• 客户端应用程序名称
• 数据库名称
• SQL文本

在数据存储标签上，我们选择event_file类型，然后点击确定。现在我们将为不同的作用域启用和禁用一个跟踪标志。

  DBCC TRACEON(9453);
  GO
  DBCC TRACEON(9495,-1);
  GO
  WAITFOR DELAY '00:00:01';
  GO
  DBCC TRACEOFF(9453);
  GO
  DBCC TRACEOFF(9495,-1);
  GO

正如我们所看到的，所有跟踪标志的变化都被我们的扩展事件会话所捕获。另一种监控跟踪标志的方法是读取SQL Server错误日志记录。当一个跟踪标志被启用时，SQL Server会为这些活动写一条日志记录。要读取Microsoft SQL Server的错误日志，我们可以使用 读取错误日志（sp_readerrorlog 过程。在以下查询的帮助下，我们可以过滤错误日志，只返回与跟踪标志相关的活动日志。

  EXEC sys.sp_readerrorlog 0, 1, N'DBCC TRACE'; 

监控查询级的跟踪标志使用活动可能与会话级和全局的有点不同。

当我们执行下面的查询时，由于Microsoft SQL Server 2019Batch Mode on Rowstore功能，它将以批处理模式处理这些行。

  SELECT  ProductID,SUM(LineTotal) AS LineTotal  ,
  SUM(UnitPrice) As TotalPrice , 
  SUM(UnitPriceDiscount) AS TotalPrice FROM 
  Sales.SalesOrderDetailEnlarged SOrderDet 
  INNER JOIN Sales.SalesOrderHeaderEnlarged  SalesOr
  ON SOrderDet.SalesOrderID = SalesOr.SalesOrderID
  GROUP  BY ProductID

现在，我们将为这个查询启用标志9453，然后批量模式的行处理将不被优化器使用。

  SELECT  ProductID,SUM(LineTotal) AS LineTotal  ,
  SUM(UnitPrice) As TotalPrice , 
  SUM(UnitPriceDiscount) AS TotalPrice FROM 
  Sales.SalesOrderDetailEnlarged SOrderDet 
  INNER JOIN Sales.SalesOrderHeaderEnlarged  SalesOr
  ON SOrderDet.SalesOrderID = SalesOr.SalesOrderID
  GROUP  BY ProductID
  OPTION(QUERYTRACEON 9453)

我们可以看到，跟踪标志的查询级别的使用已经被捕获。然而，在同一个查询的下一次执行中，任何事件都不会被捕获，因为将使用缓存的计划。监测查询级标志活动的另一种方法是使用缓存的查询计划。我们可以在缓存的查询计划中搜索'%OPTION%QUERYTRACEON%'模式，这样我们就可以找出哪些查询启用了标志。

    SELECT 
      databases.name,
    dm_exec_sql_text.text AS TSQL_Text,
    dm_exec_query_stats.creation_time, 
    dm_exec_query_stats.execution_count,
    dm_exec_query_stats.total_worker_time AS total_cpu_time,
    dm_exec_query_stats.total_elapsed_time, 
    dm_exec_query_stats.total_logical_reads, 
    dm_exec_query_stats.total_physical_reads, 
    dm_exec_query_plan.query_plan
  FROM sys.dm_exec_query_stats 
  CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
  CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
  INNER JOIN sys.databases
  ON dm_exec_sql_text.dbid = databases.database_id
  WHERE dm_exec_sql_text.text LIKE '%OPTION%QUERYTRACEON%'
  AND dm_exec_sql_text.text NOT LIKE ('%dm_exec_query_stats%')

总结

在这篇文章中，我们了解了Microsoft SQL Server跟踪标志的使用细节，以及如何启用或禁用不同范围的跟踪标志。