SQL Server中有关追踪(Trace)那点事(转发)

(Trace)走进数据库背后,我相信如用过SQL Server数据库的人,(Trace)走进数据库背后,我相信如用过SQL Server数据库的人,SQL Trace在SQL,SQL追踪(SQL,对于SQL Profiler这个工具相信大家都不是很陌生,这个工具是用来监控SQL

前言

前言

SQL追踪(SQL
Trace)是二个轻量级的寻踪工具,根据事件(伊芙nts)记录数据库发生的新闻,大概对数据库质量未有何样震慑。SQL
Server内置贰个Trace,称作暗中认可追踪(Default
Trace),暗许追踪的ID是1,我们平常应用的SQL Server Profiler,正是利用SQL
Trace记录数据库活动的八个工具。SQL Trace在SQL
Server数据库引擎种出现的可比早,能够被品质和职能更独立的恢弘事件(Extended
伊夫nts)代替。

一.What is Trace?

对此SQL Profiler这几个工具相信我们都不是很面生,没用过的仇人能够在SQL
Server Management Studio>工具>SQL Server
Profiler处使用。这一个工具是用来监督SQL,存款和储蓄进度的执行,顾客登入等等新闻。但以此工具只是贰个GUI,他的面目便是Trace。上面是Trace的架构:

金沙国际唯一官网网址 1  

数据库引擎会时有爆发一文山会海事件,然后逐个trace能够去订阅自身感兴趣的风云,一旦数据库发生了连带事件就能发放订阅该事件的trace,种种trace通过投机的过滤器对该事件的讯息过滤(例如:捕获实施时间超越1秒的SQL语句),然后放到二个缓冲队列里,最终写入文件或然是有个别客商端应用。

大概介绍完理论,上面就最初推行:

一提到追踪俩字,很几个人想到警察匪徒片中的场景,一样在我们的SQL
Server数据库中“追踪”也是无处不在的,若是大家应用好了追踪技能,就能够针对有个别特定的光景做定向深入分析,搜索丰盛的证据来破案。

一提到追踪俩字,很几个人想到警察匪徒片中的场景,同样在大家的SQL
Server数据库中“追踪”也是无处不在的,假诺大家选拔好了追踪才干,就可以针对少数特定的场地做定向分析,搜索充裕的凭证来破案。

一,查看私下认可追踪是不是启用

How to Create a Trace?

要创制三个追踪器,总共分三步:

1.实践存储进程sp_trace_create创制贰个追踪器

2.实践存款和储蓄进度sp_trace_setevent增添本身想订阅的风云以及最终结果集的列名

3.进行存款和储蓄进度sp_trace_setfilter设置过滤器来对过滤发生多少

下边是一个创办四个追踪器的SQL脚本

 1 DECLARE @return_code INT;
 2 DECLARE @TraceID INT;
 3 DECLARE @maxfilesize BIGINT;
 4 SET @maxfilesize = 5;
 5 --step 1: create a new empty trace definition
 6 EXEC sp_trace_create
 7                 @traceid OUTPUT
 8                , @options = 2
 9                , @tracefile = N'C:\TraceFiles\LongRunningQueries'
10                , @maxfilesize = @maxfilesize
11     , @stoptime =NULL
12     , @filecount = 2; 
13 -- step 2: add the events and columns
14 EXEC sp_trace_setevent
15                 @traceid = @TraceID
16                , @eventid = 10 -- RPC:Completed
17                , @columnid = 1 -- TextData
18                , @on = 1;--include this column in trace
19 EXEC sp_trace_setevent
20                 @traceid = @TraceID
21                , @eventid = 10 -- RPC:Completed
22                , @columnid = 13 --Duration
23                , @on = 1;--include this column in trace
24 EXEC sp_trace_setevent
25                 @traceid = @TraceID
26                , @eventid = 10 -- RPC:Completed
27                , @columnid = 15 --EndTime
28                , @on = 1;--include this column in trace  
29 EXEC sp_trace_setevent
30                 @traceid = @TraceID
31                , @eventid = 12 -- SQL:BatchCompleted
32                , @columnid = 1 -- TextData
33                , @on = 1;--include this column in trace
34 EXEC sp_trace_setevent
35                 @traceid = @TraceID
36                , @eventid = 12 -- SQL:BatchCompleted
37                , @columnid = 13 --Duration
38                , @on = 1;--include this column in trace
39 EXEC sp_trace_setevent
40                 @traceid = @TraceID
41                , @eventid = 12 -- SQL:BatchCompleted
42                , @columnid = 15 --EndTime
43                , @on = 1;--include this column in trace        
44 -- step 3: add duration filter
45 DECLARE @DurationFilter BIGINT;
46 SET @DurationFilter = 10000000; --duration in microseconds
47 EXEC sp_trace_setfilter
48                 @traceid = @TraceID
49                , @columnid = 13
50                , @logical_operator = 0 --AND
51                , @comparison_operator = 4 -- greater than or equal to
52                , @value = @DurationFilter; --filter value
53 SELECT @TraceID AS TraceID;

对此第九行中的C:\TraceFiles\LongRunningQueries,请保管TraceFiles文件夹存在,而LongRunningQueries是文本名,创设后会自动加上.trc后缀。

对于过滤器中切实的轩然大波以及列名,大家能够参见:

对于像本人同样的一些初学者,写上边的本子或许相比为难,那么大家能够通过SQL
Server Profiler配置各样必要,然后导出脚本:

金沙国际唯一官网网址 2

下一场把门路等一多级其余参数设置下就可以。

简言之的举多少个使用场景:

简短的举多少个利用场景:

暗中同意追踪是系统内置的,TraceID是1,私下认可是敞开的,能够透过系统布局表
sys.configurations 实行查看,配置项ID(configuration_id)是1568:

三.How to Operate a Trace?

上边大家早就把创立了三个追踪器,但以此追踪器前段时间未曾最初运营,大家能够通过下边包车型大巴脚本来查看trace的场合

select * from sys.traces

实践之后你会发觉有2个trace记录,第贰个是SQL
Server私下认可的trace,它提供最棒有限的功能,第一个就是大家刚刚创立的trace

金沙国际唯一官网网址 3

status便是指追踪器的情事

@status Action
0 Stops the trace
1 Starts the trace
2 Closes the trace and deletes its definition

 

 

 

操作status的脚本:

-- stop  the trace
 DECLARE @TraceID int ;
 SET @TraceID = 2 ; -- specify value from sp_trace_create
 EXEC sp_trace_setstatus
    @traceid = @TraceID
    ,@status = 0 ;-- stop trace
 -- delete the trace
 EXEC sp_trace_setstatus
    @traceid = @TraceID
  ,@status = 2 ;-- delete trace
 -- start the trace
 EXEC sp_trace_setstatus
    @traceid = @TraceID
  ,@status = 1 ;-- start trace

在线生产库为什么遽然宕机?数百张数据表为啥不见?刚打好补丁的系统为啥屡遭毒手?新加上的新闻表为什么每每遗失?某张表字段的猝然退换,毕竟为啥许人所为?那些个无名的拜望背后,终归是人是鬼?忽然扩大的增量数据,终究是对是错?数百兆的日记爆炸式的加强背后又掩饰着如何?这一且的幕后,是应用程序的BUG照旧客商性能的干枯?

在线生产库为什么猛然宕机?数百张数据表为什么不见?刚打好补丁的系列为啥屡遭毒手?新添长的音信表为什么再三错失?某张表字段的蓦地退换,终究为何人所为?那一个个无名氏的拜访背后,毕竟是人是鬼?忽地扩张的增量数据,终究是对是错?数百兆的日志爆炸式的增高背后又遮掩着什么?这一且的暗中,是应用程序的BUG依旧客商品质的缺乏?

金沙国际唯一官网网址 4

四.How to Viewing Trace Data?

前边已经说过,追踪器最后把追踪到的信息写到了三个文书里,约等于我们创造时钦点的门径。通过叁个种类函数就足以查看剖判这么些数量了:

SELECT *
FROM fn_trace_gettable(N'C:\TraceFiles\LongRunningQueries.trc',DEFAULT);

请关怀本篇小说,让大家共同行使数据库的“追踪”(Trace)走进数据库背后,查看当中间原理。

请关怀本篇小说,让大家一并利用数据库的“追踪”(Trace)走进数据库背后,查看其内部原理。

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

五.Summary

SQL
Trace对象提供劳动器端的寻踪的技术,包罗部分仓库储存进度,数据库,视图来创制trace,
通过选拔T-SQL和trace揭发的元数据来保管trace数据,这一个都以SQL
Profiler所不能提供的

 

 

 

二,禁止使用或启用暗许追踪

小编深信如用过SQL Server数据库的人,都会或多或少的使用过SQL
Profiler工具。这厮正是应用SQL
Trace形成的多少个图形化操作工具,大家直接进去本篇的宗旨。

自个儿深信如用过SQL Server数据库的人,都会或多或少的使用过SQL
Profiler工具。这厮正是利用SQL
Trace变成的三个图形化操作工具,大家直接踏向本篇的大旨。

即便暗许追踪被禁止使用,须求重新配置启用默许追踪:

 

 

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

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

一.查看系统暗中认可追踪音讯(Default Trace)

一.查看系统暗中同意追踪音信(Default Trace)

假若暗中认可跟踪已经启用,可以重新配置禁止使用默许追踪:

Trace作为二个很好的数据库追踪工具,在SQL Server
二零零六中便集成到系统成效中去,并且暗许是展开的,当然我们也得以手动的密封它,它坐落sp_config配置参数中,大家得以经过以下语句查看:

Trace作为二个很好的数据库追踪工具,在SQL Server
2006中便集成到系统作用中去,並且暗许是开启的,当然大家也能够手动的关闭它,它位于sp_config配置参数中,大家能够通过以下语句查看:

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

exec sp_configure 'show advanced options' , 0 ;
go
reconfigure;
go
select * from sys.configurations where configuration_id = 1568
select * from sys.configurations where configuration_id = 1568

三,查看暗许追踪的音信

金沙国际唯一官网网址 5

金沙国际唯一官网网址 5

暗中同意追踪记录的数码存款和储蓄在文书中,能够从系统视图
sys.traces查看文件的门道,文件的高低(Size)和文书的翻新格局等音讯,追踪文件私下认可的恢弘名是
.trc。

我们也足以经过上边包车型客车言辞找到那个追踪的记录

大家也足以经过上面的语句找到那个跟踪的记录

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
select * from sys.traces
select * from sys.traces

暗许追踪有5个追踪文件,每贰个文件的最大size暗中同意是20MB,SQL
Server担任维护那5个文本,当实例重启的时候照旧达到文件Size最大值的时候,SQL
Server创设新的文本,将最先创设的追踪文件删除,依次滚动(Rollover)更新。

金沙国际唯一官网网址 7

金沙国际唯一官网网址 7

四,查看跟踪文件的情节

若是未有拉开,大家也得以动用如下语句实行开启,只怕关闭等操作

假设未有打开,大家也能够利用如下语句进行开启,只怕关闭等操作

函数sys.fn_trace_金沙国际唯一官网网址,gettable,用于从跟踪文件中读取数据,以关系表的格式显式:

金沙国际唯一官网网址 9

金沙国际唯一官网网址 10

sys.fn_trace_gettable ( 'filename' , number_files )
--开启Default Trace
sp_configure 'show advanced options' , 1 ;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled' , 1 ;
GO
RECONFIGURE;
GO

--测试是否开启
EXEC sp_configure 'default trace enabled';
GO

--关闭Default Trace
sp_configure 'default trace enabled' , 0 ;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options' , 0 ;
GO
RECONFIGURE;
GO
--开启Default Trace
sp_configure 'show advanced options' , 1 ;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled' , 1 ;
GO
RECONFIGURE;
GO

--测试是否开启
EXEC sp_configure 'default trace enabled';
GO

--关闭Default Trace
sp_configure 'default trace enabled' , 0 ;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options' , 0 ;
GO
RECONFIGURE;
GO

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

金沙国际唯一官网网址 11

金沙国际唯一官网网址 12

参数number_files:如果number_files 被钦点为default,函数读取全部的滚动文件。

通过以下命令找到默许追踪的文书路线

由此以下命令找到暗中认可追踪的文件路线

函数重临的是关乎表,有效字段是:追踪关联的事件绑定的字段,

select * from ::fn_trace_getinfo(0)
select * from ::fn_trace_getinfo(0)
select * 
from sys.fn_trace_gettable(N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_4.trc',default)

金沙国际唯一官网网址 13

金沙国际唯一官网网址 13

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

以上命令归来的结果值,各种值(property)代表的意思如下:

如上命令归来的结果值,种种值(property)代表的含义如下:

函数fn_trace_geteventinfo(trace_id)重临追踪关联的平地风波列表,使用该函数能够查阅私下认可追踪记录的风浪和事件的一定字段:

率先个:2意味滚动文件;

首先个:2象征滚动文件;

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

其次个:表示近日使用的trace文件路径,依照它大家能够找到任何的追踪文件,暗许是一致目录下

其次个:表示近些日子应用的trace文件路线,依据它大家可以找到别的的跟踪文件,暗中同意是均等目录下

六,查看事件和Category

其八个:表示滚动文件的大小(单位MB),当达到那些值就能制造新的滚动文件

其七个:表示滚动文件的深浅(单位MB),当达到这么些值就能够创立新的轮转文件

Category用于组织事件(Event),是事件的分组,在SQL Server
二〇一一中,共有18个Category,1七十六个伊芙nt,每一种伊芙nt属于独一的一个Category。

第七个:追踪的告一段落时间,这里为Null,表示尚无牢固的结束时间

第多少个:追踪的停下时间,这里为Null,表示不曾长久的告一段落时间

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

第三个:当前追踪的动静:0 停止;1 运转

第四个:当前跟踪的图景:0 甘休;1 运营

七,查看事件绑定的字段

 

 

在SQL Server
2011中,事件共有六十几个字段,但不是种种Event都能绑定全部的陆21个字段,每种伊芙nt可以绑定的字段是定位的,系统预先安装,客商不能够修改,视图 sys.trace_event_bindings 用于展现各类事件绑定的字段。

找到该目录,大家查阅下该文件:

找到该目录,大家查阅下该公文:

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

金沙国际唯一官网网址 15

金沙国际唯一官网网址 15

八,使用SQL Server Profiler创建SQL Trace

系统暗许提供5个追踪文件,並且每多少个文件暗中同意大小都以20MB,SQL
Server会自身维护那5个文本,当实例重启的时候照旧达到最大值的时候,之后会重新生成新的文书,将最初的追踪文件删除,依次滚动更新。

系统私下认可提供5个追踪文件,何况每一个文书默许大小都以20MB,SQL
Server会自身维护那5个公文,当实例重启的时候依旧到达最大值的时候,之后会再次生成新的公文,将最先的追踪文件删除,依次滚动更新。

设若顾客要求创设自定义的寻踪,那么能够运用系统提供的积累进程来兑现,不过,使用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的台本代码导出:

大家通过以下命令来查看追踪文件中的内容:

咱俩因而以下命令来查阅追踪文件中的内容:

金沙国际唯一官网网址 17

金沙国际唯一官网网址 18

金沙国际唯一官网网址 18

导出的剧本如下,不能够直接动用,必得修改一处代码:在创造Trace时,钦命期存款款和储蓄追踪数据的文本(File)
或 关系表(Table),仅此而已。

 私下认可的追踪文件,提供的追踪新闻依旧很全的,从中大家得以找到登八人,操作音信等,上边的截图只是含有的一对消息。大家得以接纳该语句实行和睦的加工,然后拿走更使得的音信。

 暗中同意的追踪文件,提供的追踪新闻如故很全的,从中大家能够找到登入人,操作消息等,下边包车型大巴截图只是富含的一些消息。我们能够使用该语句进行和煦的加工,然后拿走更管用的新闻。

金沙国际唯一官网网址 20金沙国际唯一官网网址 21

金沙国际唯一官网网址 22

金沙国际唯一官网网址 23

-- 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
--获取跟踪文件中前100行执行内容
SELECT TOP 100
 gt.[HostName] 
,gt.[ServerName] 
,gt.[DatabaseName] 
,gt.[SPID] 
,gt.[ObjectName] 
,gt.[objecttype] [ObjectTypeID] 
,sv.[subclass_name] [ObjectType] 
,e.[category_id] [CategoryID] 
,c.[Name] [Category] 
,gt.[EventClass] [EventID] 
,e.[Name] [EventName] 
,gt.[LoginName] 
,gt.[ApplicationName] 
,gt.[StartTime] 
,gt.[TextData] 
FROM fn_trace_gettable('E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1267.trc', DEFAULT) gt 
LEFT JOIN sys.trace_subclass_values sv 
ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] 
INNER JOIN sys.trace_events e 
ON gt.[eventclass] = e.[trace_event_id] 
INNER JOIN sys.trace_categories c 
ON e.[category_id] = c.[category_id] 
WHERE gt.[spid] > 50 AND --50以内的spid为系统使用
    gt.[DatabaseName] = 'master' AND --根据DatabaseName过滤
    gt.[ObjectName] = 'fn_trace_getinfo' AND --根据objectname过滤
    e.[category_id]  = 5 AND --category 5表示对象,8表示安全
    e.[trace_event_id] = 46 
    --trace_event_id 
    --46表示Create对象(Object:Created),
    --47表示Drop对象(Object:Deleted),
    --93表示日志文件自动增长(Log File Auto Grow),
    --164表示Alter对象(Object:Altered),
    --20表示错误日志(Audit Login Failed)
ORDER BY [StartTime] DESC
--获取跟踪文件中前100行执行内容
SELECT TOP 100
 gt.[HostName] 
,gt.[ServerName] 
,gt.[DatabaseName] 
,gt.[SPID] 
,gt.[ObjectName] 
,gt.[objecttype] [ObjectTypeID] 
,sv.[subclass_name] [ObjectType] 
,e.[category_id] [CategoryID] 
,c.[Name] [Category] 
,gt.[EventClass] [EventID] 
,e.[Name] [EventName] 
,gt.[LoginName] 
,gt.[ApplicationName] 
,gt.[StartTime] 
,gt.[TextData] 
FROM fn_trace_gettable('E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1267.trc', DEFAULT) gt 
LEFT JOIN sys.trace_subclass_values sv 
ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] 
INNER JOIN sys.trace_events e 
ON gt.[eventclass] = e.[trace_event_id] 
INNER JOIN sys.trace_categories c 
ON e.[category_id] = c.[category_id] 
WHERE gt.[spid] > 50 AND --50以内的spid为系统使用
    gt.[DatabaseName] = 'master' AND --根据DatabaseName过滤
    gt.[ObjectName] = 'fn_trace_getinfo' AND --根据objectname过滤
    e.[category_id]  = 5 AND --category 5表示对象,8表示安全
    e.[trace_event_id] = 46 
    --trace_event_id 
    --46表示Create对象(Object:Created),
    --47表示Drop对象(Object:Deleted),
    --93表示日志文件自动增长(Log File Auto Grow),
    --164表示Alter对象(Object:Altered),
    --20表示错误日志(Audit Login Failed)
ORDER BY [StartTime] DESC

View Code

金沙国际唯一官网网址 24

金沙国际唯一官网网址 25

 

金沙国际唯一官网网址 26

金沙国际唯一官网网址 26

注:SQL
Trace是被扩张事件代表的成效,在承接的版本上校会被移除,建议在事后的支出中选择扩大事件。