SQL Server Extended Events 进阶 1：从SQL Trace 到Extended Events

2021-10-23 01:32:57

http://www.sqlservercentral.com/articles/Stairway+Series/134869/

SQL server 2008 中引入了Extended Events 用以替换SQL Trace。然而在第一个版本中并没有为用户提供UI，因此使用Extended Events并不是很方便。SQL Server 2012及时修正了这一点，将UI管理工具集成在SSMS中，这就意味着我们不需要再为了查询Event XML而学习使用XQuery了。因此跟多的DBA和开发由SQL trace 和Profiler转向了Extended Events.

这个系列教程中，我们将详细的介绍如何使用Extended Events作为诊断数据收集工具，用来跟踪SQL Server的性能问题。第一节中我们将从一个DBA都锁熟悉的问题开始：使用SQL trace 跟踪调查 long-rannning 查询。从基础开始，我会介绍如何使用Extended Events 完成同样的任务。

Extended Events不仅仅是一个用于替换SQL trace 和Profiler的工具。通过完整这个系列教程，你会发现那些使用SQL trace 往往会消耗过高代价的诊断，在Extended Event 变的切实可行，并且之前困难的，甚至不可能的跟踪任务不但变的可行，而且更加简单。

SQL Trace 和Profiler 将不再引入新功能。虽然他们为我们熟知，Profiler伴随SQL Server7.0在1998 发行，现在是时候拥抱Extended Event理解他的功能。

什么是Extended Event

Extended Event 是一个事件收集基础设施，最早在 SQL Server 2008 中引入。我们可以使用Extended Event 收集分析SQL server 实例和数据库产生的不同种类型的诊断数据。Extended Event是SQLOS的一部分，它由很多模块组成，并在SQL Server启动时被加载。它提供了大量的事件集用以替换，提升和扩展SQL Trace中的事件。

当微软决定使用Extened Events 替换SQL Trace时，他们从草图开始设计了一整套event收集架构。他的目标之一就是高度的可扩展性，可以根据需求添加新的event。与此同时微软为SQL Server引入一系列的Feature，如Avaliability Groups， In-Memory OLTP和Columnstore indexes, 因此也同时为这些Feature添加了相应的Event，用以当使用这些Feature时收集诊断数据。对于SQL Server 2012及以上版本，采用Extended Event 至关重要，因为新Featrue加入的event只能在Extened Events中找到。

表一中罗列的各主要SQL Server 版本中可用的Extended Event事件数量。这些是全部的事件，包括debug 事件，同样这些版本中， SQL Trace 只有180个事件。在SQL Server 2012中，SQL Trace的所有事件都有一个兼容的Extended Events事件，虽然他们并没有一一对应。
SQL Server Version Number of Events Notes

另一个Extended Events设计的重要目标是尽量减小收集数据的影响，在调查问题时减小系统额外的开销。Extended event 使用了多种方式来达到这一目标，我们接下来进一步讨论：

Event最小默认负载——默认情况下每个事件仅收集最少的事件数据列。如果我们希望进一步收集列，我们必须显示的添加“Actions”到事件中。SQL Trace的设计中默认会收集大量的负载数据，但是我们仅仅忽略了那些不需要的数据。
强大的过滤谓词——ExtendedEvents提供了非常细腻的过滤通过谓词，我们可以只收集那些符合特定条件的事件。我们可以使用谓词来收集特定的事件，如每发生5次，或者只收集某些特定的条件下的事件，如当一个的数据的值(如Duration)比以前的值大。Extended Event在事件触发的早期一旦默认负载数据收集完成就会被过滤，这样可以避免任何不必要的数据收集过程。
Advanced Tagets—— 与SQL Trance 相似，同时支持In-Memory(Ring_bufer)和文件系统(event_file)作为目标。 Extended events 提供了根据特定条件聚合数据选择目标。

这就意味着，即使我们设计了相对较发杂的事件会话，数据来自数据不同的事件，我们只要小心的设计谓词并选择那些我们需要收集的目标数据，我们可以很小的代价来观察服务器。
总之，大量的事件，结合高效的过滤以及多种选项，使得Extended Event成为一个远远超越Trace的事件收集器。

