掌握Trace Flag是一个成为SQL,默认追踪的ID是1

作者: www.9159.com  发布:2019-11-06

跟踪标记:1204/1222

SQL追踪(SQL Trace)是一个轻量级的追踪工具,按照事件(Events)记录数据库发生的消息,几乎对数据库性能没有什么影响。SQL Server内置一个Trace,称作默认追踪(Default Trace),默认追踪的ID是1,大家经常使用的SQL Server Profiler,就是利用SQL Trace记录数据库活动的一个工具。SQL Trace在SQL Server数据库引擎种出现的比较早,可以被性能和功能更卓越的扩展事件(Extended Events)取代。

跟踪标记是什么?

功能及用途:

一,查看默认追踪是否启用

    对于DBA来说,掌握Trace Flag是一个成为SQL Server高手的必要条件之一,在大多数情况下,Trace Flag只是一个剑走偏锋的奇招,不必要,但在很多情况下,会使用这些标记可以让你更好的控制SQL Server的行为。

捕获SQL Server死锁信息,并自动存放到错误日志(ERRORLOG)中。

默认追踪是系统内置的,TraceID是1,默认是开启的,可以通过系统配置表 sys.configurations 进行查看,配置项ID(configuration_id)是1568:

下面是官方对于Trace Flag的标记:

 

www.9159.com 1

    跟踪标记是一个标记,用于启用或禁用SQL Server的某些行为。

举例:

字段 value=1,表示Default Trace是开启的。

    由上面的定义不难看出,Trace Flag是一种用来控制SQL Server的行为的方式。很多DBA对Trace Flag都存在一些误区,认为只有在测试和开发环境中才有可能用到Trace Flag,这种想法只能说部分正确,因此对于Trace Flag可以分为两类,适合在生产环境中使用的和不适合在生产环境中使用的。

USE tempdb
GO 

CREATE TABLE t1(id int)
INSERT t1 SELECT 1

CREATE TABLE t2(id int)
INSERT t2 SELECT 1
GO 

--开启1204/1222跟踪标记(任何查询窗口)
DBCC TRACEON(1204,-1)
--DBCC TRACEON(1205,3605,-1)
--DBCC TRACEON(1206,3605,-1)

--DBCC TRACEON(1222,-1)
GO 

--查询窗口1
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN
    UPDATE t1 SET id = 0 WHERE id=1
    WAITFOR DELAY '00:00:05'
    UPDATE t2 SET id = 0 WHERE id=1
--COMMIT TRAN
GO 

--查询窗口2
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

--死锁优先级为low,将被选择作为牺牲品
SET DEADLOCK_PRIORITY LOW 

BEGIN TRAN
    UPDATE t2 SET id = 0 WHERE id=1
    UPDATE t1 SET id = 0 WHERE id=1
--COMMIT TRAN
GO 

--关闭1204/1222跟踪标记(任何查询窗口)
DBCC TRACEOFF(1204,-1)
--DBCC TRACEOFF(1205,3605,-1)
--DBCC TRACEOFF(1206,3605,-1)

--DBCC TRACEOFF(1222,-1)
GO 

--查看错误日志里的死锁信息
exec xp_readerrorlog 0,1

DROP TABLE t1,t2

二,禁用或启用默认追踪

    Important:Trace Flag属于剑走偏锋的招数,在使用Trace Flag做优化之前,先Apply基本的Best Practice。

 

如果默认追踪被禁用,需要重新配置启用默认追踪:

如何控制跟踪标记

跟踪标记1204 在错误日志中记录的死锁信息

exec sp_configure 'show advanced options' , 1 ;
go
reconfigure;
go

exec sp_configure 'default trace enabled' , 1 ;
go
reconfigure;
go

    控制跟踪标记的方式有以下三种:

DBCC TRACEON 1204, server process ID (SPID) 56. This is an informational message only; no user action is required.
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1

RID: 2:1:624:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 1:
   Owner:0x00000034DF20D840 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:56 ECID:0 XactLockInfo: 0x00000034DDE34440
   SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 9
   Input Buf: Language Event: 
--查询窗口1
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN
    UPDATE t1 SET id = 0 WHERE id=1
    WAITFOR DELAY '00:00:05'
    UPDATE t2 SET id = 0 WHERE id=1
--COMMIT TRAN

Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x00000034E122B2B8 Mode: U SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000034BB418870) Value:0xdf20d040 Cost:(5/224)
NULL
Node:2

RID: 2:3:944:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 1:
   Owner:0x00000034DF20D980 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:58 ECID:0 XactLockInfo: 0x00000034E122B2F0
   SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 8
   Input Buf: Language Event: 
