前言

前言

SQL追踪(SQL
Trace)是一个轻量级的寻踪工具,根据事件(伊芙nts)记录数据库发生的信息,大致对数据库质量未有何震慑。SQL
Server内置二个Trace,称作暗中认可追踪(Default
Trace),暗许追踪的ID是1,咱们平时选择的SQL Server Profiler,正是运用SQL
Trace记录数据库活动的贰个工具。SQL Trace在SQL
Server数据库引擎种现身的可比早,能够被质量和功力更出色的增加事件(Extended
Events)替代。

一.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

下一场把门路等一类别别的参数设置下就能够。

SQL server 2009 中引入了Extended Events 用以沟通SQL Trace。
不过在率先个本子中并从未为顾客提供UI,因而使用Extended
伊芙nts而不是很方便。SQL Server
二零一二立刻校对了那一点,将UI管理工科具集成在SSMS中,
那就象征大家无需再为了查询Event
XML而学习使用XQuery了。因而跟多的DBA和支付由SQL trace
和Profiler转向了Extended 伊芙nts.

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

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

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

三.How to Operate a Trace?

地点大家早已把创立了八个追踪器,但那么些追踪器方今不曾伊始运维,大家能够经过上面的脚本来查看trace的情况

select * from sys.traces

试行之后您会意识有2个trace记录,第一个是SQL
Server私下认可的trace,它提供极致简单的机能,第4个正是我们刚刚创制的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

本条系列教程中,我们将详细的介绍怎么样使用Extended
Events作为会诊数据采摘工具,用来追踪SQL
Server的品质问题。第3节中大家将从贰个DBA都锁熟练的标题最早: 使用SQL
trace 追踪考察 long-rannning 查询。 从基础开首,小编会介绍如何使用Extended
伊芙nts 达成一样的任务。

在线生产库为什么忽然宕机?数百张数据表为啥不见?刚打好补丁的系统为何屡遭毒手?新增添长的音讯表为啥再三遗失?某张表字段的黑马改变,究竟为何人所为?那个个无名氏的访问背后,终归是人是鬼?忽地扩大的增量数据,究竟是对是错?数百兆的日记爆炸式的增长背后又遮盖着哪些?这一且的暗中,是应用程序的BUG照旧客户质量的缺点和失误?

在线生产库为什么陡然宕机?数百张数据表为啥不见?刚打好补丁的连串为啥屡遭毒手?新增加长的新闻表为啥屡屡错过?某张表字段的豁然退换,毕竟为何人所为?那几个个无名氏的访谈背后,究竟是人是鬼?陡然扩展的增量数据,终归是对是错?数百兆的日志爆炸式的滋长背后又遮掩着什么样?这一且的骨子里,是应用程序的BUG还是顾客品质的缺少?

图片 4

四.How to Viewing Trace Data?

眼下已经说过,追踪器最终把追踪到的音信写到了一个文本里,相当于大家成立时内定的门道。通过八个连串函数就足以查看深入分析这个数据了:

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

Extended 伊芙nts不仅是四个用来替换SQL trace
和Profiler的工具。通过一体化这些连串教程, 你会发觉那几个运用SQL trace
往往会消耗过高代价的检查判断,在Extended 伊芙nt 变的切实可行,
何况以前困难的,以致不容许的追踪职分不但变的有用,并且特别简明。

请关怀本篇作品,让我们联合使用数据库的“跟踪”(Trace)走进数据库背后,查看其里面原理。

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

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

五.Summary

SQL
Trace对象提供劳动器端的寻踪的手艺,包涵部分仓库储存进度,数据库,视图来创立trace,
通过选择T-SQL和trace揭露的元数据来保管trace数据,这个都是SQL
Profiler所不可能提供的

 

SQL Trace 和Profiler
将不再引入新功能。即便她们为大家熟习,Profiler伴随SQL Server7.0在一九九六发行, 以往是时候拥抱Extended Event精通他的机能。

 

 

二,禁用或启用暗中同意追踪

什么是Extended Event