从我们熟悉的开始： SQL Trace

从我的经验讲，学习新事物最容易的方式就是从我们已知的知识开始。下面对于 SQL Trace 和Profiler等这些你已熟知的知识介绍，将作为我们理解Extended Event工作方式的基础，且并理解它与SQL Trace 和Profiler的不同。

对于数据库专家，排查SQL查询性能问题是我们经常遇到的问题之一。以前，我们通常会创建一个Trace用于捕捉与我们存储过程或SQL执行相关的事件。我们通常会设置一些过滤条件，如超过一定数量的Reads，特定的执行时间或是消耗的CPU等。

通常打开Profiler来定义trace是一个方便快捷的方式。当我们新建一个trace，也可能是使用一些已有的模板，连接到SQL 实例，并选择需要的时间。在截图1中，我们仅选择了两个事件“RPC:Completed" 和"SQL:StmtCompleted”，并捕捉了相同的数据列。

Figure 1: Selecting events and data columns for a trace

当使用Profiler或SQL Trace时，添加过滤过滤条件总是被推荐的。但是与Extended Event比，使用SQL Trace的问题之一就是晚期过滤。及时我们定义了过滤条件，SQL Trace or Profiler 任然会收集事件实例的全部数据，然后再过滤它。当然，过滤条件依然是非常重要的，它保证了我们的发送到客户端或者目标文件的事件是经过过滤的。
在这个例子中，我们添加一个过滤条件：Reads 大于等于10000，如图2

Figure 2: Defining a trace filter on number of reads