--查询窗口2
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

BEGIN TRAN
    UPDATE t2 SET id = 0 WHERE id=1
    UPDATE t1 SET id = 0 WHERE id=1
--COMMIT TRAN
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x00000034DDE34408 Mode: U SPID:56 BatchID:0 ECID:0 TaskProxy:(0x00000034BB1DC870) Value:0xdf20ec00 Cost:(0/224)
NULL
Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x00000034E122B2B8 Mode: U SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000034BB418870) Value:0xdf20d040 Cost:(5/224)
DBCC TRACEOFF 1204, server process ID (SPID) 56. This is an informational message only; no user action is required.

如果默认追踪已经启用,可以重新配置禁用默认追踪:

1.通过DBCC命令

 

exec sp_configure 'default trace enabled' , 0 ;
go
reconfigure;
go

exec sp_configure 'show advanced options' , 0 ;
go
reconfigure;
go

    可以通过DBCC命令来启用或关闭跟踪标记,这种方式的好处是简单易用,分别使用下面三个命令来启用,禁用已经查看跟踪标记的状态:

跟踪标记1222 在错误日志中记录的死锁信息

三,查看默认追踪的信息

DBCC TRACEON(2203,-1) DBCC TRACEOFF(2203,1) DBCC TRACESTATUS

DBCC TRACEON 1222, server process ID (SPID) 56. This is an informational message only; no user action is required.
deadlock-list
 deadlock victim=process34d71fc4e8
  process-list
   process id=process34d71fc4e8 taskpriority=5 logused=224 waitresource=RID: 2:1:624:0 waittime=3012 ownerId=1281992 transactionname=user_transaction lasttranstarted=2016-10-12T10:11:24.513 XDES=0x34dde34408 lockMode=U schedulerid=2 kpid=5736 status=suspended spid=58 sbid=0 ecid=0 priority=-5 trancount=2 lastbatchstarted=2016-10-12T10:11:24.513 lastbatchcompleted=2016-10-12T10:11:12.760 lastattention=2016-10-12T10:10:46.323 clientapp=Microsoft SQL Server Management Studio - Query hostname=CHRZHANG hostpid=6924 loginname=PCLC0chrzhang isolationlevel=read committed (2) xactid=1281992 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=adhoc line=9 stmtstart=38 stmtend=116 sqlhandle=0x020000006f1dc606af4ee82be297cae142d4eca1b1b26d840000000000000000000000000000000000000000
unknown     
     frame procname=adhoc line=9 stmtstart=324 stmtend=384 sqlhandle=0x02000000c9c0f33adb0fe790eb6b0e4c7175f9f4b5931a970000000000000000000000000000000000000000
unknown     
    inputbuf
--查询窗口2
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
BEGIN TRAN
    UPDATE t2 SET id = 0 WHERE id=1
    UPDATE t1 SET id = 0 WHERE id=1
--COMMIT TRAN
   process id=process34ddc6a4e8 taskpriority=0 logused=224 waitresource=RID: 2:3:944:0 waittime=1071 ownerId=1281979 transactionname=user_transaction lasttranstarted=2016-10-12T10:11:21.440 XDES=0x34e122b2b8 lockMode=U schedulerid=2 kpid=968 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-10-12T10:11:21.440 lastbatchcompleted=2016-10-12T10:11:04.287 lastattention=1900-01-01T00:00:00.287 clientapp=Microsoft SQL Server Management Studio - Query hostname=CHRZHANG hostpid=6924 loginname=PCLC0chrzhang isolationlevel=read committed (2) xactid=1281979 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=adhoc line=8 stmtstart=38 stmtend=116 sqlhandle=0x0200000083cd4832d993ca3b2dcaae9f9cc70a25e718dbe90000000000000000000000000000000000000000
unknown     
     frame procname=adhoc line=8 stmtstart=326 stmtend=386 sqlhandle=0x02000000ff6a84274c6888c86c481ae84300231e8f5fb8000000000000000000000000000000000000000000
unknown     
    inputbuf
--查询窗口1
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
    UPDATE t1 SET id = 0 WHERE id=1
    WAITFOR DELAY '00:00:05'
    UPDATE t2 SET id = 0 WHERE id=1
--COMMIT TRAN
  resource-list
   ridlock fileid=1 pageid=624 dbid=2 objectname=tempdb.dbo.t1 id=lock34d8ea3200 mode=X associatedObjectId=2017612634171244544
    owner-list
     owner id=process34ddc6a4e8 mode=X
    waiter-list
     waiter id=process34d71fc4e8 mode=U requestType=wait
   ridlock fileid=3 pageid=944 dbid=2 objectname=tempdb.dbo.t2 id=lock34d8ea3780 mode=X associatedObjectId=2089670228250132480
    owner-list
     owner id=process34d71fc4e8 mode=X
    waiter-list
     waiter id=process34ddc6a4e8 mode=U requestType=wait
