数据库教程19:这可能是你了解SQL Server跟踪标记的最正确的姿势

855 阅读8分钟

这是我参与8月更文挑战的第19天,活动详情查看:8月更文挑战

关于SQL Server跟踪标记(Trace Flags)的资料不是很多,通常用到它的时候比较少。但是对于完成和修改一些特定的数据库行为,跟踪标记能起到很好的作用。

什么是跟踪标记

对于比较陌生的Trace Flags,首先我们应该了解跟踪标记是什么?以及是做什么用的?

SQL Server跟踪标志用于更改数据库引擎的某些行为,它用于设置特定的服务器特征或修改特定的行为

简单说就是,SQL Server跟踪标志或标记,就是一些标记,使用它们可以启用或关闭数据库引擎的某些功能

例如,标志3226是一个常用的启动跟踪标记,用于取消错误日志中的成功备份消息。

跟踪标记常用于诊断性能问题,或调试存储过程、复杂的计算机系统,还可以解决特定工作负载产生消极影响的行为。

跟踪标记非常适合改进SQL Server,但某些情况下,未必会提供预期的好处,设置会产生不利的影响和其他的问题。所以,SQL Server中的跟踪标记需要慎重使用

使用某个标记时,必须首先在相同的环境下测试好。否则容易产生轻微的风险。

特定用途的跟踪标记,可能会有一些注意事项,一定要仔细确认再使用。

官方文档中记录的跟踪标记和Microsoft支持人员推荐的跟踪标记,都在生产环境中得到了完全支持。

关于标志3226

默认情况下,每个成功的备份操作都会在SQL Server错误日志和系统事件日志中添加一个条目。如果你非常频繁地执行备份,则日志将积累得非常快,导致查找其他消息时需要处理大量的错误日志。

如果频繁地日志备份并且没有脚本依赖这些记录操作的条目,可以使用标记3226取消记录备份的错误日志条目。

启用和禁用跟踪标记

有两种方式启用跟踪标记:

  • 使用DBCC TRACEONDBCC TRACEOFF。可以不必重启实例就能使用跟踪标记。
  • 在启动SQL Server服务的启动参数中使用-T标识号

DBCC TRACEON、DBCC TRACEOFF

使用T-SQL的DBCC TRACEON,你可以在session或全局启动跟踪标记。大多数情况下都是要在整个实例中使用TF。

如下,在全局启动跟踪标记3226。

DBCC TRACEON (3226, -1);

禁用3266跟踪标记

DBCC TRACEOFF (3226, -1);

使用DBCC TRACEONDBCC TRACEOFF的优势是:可以不必重启实例就能使用跟踪标记。缺点是任何sysadmin成员并运行DBCC TRACEOFF的人都可以禁用它,并且它不会在重新启动后持续存在

建议在测试跟踪标志时使用此方法。

可以借助sp_procoption在启动SQL Server实例时自动执行存储过程,然后在过程中DBCC TRACEON启动相关的标记,做到重启服务后标记仍有效。

EXEC sp_procoption @ProcName = '<procedure name>' 
   , @OptionName = ] 'startup' 
   , @OptionValue = 'on'; 

启动参数中启用TF

当需要将跟踪标记作为启动参数(startup parameter)添加到SQL Server服务中时,需要使用SQL Server配置管理器(SQL Server Configuration Manager——SSCM)。

如下,在SSCM中右键SQL Server实例服务,选择"属性",然后切换到"启动参数"tab页(Startup Parameters)。使用-T跟踪标记添加一个启动参数。-T和跟踪标记之间没有空格

添加后点击确定,并重启实例服务。

查看当前已启动的跟踪标记

使用DBCC TRACESTATUS可以检测当前实例启动的跟踪标记。

  • 查看启动的所有跟踪标记的状态
DBCC TRACESTATUS;

  • 查看启动的所有全局跟踪标记的状态
DBCC TRACESTATUS(-1);
  • 查看指定跟踪标记的状态,而不管是否启用
DBCC TRACESTATUS (8690, 1118);

推荐用于安装的跟踪标记

有三个跟踪标记推荐用于SQL Server安装:

  • 1118(适用于 SQL Server 2016 之前的版本)
  • 3023(适用于 SQL Server 2014 之前的版本)
  • 3226

TF 1118

