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。我们在一下阶中解决这些问题。

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

标签：