DBCC TRACEOFF 1222, server process ID (SPID) 56. This is an informational message only; no user action is required.

默认追踪记录的数据存储在文件中,可以从系统视图 sys.traces查看文件的路径,文件的大小(Size)和文件的更新方式等信息,追踪文件默认的扩展名是 .trc。

其中,TRACEON和TRACEOFF第二个参数代表启用标志的范围,1是Session Scope,-1是Global Scope,如果不指定该值,则保持默认值Session Scope。

 

select id
    ,iif(status=1,'running','stopped') as status
    ,path
    ,max_size
    ,start_time
    ,stop_time
    ,event_count
    ,max_files
    ,is_rowset
    ,is_rollover
    ,is_shutdown
    ,is_default
    ,buffer_count
    ,buffer_size as each_buffer_size
from sys.traces
where id=1

另外,值得说的是,如果你希望在每次SQL Server服务启动时通过DBCC命令控制某些Flag,则使用

小结:

默认追踪有5个跟踪文件,每一个文件的最大size默认是20MB,SQL Server负责维护这5个文件,当实例重启的时候或者到达文件Size最大值的时候,SQL Server创建新的文件,将最早创建的跟踪文件删除,依次滚动(Rollover)更新。

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

(1) 不需要加跟踪标记3605,跟踪标记1204,1222捕获的死锁信息便会被写到错误日志;

四,查看追踪文件的内容

这个存储过程来指定,sp_procoption存储过程会在SQL Server服务器启动时自动执行。

(2) 跟踪标记 1222以XML样式返回死锁信息,相比跟踪标记1204,返回的信息也更为丰富;

函数sys.fn_trace_gettable,用于从追踪文件中读取数据,以关系表的格式显式:

       还有一点值得注意的是,不是所有的跟踪标记都可以用DBCC命令启动,比如Flag 835就只能通过启动参数指定。

(3) 无文档记载的跟踪标记1205,1206,据说可以用来丰富1204捕获死锁的信息,在SQL Server 2016下简单测试同时开启1204,1205,1206,和单独开启1204并没发现有什么不同,个人猜测是随着版本更替,跟踪标记捕获的死锁信息已经被整合,直接用1204或1222即可;

sys.fn_trace_gettable ( 'filename' , number_files )

2.通过在SQL Server配置管理器中指定

(4) 从SQL Server 2008开始,引进了扩展事件(Extended Events),也可以用来捕获死锁信息。

参数filename:用于指定追踪文件的名称,其值可以从系统视图sys.traces 中的path获取;

这种方式是通过在数据库引擎启动项里加启动参数设置,只有Global Scope。格式为-T#跟踪标记1;T跟踪标记2;T跟踪标记3。

 

参数number_files:如果number_files 被指定为default,函数读取所有的滚动文件。

3.通过注册表启动

参考:

函数返回的是关系表,有效字段是:追踪关联的事件绑定的字段,

     这种方式和方法2大同小异,就不多说了。

Tracing a SQL Server Deadlock