跟踪标志 1118 解决了可能存在于数据库中特定类型的页上的争用,即SGAM页面。此跟踪标志通常为大量使用tempdb系统数据库的客户提供好处。在SQL Server 2016中,可以使用MIXED_PAGE_ALLOCATION数据库选项更改此行为,并且不需要TF 1118。

TF 3023

跟踪标志 3023 用于为实例上的所有备份默认启用CHECKSUM选项。启用此选项后,将在备份期间验证页面校验和,并生成整个备份的校验和。从SQL Server2014 开始,可以通过sp_configure(backup checksum default)在实例范围内设置此选项。

TF 3226

跟踪标志 3226 阻止将成功的备份消息写入SQL Server的ERRORLOG。有关成功备份的信息仍会写入msdb,并且可以使用 T-SQL进行查询。对于具有多个数据库和常规事务日志备份的服务器,启用此选项意味着ERRORLOG不再因BACKUP DATABASE和数据库备份消息而变得臃肿。作为一名DBA,这是一件好事,因为当我查看我的ERRORLOG时,我真的只想看到错误,我不想滚动浏览成百上千个关于成功备份的条目。

建议

在可能的情况下,首先要确认在环境中看到的内容与跟踪标志描述的行为相匹配。如果认为应该启用跟踪标志,请首先在测试或开发环境中启用它,你可以在那里重新创建问题,然后对其进行全面测试。最后,经过严格的测试后,才可以在生产中进行尝试(即使如此,也可能不是适合的正确解决方案)。

Trace Flags是一种高级 QL Server机制,允许深入挖掘隐藏的高级SQL Server功能,以确保更有效地进行故障排除和调试、高级监控SQL Server行为和诊断性能问题,或者,打开和关闭各种SQL Server功能。跟踪标志应用作临时帮助,并且大多数标记不应长时间保持跟踪处于打开状态。

仅在SQL Server产品支持人员指示的情况下才应进行长时间或无限期的标志使用。

其他一些跟踪标记介绍

  • 3064 将DBCC的结果可以在客户端上显示出来
  • 3605 将DBCC的结果输出到错误日志。
  • 610 OLTP不建议开启,减少日志产生量。如果你对于日志用了很多基础的best practice,比如说只有一个日志文件、VLF数量适当、单独存储,如果还是不能缓解日志过大的话,考虑使用该跟踪标记。
  • 834 使用 Microsoft Windows 大页面缓冲池分配。如果服务器是SQL Server专用服务器的话,值得开启该跟踪标记。
  • 1118 tempdb分配整个区,而不是混合区,减少SGAM页争抢。
  • 1204和1222 这两个跟踪标记都是将死锁写到错误日志中,不过1204是以文本格式进行,而1222是以XML格式保存。可以通过sp_readerrorlog来查看错误日志。最佳实践:dbcc traceon (1204,1222,3605,-1);

避免在导致死锁的工作负载密集型系统上使用跟踪标志1204或1222。有关检测死锁的其他方法请参阅事务锁定和行版本控制指南

  • 1211和1224 两种方式都是禁用锁升级。但行为有所差别。1211是无论何时都不会锁升级,而1224在内存压力大的时候会启用锁升级,从而避免了out-of-locks错误。当两个跟踪标记都启用是,1211的优先级更高。

  • 2528 不建议开启,禁用并行执行DBCC CHECKDB, DBCC CHECKFILEGROUP,DBCC CHECKTABLE。这意味着这几个命令只能单线程执行,这可能会需要更多的时间,但是在某些特定情况下还是有些用处。

  • 2371 允许增加数据库内统计信息的更新频率(统计信息参考:www.cnblogs.com/gered/p/908…

  • 1200 返回加锁信息的整个过程,是学习加锁过程很牛逼的标志。 最佳实践:DBCC TRACEON(1200,3604,-1) ;dbcc tracestatus;

  • 3604 开启后,可以将DBCC Page的结果显示在客户端,否则就不会显示出来。

  • 3502 在日志中显示有关checkpoint的相关信息。

  • 3505 --不允许自动进行checkpoint,checkpoint只能手动进行,是非常危险的一个命令。

  • 3004:数据库还原详细信息记录在errorlog里面

  • 8602 停止索引提示功能

  • 8755 停止锁提示功能

  • 1117:同一个文件组内的多个文件插入数据的时候会同时增长,而不是只增长第一个数据文件

参考