当我们选择好事件和列，设置过滤，以及保存的Trace 文件后，我们就可以启动Trace了。经验丰富的DBA和开发都知道直接运行Profiler会带来严重的性能问题(http://support.microsoft.com/kb/929728 ).因此为了最小化对生产环境的影响，最好的方法是点击开始后立即停止Trace，然后通过"File | Export | Script Trace Definition | For SQL Server 2005 – 2014"菜单导出脚本。这样做之后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细节请查看 http://msdn.microsoft.com/en-us/library/ms190362.aspx。

输出文件路径也是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(http://msdn.microsoft.com/en-us/library/ms186265.aspx))来查找每个值得定义。一下代码中数字10，和41分别代表RPC:Completed 和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 SERVERPROPERTY )。

在这个例子中，我们设置了一个过滤条件，只将Reads大于等于10000 (@bigintfilter = 10000)的查询语句或存储过程发送到目标文件中。再次提醒，这是晚期过滤，所有的事件和信息都会被捕捉。然后在发送到文件或客户端前根据筛选条件移除。

-- 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 Event替代呢？

转换Trace到Extended Events 事件会话

将已经存在的Trace文件定义转换为事件会话，我推荐的方式使用一个存储过程，他的作者是 Jonathan Kehayias。你可以从(https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/)下载这个脚本，名为"sp_SQLskills_ConvertTraceToExtendedEvents"。这个脚本只能在SQL Server 2012及以后版本运行，以为SQL Server 2012以前的版本Extended Events并不支持所有的Trace事件。

手动转换Trace到Extended Events会话

如果由于某些原因你不能使用以上存储过程，微软文档介绍了一个手动转换过程：Convert an Existing SQL Trace Script to an Extended Events Session (https://msdn.microsoft.com/en-us/library/ff878114.aspx)

在你的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

检查Extended Event事件会话

与我们分析由Profiler生成的服务器端Trace脚本一样，我们会通过不同的段落逐步分析Extended Events事件会话是如何创建的。

创建事件会话

脚本首先包含了一个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 语法创建了一个事件会话(http://msdn.microsoft.com/en-us/library/bb677289.aspx)。这点与第一部分中使用sp_trace_create穿件Trace脚本相似，但是参数不完全一样。

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

添加Event和Actions

接下来，Extended Events脚本使用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 Events和Trace关键的不同点需要指出。第一，注意事件名和Actions中收集的数据列(如：client_app_name, database_id)，他们是文本。我们不需要再查询哪些数字对应的事件或者数据列！与SQL Trace相比，书写阅读Extended Events脚本变的更加简单。

其次，注意Extended Events脚本中并没有指定全部的数据列。事实上，许多数据列被定义为事件的默认负载被收集。我们显然不需要在脚本中指定这些默认列。但是我们可以使用UI来查看事件的默认负载由哪些列组成，我们会在下一阶教程中讲解。

这是Trace 和Extended Events一个重要的行为不同。SQL Trace 默认行为是收集所有可能有用的列。然后由用户过滤任何不需要的信息。Extended Events 更加高效，每个事件有一组由最少的数据列组成的默认负载，在事件出发时总会被默认收集。如果我们需要收集任何不再默认负载中的列，我们需要以Actions方式添加他们。例如，在RPC:Completed事件中添加的Actions：client_app_name, database_id, server_instance_name 和session_id，他们都不属于事件的默认负载。收集这些Actions是可选的。

因为只有默认负载事件列会被包含在时间中，因此初始化事件收集的开销相对较小。Actions数据收集是在谓词过滤后才发生的，因此收集大量的Actions，或者高消耗的Actions(如内存Dump),都会增加Extended Events会话的消耗。因而，审慎的选择额外的数据收集对捕获事件尤为重要。我们会在下一阶中详细讨论这个主题。

定义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设置了一个过滤，排除所有小于10000Reads的事件数据。在事件会话定义中，这个过滤，术语为谓词，是一个简单的WHERE子句。

Extended Events执行早期过滤。换句话说，在事件基础数据收集后里立即执行谓词，只有符合过滤条件的事件实例才会被触发。这种工作机制与SQL Trace和Profiler的晚期过滤相比，在数据收集时的开销更小。

添加其他事件

此时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 Events中我们可以设置更多强大的过滤条件，如我们可以使用AND和OR条件，在此我们不做过多讨论。

指定目标

在添加所有事件后，我们使用ADD TARGET 来指定输出目标，SQL Server将收集的数据以及相关的Actions写入目标。在Trace中我们可以选择输出至文件，或者试试写入Profiler，虽然不被推荐。在Extended Events我们也有多个目标可以选择，包括最基本内存存储(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
同样我们可以设置文件大小，以及我们可以设置创建的滚动跟新文件数量

设置事件会话选项

最后，在我们的会话定义中，我们还有许多可选的会话配置项，如最大内存大小以及调度延迟。因为我们在脚本中没有指定这些选项，因此会使用默认值。会话设置会在下一阶中详细讨论。
如果我们再花一分钟回顾一下整个Extended Events会话定义，你会发现它非常直观而且对于这个DDL每一部分的理解不会有任何困难。

运行事件会话

与Trace一样，Extended Event会话不会被默认启动。为了启动一个会话，我们需要使用Listing 14中所示的ALERT语句。

ALTER EVENT SESSION [XE_ReadsFilter_Trace]

 ON SERVER

 STATE=START;

GO

Listing 14: Starting the event session

执行启动后，我们可以运行一段脚本来验证Extended Events是否已经启动。

/* 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 Evetns也有system_health 事件会话，这个与默认Trace并不完全一样。我们在下阶中再看system_health 会话。如果你使用了Availability Groups (AG)，也会有一个AlwaysOn_health 会话一直执行，来收集AG相关的信息以及故障检测事件。

Figure 3: Which traces and event sessions are running?

在我们启动了Trace和Session后，我们可以使用ALTER 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 Events与Trace重要的不同点:会话定义会作为元数据保存在服务器中，并且会被持久化。创建过事件会话后，你就可以根据需要启动或停止它了。

总结

你现在已经知道如何将SQL Trace 的知识映射到Extended Events中了，我们可以使用T-SQL达到我们的目标。我们下一步将详细的看一下DDL, 并且转向Extended Events的UI。我们在一下阶中解决这些问题。

码农公寓