Extended Event 是贰个事件访谈基础设备,最初在 SQL Server 二〇〇九中引进。我们得以使用Extended 伊夫nt 采摘深入分析SQL server
实例和数据库发生的两样体系型的检查判断数据。Extended 伊芙nt是SQLOS的一有的,
它由众多模块组合,并在SQL
Server运营时被加载。它提供了大量的事件集用以沟通,进步和扩充S(英文名:Barbie Hsu)QL
Trace中的事件。

当微软说了算使用Extened Events 替换SQL
Trace时,他们从草图起先规划了一整套event搜罗架构。他的目的之一正是中度的可扩充性,能够根据必要加多新的event。与此相同的时间微软为SQL
Server引进一名目好多的Feature, 如Avaliability Groups, In-Memory
OLTP和Columnstore indexes,
由此也还要为那些Feature增添了对应的Event,用以当使用这个Feature时搜聚会诊数据。对于SQL
Server 2011及以上版本,选取Extended Event
至关心珍视要,因为新Featrue参加的event只好在Extened Events中找到。

表一中罗列的各入眼SQL Server 版本中可用的Extended
伊芙nt事件数量。这一个是成套的风云, 包蕴debug 事件, 同样那几个本子中, SQL
Trace 唯有1八十几个事件。 在SQL Server 2013中,SQL
Trace的具备事件都有二个合作的Extended
伊芙nts事件,即便他们并从未各样对应。
SQL Server Version Number of Events Notes
图片 5

另三个Extended
伊夫nts设计的最首要对象是竭尽减小收罗数据的震慑,在踏勘难点时减小系统卓殊的开销。Extended
event 使用了种种艺术来达成这一目的, 大家接下去进一步钻探:

Event最小暗中认可负载——暗中认可景况下每一种事件仅收罗最少的事件数据列。假诺我们盼望越来越搜聚列,我们亟须出示的增进“Actions”到事件中。SQL
Trace的打算中默许会搜集大量的负载数据,不过我们只有忽略了那一个无需的数额。
强有力的过滤谓词——ExtendedEvents提供了这多少个细致的过滤
通过谓词,大家得以只采撷那个符合特定条件的风云。我们能够使用谓词来访问特定的事件,如每发生5次,
大概只搜罗一些特定的基准下的风浪,如当三个的数目标值(如Duration)比原先的值大。Extended
伊芙nt在事件触发的开始的一段时代一旦暗许负载数据搜集完结就能够被过滤,那样可防止止别的不须要的多少搜罗进度。
Advanced Tagets—— 与SQL Trance
相似,同时协理In-Memory(Ring_bufer)和文件系统(event_file)作为对象。
Extended events 提供了依Wright定条件聚合数据选拔对象。

这就象征,尽管我们统筹了相对较发杂的事件会话,数据出自数据分歧的风浪,大家只要小心的希图谓词并选拔那一个大家须要搜聚的对象数据,我们得以比相当小的代价来察看服务器。
总来说之,多量的平地风波,结合高效的过滤以及各个选项,使得Extended
Event成为贰个远远抢先Trace的风浪搜集器。

小编信任如用过SQL Server数据库的人,都会或多或少的行使过SQL
Profiler工具。这厮正是利用SQL
Trace形成的二个图形化操作工具,我们一直进去本篇的大旨。

自家深信不疑如用过SQL Server数据库的人,都会或多或少的选拔过SQL
Profiler工具。这厮正是运用SQL
Trace产生的三个图形化操作工具,大家一向进去本篇的大旨。

如若暗许追踪被禁止使用,须要重新配置启用默许追踪:

从大家耳濡目染的最先: SQL Trace

从自己的阅历讲,学习新东西最轻松的主意正是从大家已知的学问开头。下面临于
SQL Trace 和Profiler等这几个你已精通的文化介绍,将用作我们驾驭Extended
Event职业章程的底蕴,且并领悟它与SQL Trace 和Profiler的不等。

对于数据库专家,排查SQL查询质量难点是大家日常遇上的难题之一。从前,大家普通会创制多少个Trace用于捕捉与我们存款和储蓄进度或SQL试行相关的风浪。大家平时会设置有个别过滤条件,如超越一定数量的Reads,特定的实行时间恐怕消耗的CPU等。