select * 
from sys.fn_trace_gettable(N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLLoglog_4.trc',default)

一些在生产环境中可能需要的跟踪标记

五,查看默认追踪记录的事件列表

Trace Flag 610

 

函数fn_trace_geteventinfo(trace_id)返回追踪关联的事件列表,使用该函数可以查看默认追踪记录的事件和事件的特定字段:

减少日志产生量。如果你对于日志用了很多基础的best practice,比如说只有一个日志文件、VLF数量适当、单独存储,如果还是不能缓解日志过大的话,考虑使用该跟踪标记。

Trace Flags - SQL Server Wiki - SQL Server - Toad World

select categ.name as category,
    te.trace_event_id as event_id, 
    te.name as event_name,
    tc.trace_column_id as event_column_id,
    tc.name as column_name,
    tc.type_name as column_type
from sys.fn_trace_geteventinfo(1) as gei
inner join sys.trace_columns tc 
    on gei.columnid=tc.trace_column_id
inner join sys.trace_events te 
    on gei.eventid=te.trace_event_id
inner join sys.trace_categories categ
    on te.category_id=categ.category_id
order by category,event_id,event_column_id

参考资料:

六,查看事件和Category

http://msdn.microsoft.com/en-us/library/dd425070.aspx

 

Category用于组织事件(Event),是事件的分组,在SQL Server 2012中,共有21个Category,180个Event,每个Event属于唯一的一个Category。

Using SQL Server 2008 Extended Events

select tc.name as category,
    te.trace_event_id as event_id,
    te.name as event_name
from sys.trace_categories tc 
inner join sys.trace_events te 
    on tc.category_id=te.category_id
order by category,event_id
Trace Flag 834

七,查看事件绑定的字段

使用 Microsoft Windows 大页面缓冲池分配。如果服务器是SQL Server专用服务器的话,值得开启该跟踪标记。

 

在SQL Server 2012中,事件共有66个字段,但不是每个Event都能绑定所有的66个字段,每个Event能够绑定的字段是固定的,系统预先设置,用户不能修改,视图 sys.trace_event_bindings 用于显示每个事件绑定的字段。

Trace Flag 835
select te.trace_event_id as event_id, 
    te.name as event_name,
    tc.trace_column_id as column_id,
    tc.name as column_name,
    tc.type_name as column_type
from sys.trace_event_bindings teb 
inner join sys.trace_columns tc 
    on teb.trace_column_id=tc.trace_column_id
inner join sys.trace_events te 
    on teb.trace_event_id=te.trace_event_id
order by event_id,column_id

允许SQL Server 2005和2008标准版使用"锁定内存页",和在组策略中设置的结果大同小异,但是允许在标准版中使用.

八,使用SQL Server Profiler创建SQL Trace

Trace Flag 1118

tempdb分配整个区,而不是混合区,减少SGAM页争抢。

当apply tempdb的best practice之后,还遇到争抢问题,考虑使用该跟踪标记。

参考资料:

如果用户需要创建自定义的追踪,那么可以使用系统提供的存储过程来实现,但是,使用TSQL代码创建追踪的过程十分繁琐,代码量庞大,整个过程不直观。大家知道,SQL Server Profiler是一个可视化用于查看数据库活动的工具,同时,它也是一个用于创建SQL Trace的工具。使用SQL Server Profiler创建SQL Trace的过程十分简单:选择相应的事件和事件的字段之后,导出SQL Trace  的定义即可。

在创建SQL Trace之后,点击File->Export->Scipt Trace Definition,把SQL Server Profiler用于创建SQL Trace的脚本代码导出:

Trace Flag 1204和1222

这两个跟踪标记都是将死锁写到错误日志中,不过1204是以文本格式进行,而1222是以XML格式保存。可以通过

sp_readerrorlog查看日志。

Trace Flag 1211和1224

www.9159.com 2

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

导出的脚本如下,不能直接使用,必须修改一处代码:在创建Trace时,指定存储追踪数据的文件(File) 或 关系表(Table),仅此而已。

Trace Flag 2528

www.9159.com 3www.9159.com 4

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

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Client side File and Table cannot be scripted
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on

--delete many commands here ---

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%drop%'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go
Trace Flag 3226  

View Code

防止日志记录成功的备份。如果日志备份过于频繁的话,会产生大量错误日志,启用该跟踪标记可以使得日志备份不再被记录到错误日志。

 

Trace Flag 4199

所有KB补丁对于查询分析器行为的修改都生效,这个命令比较危险,可能扫称性能的下降,具体请参看:

http://support.microsoft.com/kb/974006

注:SQL Trace是被扩展事件取代的功能,在后续的版本中将会被移除,建议在以后的开发中使用扩展事件。

不应该在生产环境中启用的跟踪标记 

 

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 ServerMSSQLdatapubs.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 TRACESTATUSSELECT * 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行为的一种方式,对于某些跟踪标志来说,可以在生产环境中提高性能,而对于另一些来说,用在生产环境中是一件非常危险的事情,只有在测试环境中才能被使用。要记住,跟踪标记对于调优是一种剑走偏锋的手段,只有在使用了所有基本的调优手段之后,才考虑使用跟踪标记。

参考文档:

对于DBA来说,掌握Trace Flag是一个成为SQL Server高手的必要条件之一,在大多数情况下,Trace Flag只是一个剑走偏锋的奇招,...

SQL Trace

Server-wide Configuration Catalog Views (Transact-SQL).aspx)

System Trace Functionswww.9159.com,.aspx)

SQL Server 默认跟踪(Default Trace)

SQL Server中关于跟踪(Trace)那点事

本文由9159.com发布于www.9159.com,转载请注明出处:掌握Trace Flag是一个成为SQL,默认追踪的ID是1

关键词:

上一篇:  --查看表结构,一、修改字段默认值
下一篇:没有了