跟踪标记是什么?
对于DBA来说,掌握Trace Flag是一个成为SQL Server高手的必要条件之一,在大多数情况下,Trace Flag只是一个剑走偏锋的奇招,不必要,但有时会使用这些标记可以让你更好的控制SQL Server的行为。
下面是官方对于Trace Flag的介绍:
跟踪标记是一个标记,用于启用或禁用SQL Server的某些行为。
由上面的定义不难看出,Trace Flag是一种用来控制SQL Server的行为的方式。
跟踪标记有全局和会话两种影响方式:
- 全局方式在服务器范围有效,对服务器上每个连接都可见
- 会话方式只对执行命令的连接有效
如何控制跟踪标记
控制跟踪标记的方式有以下三种:
1. 通过DBCC命令
可以通过DBCC命令来启用或关闭跟踪标记,这种方式的好处是简单易用,分别使用下面三个命令来启用,禁用已经查看跟踪标记的状态:
开启括号内列出的所有跟踪标记
DBCC TRACEON(trace#[,…n],-1|1)
例如:DBCC TRACEON(2203,1)
关闭括号内列出的所有跟踪标记
DBCC TRACEOFF(trace#[,…n],-1|1)
例如:DBCC TRACEOFF(2203,-1)
查看括号内列出的所有跟踪标记当前值
若省略参数列表,则默认显示当前会话中处于开启状态的所有跟踪标记
DBCC TRACESTATUS (trace#[,…n],-1|1)
DBCC TRACESTATUS
其中,1是Session范围(默认值),-1是Global范围;如果不指定,则保持默认值Session范围。
另外,如果你希望在每次SQL Server服务启动时通过DBCC命令控制某些Flag,则使用
EXEC sp_procoption @ProcName = '<procedure name>'
, @OptionName = ] 'startup'
, @OptionValue = 'on';
这个存储过程来指定,sp_procoption存储过程会在SQL Server服务器启动时自动执行。
还有一点值得注意的是,不是所有的跟踪标记都可以用DBCC命令启动,比如Flag 835就只能通过启动参数指定。
2. 在SQL Server启动时指定
这种方式是通过在数据库引擎启动项里加启动参数设置,只有Global Scope。格式为-T#跟踪标记1;T跟踪标记2;T跟踪标记3。
使用sqlservr.exe来启动
先cd切换到sqlservr.exe目录
默认实例:sqlservr.exe –T610
命名实例:sqlservr.exe -sMSSQL2016 –T610
注:
(1) 以这种方式启动的SQL Server,服务状态仍然是停止,但是SSMS/SQLCMD均可以连接并执行SQL语句,通常用于系统故障诊断/维护时,不作为常规启动方式使用;
(2) 关闭当前的cmd窗口,即可关闭该次启动;
使用net命令来启动
启动服务(以管理员身份运行cmd命令窗口)
默认实例:net start MSSQLSERVER /T610 或net start "SQL Server (MSSQLSERVER)" /T610
命名实例:net start MSSQL$MSSQL2016 /T610 或net start "SQL Server (MSSQL2016)" /T610
3. 使用配置管理器 (Configuration Manager) 来启动
打开SQL Server 配置管理器\SQL Server 服务\选择相应实例:
(1) 停止相应实例的服务
(2) 属性\启动参数, 输入-T610 并添加
(3) 启动相应实例的服务
注:
(1) 启动参数被保存在注册表里,在服务启动时加载,所以会一直有效,直到在启动参数中删除该项并保存;
(2) 在配置管理器中修改SQL Server服务相关的参数为最佳实践(Best Practice),推荐;
4. 使用注册表启动
这种方式和方法3大同小异,就不多说了。
一些在生产环境中可能需要的跟踪标记
Trace Flag 610
减少日志产生量。如果你对于日志用了很多基础的best practice,比如说只有一个日志文件、VLF数量适当、单独存储,如果还是不能缓解日志过大的话,考虑使用该跟踪标记。
http://msdn.microsoft.com/en-us/library/dd425070.aspx http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging-for-sql-server-2008.aspx
Trace Flag 834
使用 Microsoft Windows 大页面缓冲池分配。如果服务器是SQL Server专用服务器的话,值得开启该跟踪标记。
Trace Flag 835
允许SQL Server 2005和2008标准版使用"锁定内存页",和在组策略中设置的结果大同小异,但是允许在标准版中使用.
Trace Flag 1118
在对表或索引分配空间时,从开始就直接分配专用去,而不在混合区中分配前8个数据页,减少SGAM页争抢。当apply tempdb的best practice之后,还遇到争抢问题,考虑使用该跟踪标记。 http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx
Trace Flag 1204和1222
这两个跟踪标记都是将死锁写到错误日志中,不过1204是以文本格式进行,而1222是以XML格式保存。可以通过sp_readerrorlog查看日志。
Trace Flag 1211和1224
两种方式都是禁用锁升级。但行为有所差别1211是无论何时都不会锁升级,而1224在内存压力大的时候会启用锁升级,从而避免了out-of-locks错误。当两个跟踪标记都启用是,1211的优先级更高。
Trace Flag 2528
禁用并行执行DBCC CHECKDB, DBCC CHECKFILEGROUP,DBCC CHECKTABLE。这意味着这几个命令只能单线程执行,这可能会需要更多的时间,但是在某些特定情况下还是有些用处。
Trace Flag 2588
开启后有两个效果:
- 执行dbcc help('?')时,可以得到包括非公开命令在内的dbcc命令完整列表
- 已未公开dbcc命令的名称为参数执行dbcc help时,可以得到其语法相关帮助信息
Trace Flag 3226
防止日志记录成功的备份。如果日志备份过于频繁的话,会产生大量错误日志,启用该跟踪标记可以使得日志备份不再被记录到错误日志。
Trace Flag 3604
执行dbcc page命令时将输出显示在客户端,若未开启此标记,则输出显示在错误日志中
Trace Flag 4199 所有KB补丁对于查询分析器行为的修改都生效,这个命令比较危险,可能导致性能的下降,具体请参看:http://support.microsoft.com/kb/974006
不应该在生产环境中启用的跟踪标记
Trace Flag 806 在读取过程中对页检查逻辑一致性,在错误日志中就可以看到类似下面的信息: 2004-06-25 11:29:04.11 spid51 错误: 823,严重性: 24 日状态: 2 2004-06-25 11:29:04.11 spid51 I/O 错误 (审核失败) 在读取过程中检测到的偏移量主题 SQL Server\MSSQL\data\pubs.mdf e:\Program 文件中的 0x000000000b0000. 该跟踪标记会极大的降低性能!! http://support.microsoft.com/kb/841776
Trace Flag 818
检查写一致性
跟踪标志 818 启用了一个内存中的环形缓冲区,用于跟踪由运行 SQL Server 的计算机执行的最后 2,048 个成功写操作(不包括排序和工作文件 I/O)。发生 605、823 或 3448 之类的错误时,将传入缓冲区的日志序列号 (LSN) 值与最新写入列表进行比较。如果在读操作期间检索到的 LSN 比在写操作期间指定的更旧,就会在 SQL Server 错误日志中记录一条新的错误信息。大部分 SQL Server 写操作以检查点或惰性写入形式出现。惰性写入是一项使用异步 I/O 操作的后台任务。环形缓冲区的实现是轻量的,因此对系统性能的影响可以忽略。 http://support.microsoft.com/kb/826433
Trace Flag 1200
返回加锁信息的整个过程,是学习加锁过程很牛逼的标志,示例代码如下: DBCC TRACEON(1200,-1) DBCC TRACEON(3604) DBCC TRACESTATUS SELECT * FROM AdventureWorks.person.Address http://stackoverflow.com/questions/7449061/nolock-on-a-temp-table-in-sql-server-2008
Trace Flag 1806 禁用即时文件初始化,所有的磁盘空间请求全部使用填0初始化,可能造成在空间增长时产生阻塞。
Trace Flag 3502 在日志中显示有关checkpoint的相关信息。如图1所示。
图1.在错误日志中显示Checkpoint
Trace Flag 3505
不允许自动进行checkpoint,checkpoint只能手动进行,是非常危险的一个命令。
小结
跟踪标志是控制SQL Server行为的一种方式,对于某些跟踪标志来说,可以在生产环境中提高性能,而对于另一些来说,用在生产环境中是一件非常危险的事情,只有在测试环境中才能被使用。要记住,跟踪标记对于调优是一种剑走偏锋的手段,只有在使用了所有基本的调优手段之后,才考虑使用跟踪标记。
参考
https://www.cnblogs.com/CareySon/archive/2013/04/23/3039284.html
https://www.cnblogs.com/seusoftware/p/5711207.html
https://www.cnblogs.com/lyhabc/articles/3816529.html
《sqlserver 2008数据库技术内幕》