普通展开Profiler来定义trace是八个方便飞快的主意。当大家新建二个trace,也说不定是行使一些已部分模板,连接受SQL
实例,并选择供给的大运。在截图第11中学,大家仅选用了三个事件“RPC:Completed”
和”SQL:StmtCompleted”,并捕捉了长久以来的数据列。

图片 6

Figure 1: Selecting events and data columns for a trace

当使用Profiler或SQL
Trace时,增加过滤过滤条件总是被推举的。不过与Extended Event比,使用SQL
Trace的主题素材之一便是后期过滤。及时我们定义了过滤条件,SQL Trace or
Profiler
任然会收罗事件实例的整套数据,然后再过滤它。当然,过滤条件还是是不行首要的,它保证了我们的发送到顾客端依旧指标文件的平地风波是透过过滤的。
在那么些事例中,大家抬高贰个过滤条件:Reads 大于等于10000,如图2

图片 7

Figure 2: Defining a trace filter on
number of reads

当大家选择好事件和列,设置过滤,以及保存的Trace
文件后,大家就足以运维Trace了。经验丰硕的DBA和付出都驾驭直接运转Profiler会带来深重的属性难题(
).由此为了最小化对生育处境的震慑,最佳的不二秘诀是点击早先后及时甘休Trace,然后通过”File
| Export | Script Trace Definition | For SQL Server 二零零五 –
二〇一四”菜单导出脚本。 那样做之后Trace将要劳动器端奉行实际不是在
Profiler的GUI上举办。 List 1 是导出的SQL脚本。

/****************************************************/
/* Created by: SQL Server 2014 Profiler          */
/* Date: 11/30/2015  08:50:44 AM         */
/****************************************************/

-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'InsertFileNameHere',
    @maxfilesize, NULL 
IF ( @rc != 0 )
    GOTO error

-- Client side File and Table cannot be scripted

-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 10, 10, @on
EXEC sp_trace_setevent @TraceID, 10, 3, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 10, 15, @on
EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 26, @on
EXEC sp_trace_setevent @TraceID, 41, 3, @on
EXEC sp_trace_setevent @TraceID, 41, 10, @on
EXEC sp_trace_setevent @TraceID, 41, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 13, @on
EXEC sp_trace_setevent @TraceID, 41, 14, @on
EXEC sp_trace_setevent @TraceID, 41, 15, @on
EXEC sp_trace_setevent @TraceID, 41, 16, @on
EXEC sp_trace_setevent @TraceID, 41, 18, @on
EXEC sp_trace_setevent @TraceID, 41, 26, @on
EXEC sp_trace_setevent @TraceID, 41, 61, @on


-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT

EXEC sp_trace_setfilter @TraceID, 10, 0, 7,
    N'SQL Server Profiler - f45d52c9-c0eb-45da-8bae-dc6f1a945251'
SET @bigintfilter = 10000
EXEC sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter

-- 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

Listing 1: A server-side trace to capture poorly-performing queries

稍许人以前逐句的开卷过上述SQL Trace
脚本,也也许只是对上述脚本的效果与利益由必然了然。
但是为着保证大家在同一块跑线上,大家会连忙的解释一下这段脚本。

始于的一段定义了有些用以创制Trace的仓库储存进度sp_trace_create
所要求的变量。作为一个客户,大家率先定义了最大文件大小(这么些事例中@maxfilesize设置的为5MB)。大家也得以内定是或不是。越来越多关于sp_trace_create细节请查看

出口文件路径也是sp_trace_create的一有的。在运转那几个Trace钱,请使用三个合适的文件路线替换InsertFileNameHere,如“C:\temp\ReadsFilter_Trace”。遵照脚本的笺注,大家无需钦定.trc后缀名。

-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'InsertFileNameHere',
    @maxfilesize, NULL 
IF ( @rc != 0 )
    GOTO error

Listing 2: The sp_trace_create portion
of the server-side trace

基于以上定义,那些Trace会一向实行,直到我们手动结束它。或然,大家可认为sp_trace_create提供三个@datetime参数,那样大家就足以界定Trace的实行时间(比如大家可以安装进行四个钟头set
@DateTime = dateadd(hh, 1, getdate())。

Trace脚本的下一段设置了大家所急需的事件。存款和储蓄进度sp_trace_setevent增多了大家供给捕获的风浪和列。这几个使用数据来标记的平地风波和列并不低价阅读。我们日常要求MSDN(
和SQL:StmtCompleted事件。接下来第二列定义了数据列。举个例子10意味ApplicationName,3意味DatabaseID等。为了更清楚的查阅,作者在源代码上增添了疏解。

 

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 10, @on    --RPC:Completed, AppName
exec sp_trace_setevent @TraceID, 10, 3,  @on    --RPC:Completed, DatabaseID
exec sp_trace_setevent @TraceID, 10, 12, @on    --RPC:Completed, SPID
exec sp_trace_setevent @TraceID, 10, 13, @on    --RPC:Completed, Duration
exec sp_trace_setevent @TraceID, 10, 14, @on    --RPC:Completed, StartTime
exec sp_trace_setevent @TraceID, 10, 15, @on    --RPC:Completed, EndTime
exec sp_trace_setevent @TraceID, 10, 16, @on    --RPC:Completed, Reads
exec sp_trace_setevent @TraceID, 10, 18, @on    --RPC:Completed, CPU
exec sp_trace_setevent @TraceID, 10, 26, @on    --RPC:Completed, ServerName
exec sp_trace_setevent @TraceID, 41, 3,  @on    --SQL:StmtCompleted, DatabaseID
exec sp_trace_setevent @TraceID, 41, 10, @on    --SQL:StmtCompleted, AppName
exec sp_trace_setevent @TraceID, 41, 12, @on    --SQL:StmtCompleted, SPID
exec sp_trace_setevent @TraceID, 41, 13, @on    --SQL:StmtCompleted, Duration
exec sp_trace_setevent @TraceID, 41, 14, @on    --SQL:StmtCompleted, StartTime
exec sp_trace_setevent @TraceID, 41, 15, @on    --SQL:StmtCompleted, EndTime
exec sp_trace_setevent @TraceID, 41, 16, @on    --SQL:StmtCompleted, Reads

Listing 3: Setting the trace events

在数据库引擎中,Trace调控器会检查三个平地风波是还是不是须求被擒获。如若需求则将事件的音讯发送到SQL追踪行集提供程序,或然一旦你运转的Profiler,也许是文本。在Trace发送这个消息前,全体不须要的行会被移除。举个例子在我们定义的五个事件中,大家并未有选拔DatabaseName。可是SQL
server
任然会为那五个小时捕捉DatabaseName列,只是它并不会被发送到GUI或然封存在文件中。

在结尾一步中,大家使用存款和储蓄进度sp_trace_setfilter为Trace定义了过滤条件。二个过过滤条件“SQL
Server Profiler –
f45d52c9-c0eb-45da-8bae-dc6f1a945251”是由脚本默许生成的,它过滤了有的由Profiler
UI生成的“admin”查询(SELECT SEQX56VERPROPERTY )。

在那几个事例中,大家设置了一个过滤条件,只将Reads大于等于一千0
(@bigintfilter =
一千0)的查询语句或存款和储蓄进度发送到目的文件中。再次提示,那是中期过滤,全部的风云和音信都会被捕捉。然后在发送到文件或客商端前遵照筛选规范移除。

-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT

EXEC sp_trace_setfilter @TraceID, 10, 0, 7,
    N'SQL Server Profiler - f45d52c9-c0eb-45da-8bae-dc6f1a945251'
SET @bigintfilter = 10000
EXEC sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter

Listing 4: Setting the trace
filter

剧本的最终一段使用存款和储蓄进度sp_trace_setstatus
运行Trace,并展现TraceID。那一个独一的TraceID用于停止Trace,也得以用于删除Trace定义。

-- 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

Listing 5: Starting the trace

设若大家实践了那一个剧本,Trace将被运维并将到处的周转和征集事件数量,直至大家结束它。未来,大家怎么将这几个所了解的手艺,使用Extended
伊夫nt取代呢?

 

 

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

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

改动Trace到Extended 伊夫nts 事件会话

将已经存在的Trace文件定义转换为事件会话,作者引入的秘技使用贰个囤积进度,他的作者是 Jonathan Kehayias。
你能够从(
那一个剧本只能在SQL Server 贰零壹壹及之后版本运营,认为SQL Server
二〇一一在先的版本Extended 伊夫nts并不扶助全部的Trace事件。

手动转变Trace到Extended 伊芙nts会话

借使是因为一些原因你无法应用上述存款和储蓄进程,微软文书档案介绍了多个手动调换进度:Convert
an Existing SQL Trace Script to an Extended Events Session
()

在您的SQL实例中进行以上脚本用于创立那个蕴藏进程。实行这些蕴藏进度仅供给输入以下参数,如Listing
6 所示。

EXECUTE sp_SQLskills_ConvertTraceToExtendedEvents 
              @TraceID = 2, 
              @SessionName = 'XE_ReadsFilter_Trace', 
              @PrintOutput = 1, 
              @Execute = 0;

Listing 6: Converting a server-side
trace to use Extended Events

参数@TraceID 是你要转变为Extended event 的Trace
ID。由此,这几个Trace必需存在,无论正在实施与否。在此处TraceID为2(从Listing
5的奉行结果中得到)。

推行那几个蕴藏进程为ReadsFilter_Trace.trc Trace生成Extended
Event会话DLL脚本,如Listing7所示:

IF EXISTS ( SELECT 1
             FROM   sys.server_event_sessions
             WHERE  name = 'XE_ReadsFilter_Trace' )
    DROP EVENT SESSION [XE_ReadsFilter_Trace] ON SERVER;
GO
 CREATE EVENT SESSION [XE_ReadsFilter_Trace] ON SERVER
 ADD EVENT sqlserver.rpc_completed (
    ACTION ( sqlserver.client_app_name   -- ApplicationName from SQLTrace
    , sqlserver.database_id              -- DatabaseID from SQLTrace
    , sqlserver.server_instance_name     -- ServerName from SQLTrace
    , sqlserver.session_id               -- SPID from SQLTrace
                   -- EndTime implemented by another Action in XE already
                   -- StartTime implemented by another Action in XE already
   )
    WHERE 
   ( logical_reads >= 10000 ) ),
 ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sqlserver.client_app_name   -- ApplicationName from SQLTrace
    , sqlserver.database_id              -- DatabaseID from SQLTrace
    , sqlserver.server_instance_name     -- ServerName from SQLTrace
    , sqlserver.session_id               -- SPID from SQLTrace
                   -- EndTime implemented by another Action in XE already
                   -- StartTime implemented by another Action in XE already
   )
    WHERE 
   ( logical_reads >= 10000 ) )
 ADD TARGET package0.event_file (  SET filename =                                  'C:\temp\XE_ReadsFilter_Trace.xel' ,
                                   max_file_size = 5 ,
                                   max_rollover_files = 1 )
GO

Listing 7: The Extended Events event
session

一.查看系统私下认可追踪消息(Default Trace)

一.查看系统默许追踪音讯(Default Trace)

尽管暗中认可追踪已经启用,能够重新配置禁用默许追踪:

检查Extended Event事件会话

与大家解析由Profiler生成的劳务器端Trace脚本同样,我们会透过不一样的段子逐步剖析Extended
伊芙nts事件会话是何等成立的。

Trace作为多少个很好的数据库追踪工具,在SQL Server
二零零六中便集成到系统机能中去,况兼暗许是敞开的,当然大家也得以手动的关闭它,它身处sp_config配置参数中,大家可以透过以下语句查看:

Trace作为四个很好的数据库追踪工具,在SQL Server
二〇〇六中便集成到系统机能中去,而且暗中同意是展开的,当然大家也足以手动的密封它,它放在sp_config配置参数中,大家能够经过以下语句查看:

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

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

创设事件会话

剧本首先包罗了一个IF段落证明,用于检查是不是有同名事件会话存在,要是存在则删除它。这样能够制止在创制事件会话时出现错误。

IF EXISTS ( SELECT  1
            FROM    sys.server_event_sessions
            WHERE   name = 'XE_ReadsFilter_Trace' )
    DROP EVENT SESSION [XE_ReadsFilter_Trace] ON SERVER;
GO

Listing 8: Checking for the existence of
an event session with the same name

这段脚本接着使用CREATE EVENT SESSION
语法创设了三个事变会话(

/* Extended Events */

CREATE EVENT SESSION [XE_ReadsFilter_Trace]
ON SERVER
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 5 

-- Please replace the text
--InsertFileNameHere…etc…

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'InsertFileNameHere',
    @maxfilesize, NULL 
IF ( @rc != 0 )
    GOTO ERROR

 

Listing 9: Create the event session

select * from sys.configurations where configuration_id = 1568
select * from sys.configurations where configuration_id = 1568

三,查看暗许追踪的新闻

添加Event和Actions

接下去,Extended 伊芙nts脚本金和利息用CREATE EVENT SESSION 的 ADD EVENT
子句内定了第三个事件,此处为
rpc.completed事件,何况接下去内定了风云触发时推行的有的特别动作,这些例子中为采访额外的八个事件数据列。

/*Extended Events*/
ADD EVENT sqlserver.rpc_completed (
    ACTION (
      sqlserver.client_app_name
    , sqlserver.database_id
    , sqlserver.server_instance_name
    , sqlserver.session_id
           )

 

/* Trace */
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 3,  @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 26, @on

 

Listing 10: Adding actions

那边有几处Extended
伊芙nts和Trace关键的不一样点要求建议。第一,注意事件名和Actions中募集的数据列(如:client_app_name,
database_id),他们是文本。大家无需再查询哪些数字对应的平地风波照旧数据列!与SQL
Trace比较,书写阅读Extended Events脚本变的特别简约。

支持,注意Extended
伊夫nts脚本中并从未点名全体的数据列。事实上,许好些个据列被定义为事件的暗许负载被访谈。大家鲜明无需在剧本中钦点这一个暗中同意列。不过我们得以使用UI来查阅事件的暗许负载由什么列组成,
我们会在下一阶教程中等教育授。

那是Trace 和Extended Events三个要害的作为分化。SQL Trace
默许行为是搜聚全部不小可能率立见作用的列。然后由客商过滤任何无需的音信。Extended
伊夫nts
越发便捷,各样事件有一组由最少的数据列组成的默许负载,在事变出发时总会被暗许收罗。纵然我们需求搜集其余不再暗中同意负载中的列,大家要求以Actions情势丰硕他们。举例,在RPC:Completed事件中加多的Actions:client_app_name,
database_id, server_instance_name 和session_id,
他们都不属于事件的暗许负载。采摘那一个Actions是可选的。

因为只有暗中同意负载事件列会被含有在时光中,由此初阶化事件访问的开拓相对相当的小。Actions数据采摘是在谓词过滤后才发出的,因而搜集多量的Actions,或许高消耗的Actions(如内部存款和储蓄器Dump),都会扩充Extended
伊夫nts会话的消耗。由此,严谨的取舍额外的数码搜聚对捕获事件越发重要。大家会在下一阶中详细座谈这么些宗旨。

图片 8

图片 8

私下认可追踪记录的数额存款和储蓄在文件中,可以从系统视图
sys.traces查看文件的路径,文件的轻重缓急(Size)和文件的立异方式等新闻,追踪文件暗许的恢宏名是
.trc。

定义predicate

在选拔过事件和附加的Actions后,接下的一段定义了过滤器。

/* Extended Events */
    WHERE 
     ( logical_reads >= 10000 )
/* Trace */

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - f45d52c9-c0eb-45da-8bae-dc6f1a945251'
set @bigintfilter = 10000
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter

 

Listing 11: Adding a filter

回看下当大家接纳Profiler定义trace时,我们采纳了Sp_trace_setfilter设置了一个过滤,排除具备小于一千0Reads的风云数量。在事变会话定义中,这些过滤,术语为谓词,是一个简单易行的WHERE子句。

Extended
伊夫nts实行开始的一段时期过滤。换句话说,在事件基础数据采摘后里立时试行谓词,唯有符合过滤条件的事件实例才会被触发。这种事业体制与SQL
Trace和Profiler的末日过滤比较,在数额采摘时的支付更加小。

大家也能够经过下边包车型地铁语句找到这么些追踪的笔录

大家也得以经过上边包车型客车说话找到那几个追踪的记录

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

增多其余事件

此时rpc_completed 事件早就安顿实现。加多其他的平地风波仅需使用ADD
EVENT子句再一次加多,如Listing12所示的丰裕sql:statement_completed事件。

ADD EVENT sqlserver.sql_statement_completed(
   ACTION 
   (
           sqlserver.client_app_name         -- ApplicationName from SQLTrace
          , sqlserver.database_id            -- DatabaseID from SQLTrace
          , sqlserver.server_instance_name   -- ServerName from SQLTrace
          , sqlserver.session_id             -- SPID from SQLTrace
                   -- EndTime implemented by another Action in XE already
                   -- StartTime implemented by another Action in XE already
   )
   WHERE 
   (
           logical_reads >= 10000
   )

Listing 12: Adding a second event to the
event session

重新,大家得以挑选额外的数据列。Extended
Events的狡滑在于我们可以为各种事件设置同一或许区别的过滤条件。这一点在Trace中不能到位,过滤条件对于具备事件生效。另外在Extended
伊芙nts中大家得以设置越来越多庞大的过滤条件,如大家得以应用AND和O汉兰达条件,在此大家不做过多斟酌。

select * from sys.traces
select * from sys.traces

暗中认可跟踪有5个追踪文件,每一个文书的最大size暗许是20MB,SQL
Server肩负维护那5个公文,当实例重启的时候依旧达到文件Size最大值的时候,SQL
Server成立新的公文,将最初创立的追踪文件删除,依次滚动(Rollover)更新。

钦命指标

在累加富有事件后,大家运用ADD TAWranglerGET 来内定输出目的,SQL
Server将搜罗的数据以及有关的Actions写入目的。在Trace中大家得以采取输出至文件,或然试试写入Profiler,纵然不被推举。在Extended
伊芙nts我们也会有八个指标能够挑选,饱含最核心内部存款和储蓄器存储(ring_buffer)和文件系统存款和储蓄(event_file),同不时候又有些高档的目的可以提供数据聚合成效。

在那几个例子中,大家将选用event_file作为指标,那点与Trace输出至.trc文件类似,不过我们须求在文件中内定文件扩大名。

/* Extended Events */

ADD TARGET package0.event_file
(
      SET filename = 'C:\temp\XE_ReadsFilter_Trace.xel',
             max_file_size = 5,
             max_rollover_files = 1
)
/* Trace */

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

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error
Listing 13: Specifying the target for the event session

Listing 13: Specifying the target for the event session
同一大家得以设置文件大小,以及大家能够安装创设的轮转跟新文件数量

图片 10

图片 10

四,查看追踪文件的剧情

设置事件会话选项

最终,在我们的对话定义中,大家还也会有众多可选的对话配置项,如最大内部存款和储蓄器大小以及调解延迟。因为我们在剧本中绝非点名这几个选择,由此会利用暗中认可值。会话设置会在下一阶中详细钻探。
万一我们再花一分钟回想一下全套Extended
Events会话定义,你会发觉它非常直观并且对于那个DDL每一片段的精晓不会有别的不便。

固然未有开启,大家也能够动用如下语句举行开启,或然关闭等操作

假设未有拉开,我们也得以利用如下语句进行开启,大概关闭等操作

函数sys.fn_trace_gettable,用于从跟踪文件中读取数据,以涉及表的格式显式:

运作事件会话

与Trace同样,Extended
Event会话不会被私下认可运维。为了运营三个对话,大家须要动用Listing
第114中学所示的ALERT语句。

ALTER EVENT SESSION [XE_ReadsFilter_Trace]
 ON SERVER
 STATE=START;
GO

Listing 14: Starting the event
session

实践运行后,大家能够运转一段脚本来验证Extended 伊夫nts是否早已起步。

/* Extended Events */

SELECT
  [es].[name] AS [EventSession],
 [xe].[create_time] AS [SessionCreateTime],
  [xe].[total_buffer_size] AS [TotalBufferSize],
  [xe].[dropped_event_count] AS [DroppedEventCount]
FROM [sys].[server_event_sessions] [es]
LEFT OUTER JOIN [sys].[dm_xe_sessions] [xe] ON [es].[name] = [xe].[name];
GO
/* Trace */

SELECT 
  [id] AS [TraceID],
  CASE
    WHEN [status] = 0 THEN 'Not running'
    WHEN [status] = 1 THEN 'Running'
  END AS [TraceStatus],
  [start_time] AS [TraceStartTime],
  [buffer_size] AS [BufferSize],
  [dropped_event_count] AS [DroppedEventCount]
FROM [sys].[traces];
GO

Listing 15: Check to see which event sessions and traces are running

在这么些事例里,大家能够看来Figure
3中的输出,他们来得了我们所创制的客户事件会话和Trace,一样,事件会话和Trace已经被运营了。

就像是Trace由一个暗许直至实行的Trace(TraceID =1), Extended
伊芙tns也许有system_health
事件会话,那个与私下认可Trace并不完全同样。大家在下阶中再看system_health
会话。假若你利用了Availability Groups (AG),也许有贰个AlwaysOn_health
会话向来施行,来采访AG相关的新闻以及故障检查评定事件。

图片 12

Figure 3: Which traces and event sessions are running?

在我们运维了Trace和Session后,我们能够动用ALTE奥德赛SESSION来终止事件会话,使用sp_trace_setstatus停止Trace。

/* Extended Events */

ALTER EVENT SESSION [XE_ReadsFilter_Trace]
  ON SERVER
  STATE=STOP;
GO
/* Trace */

DECLARE @TraceID INT = 2;
EXEC sp_trace_setstatus @TraceID, 0; 
GO

Listing 16: Stopping the extended events session and trace

那儿,不多被访问,可是大家定义的Trace和事件会话都还在,大家能够依照须要再也启航他们,或许将她们的定义完全除去。

/* Extended Events */

DROP EVENT SESSION [XE_ReadsFilter_Trace]
    ON SERVER;
GO
/* Trace */

DECLARE @TraceID INT = 2;
EXEC sp_trace_setstatus @TraceID, 2; 
GO

Listing 17: Removing the extended events session and trace definition

咱俩并不引入在事件会话完成后将它删除。恐怕很六个人有在行使Trace时有其一习贯,在Trace中当SQL实例被重启后,除了默许Trace其余Trace定义会整整有失。那些也是Extended
伊夫nts与Trace主要的差别点:会话定义会作为元数据保存在服务器中,并且会被长久化。创制过事件会话后,你就能够遵照必要运行或终止它了。

图片 13

图片 14

sys.fn_trace_gettable ( 'filename' , number_files )

总结

你未来一度清楚怎么样将SQL Trace 的学问映射到Extended
Events中了,大家能够利用T-SQL达到我们的靶子。大家下一步将详细的看一下DDL,
何况转向Extended 伊夫nts的UI。我们在须臾间阶中消除这一个标题。

--开启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获取;

图片 15

图片 16

参数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)

图片 17

图片 17

五,查看默许追踪记录的风波列表

以上命令归来的结果值,各样值(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用于集体事件(伊芙nt),是事件的分组,在SQL Server
2011中,共有十八个Category,1七十九个伊夫nt,种种Event属于独一的三个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
二零一三中,事件共有陆18个字段,但不是各样Event都能绑定全部的陆二十一个字段,每一种Event能够绑定的字段是永远的,系统预先安装,客户无法修改,视图 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

图片 19

图片 19

八,使用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 
的概念就可以。

相关文章