SQL Server扩张事件的利用ring_buffer target时“错过”

2019-10-11 12:19 来源:未知

1,查看扩展事件的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会话的消耗。因而,审慎的选择额外的数据收集对捕获事件尤为重要。我们会在下一阶中详细讨论这个主题。

2,从 sys.dm_xe_session_targets 中查看target输出

 

转换Trace到Extended Events 事件会话

将已经存在的Trace文件定义转换为事件会话,我推荐的方式 使用一个存储过程,他的作者是 Jonathan Kehayias。 你可以从( 这个脚本只能在SQL Server 2012及以后版本运行,以为SQL Server 2012以前的版本Extended Events并不支持所有的Trace事件。

手动转换Trace到Extended Events会话

如果由于某些原因你不能使用以上存储过程,微软文档介绍了一个手动转换过程: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:tempXE_ReadsFilter_Trace.xel' ,
                                   max_file_size = 5 ,
                                   max_rollover_files = 1 )
GO

Listing 7: The Extended Events event session

一,创建扩展事件的会话

总结:  
  以此来看,使用ring_buffer为扩展事件的target,潜在以下问题
  1,解析出来的结果并不可靠(完整),可能无法解析到最近的部分事件。
  2,以下译文中还会提到,ring_buffer作为target可能会撑爆内存的情况,所以要谨慎使用。
  3,同样下文会提到,SSMS的UI对ring_buffer中的事件支持的并不好,对于ring_buffer的target,UI也仅仅是show出来一个XML文件,必须要自己解析,而不像event_file中那样表格化展示(可读性)
  因此要尽量避免在扩展事件中使用ring_buffer target。

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

事件文件类型的Target 以 rollover 方式复用文件,例如,如果 max_rollover_files=3,那么系统中最多保留 3个 xel文件,文件命名:xxx_0.xel,xxx_1.xel,xxx_2.xel。当文件xxx_0.xel达到Max_File_Size时,Target执行一次rollover,将文件:xxx_0.xel删除,创建新文件:xxx_3.xel,并用该文件存储事件数据。

 

添加其他事件

此时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条件,在此我们不做过多讨论。

Step7,指定会话数据的存储(Specify Session Data Storage)

实际情况是,事件(event)实际上在就那里,你无法看到(你预期的事件)是因为sys.dm_xe_session_targets 这个DMV的限制。
这个DMV的目标数据列只能输出大约4 MB的XML数据。
Bob Ward20009年的时候在CSS SQL Server工程师博客中解释了DMV的4 MB格式XML限制的信息。
为了证明这种限制的结果,让我们来看看在SQL Server 2012 SP1 CU7服务器上的系统健康事件会话中包含的事件数量,我可以使用下面的查询来查看信息。

1,以Rollover 方式复用事件文件

我发现在繁忙的服务器上出现问题时,sp_server_diagnostics_component_output事件的大小超过了512KB,
因此当输出XML中包含其中一个事件时,对ring_buffer目标,由DMV返回的数据可能会受到很大限制。

设置事件会话选项

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

Event File target使用File来存储Session Output,当需要存储大量数据集时,使用该选项。

我曾经多次遇到扩展事件中有关ring_buffer target同样的问题,
我想我会写一篇博客文章,解释了我教的所有信息关于ring_buffer target和与之关联的问题。
自从sqlserver 2012发布以及扩展事件新UI的更新,我以后坚决不会再使用ring_buffer target
事实上,正如文章标题所言,我确实很讨厌ring_buffer target,这篇文章中我将会阐述我讨厌ring_buffer target的原因,并且希望说服应该使用file_target代替。

创建事件会话

脚本首先包含了一个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 s.name as xe_session_name,
    cast(st.target_data as xml) as target_data
from sys.dm_xe_sessions s 
inner join sys.dm_xe_session_targets st 
    on s.address=st.event_session_address
where s.name='xe_session_name'

 

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

当Target的名称是 ring buffer时,Session 输出的数据存储在内存 buffers中,通过 sys.dm_xe_session_targets的 target_data 字段查看

这对我来说可能是我日常工作中不再使用ring_buffer作为输出目标的最大原因。
扩展事件UI不支持分解ring_buffer目标中包含的信息, UI的唯一功能是显示由sys.dm_xe_session_targets DMV的target_data列输出的XML。

运行事件会话

与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相关的信息以及故障检测事件。

彩民之家高手论坛 1

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

彩民之家高手论坛 2

  因此就可以说,系统默认自带的sysem_health扩展事件,捕获死锁本身是没有问题的,问题出在扩展事件的输出目标ring_buffer上。
  在不过滤所有的扩展事件情况下,从ring_buffer里面解析出来的数据还有个特点,其不包含最近一段时间的任何一种事件信息。
  也就是说,ring_buffer中解出来的事件信息,是当前时间前一段时间的事件信息,并不包含所有的事件信息,以及最近一段事件所有的事件信息。
  当然你可以说ring_buffer是先进先出的队列模型,那也应该留下新的事件,而不是解析不出来最新的事件信息。

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

四,Ring Buffer Target

当心内存的使用

检查Extended Event事件会话

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

彩民之家高手论坛 3

彩民之家高手论坛 4

定义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的晚期过滤相比,在数据收集时的开销更小。

ring buffer target简单地把数据存储到内存中,这种target模式能够采用两种模式来管理事件:

system_health事件会话特别倾向于收集最多5000个事件但ring_buffer,DMV实际上只能输出一小部分事件会话。
最有可能的是,sp_server_diagnostics_component_output和xml_deadlock_report具有相当大的事件是(占用的空间),因为这两个事件返回的XML的大小取决于它们何时触发的条件的具体情况。

指定目标

在添加所有事件后,我们使用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:tempXE_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
同样我们可以设置文件大小,以及我们可以设置创建的滚动跟新文件数量

  • Drop event
  • Do not drop events
  • Drop full buffer
  • Allocate new buffer

没有UI的支持

总结

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

step1,打开新建会话向导(New Session Wizard)

SELECT
    target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int') AS buffer_memory_used_bytes,
    ROUND(target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int')/1024., 1) AS buffer_memory_used_kb,
    ROUND(target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int')/1024/1024., 1) AS buffer_memory_used_MB,
    DATALENGTH(target_data) AS xml_length_bytes,
    ROUND(DATALENGTH(target_data)/1024., 1) AS xml_length_kb,
    ROUND(DATALENGTH(target_data)/1024./1024,1) AS xml_length_MB
FROM (
SELECT CAST(target_data AS XML) AS target_data  
FROM sys.dm_xe_sessions as s
INNER JOIN sys.dm_xe_session_targets AS st 
    ON s.address = st.event_session_address
WHERE s.name = N'system_health'
 AND st.target_name = N'ring_buffer') as tab(target_data)

从我们熟悉的开始: SQL Trace

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

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

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

彩民之家高手论坛 5

Figure 1: Selecting events and data columns for a trace

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

彩民之家高手论坛 6

Figure 2: Defining a trace filter on number of reads

当我们选择好事件和列,设置过滤,以及保存的Trace 文件后,我们就可以启动Trace了。经验丰富的DBA和开发都知道直接运行Profiler会带来严重的性能问题( ).因此为了最小化对生产环境的影响,最好的方法是点击开始后立即停止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:MyFolderMyTrace. 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:tempReadsFilter_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:MyFolderMyTrace. 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 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替代呢?

Using Xquery to query Extended Events asynchronous file target results

SELECT
    ring_buffer_event_count, 
    event_node_count, 
    ring_buffer_event_count - event_node_count AS events_not_in_xml
FROM
(    SELECT target_data.value('(RingBufferTarget/@eventCount)[1]', 'int') AS ring_buffer_event_count,
        target_data.value('count(RingBufferTarget/event)', 'int') as event_node_count
    FROM
    (    SELECT CAST(target_data AS XML) AS target_data  
        FROM sys.dm_xe_sessions as s
        INNER JOIN sys.dm_xe_session_targets AS st 
            ON s.address = st.event_session_address
        WHERE s.name = N'system_health'
            AND st.target_name = N'ring_buffer'    ) AS n    ) AS t;

什么是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
彩民之家高手论坛 7

另一个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的事件收集器。

 

如下是译文,原文地址:

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

Step5,选择捕获的字段(Capture Global Fields)

彩民之家高手论坛 8

通过TSQL 脚本获取的Target 输出都是以XML格式显示的,通过View Target Data能够以 表格 方式查看Target的输出

 

 

这是我通过电子邮件解释关于ring_buffer目标的最常见问题。一般来说,如下是典型的问题描述:

彩民之家高手论坛 9

 

Step8,查看扩展事件会话的汇总信息,开始创建事件会话。

 

选择Event发生时其他信息,这些信息一般跟Session相关,例如Database_id 和 Database_id 就是Event发生的database 信息。一般会将sql_text也选中,便于查看触发Event的SQL语句。

在生产服务器上配置ring_buffer目标的方式需要非常小心,这对我来说是前所未闻的。
两周之前,Andy Galbraith遇到一个所有连接报701系统内存不足的错误,
经过分析,Andy发现在内存16GB,最大内存(max server memory)配置为11000MB的服务器上,MEMORYCLERK_XE memory clerk 占用了10GB的内存,
问题就在于,一个扩展事件配置了收集最大(MAX_EVENTS_LIMIT)1,000,000 个事件,但是没有配置最大内存限制,
因此内存的使用就基于扩展事件收集到的事件个数,并且没有最大的内存使用限制,那么它就可以使用无限的内存,在内存有限的情况下,从而导致服务器上出现问题。

彩民之家高手论坛 10

彩民之家高手论坛 11

select s.name,
    s.total_regular_buffers*s.regular_buffer_size/1024 as total_regular_buffer_kb,
    s.total_buffer_size/1024 as total_buffer_kb,
    s.buffer_policy_desc,
    s.flag_desc,
    s.dropped_event_count,
    s.dropped_buffer_count
from sys.dm_xe_sessions s

(标题)我为什么讨厌扩展事件中的ring_buffer target

彩民之家高手论坛 12

事情起因:
  排查SQL Server上的死锁问题,一开始想到的就是扩展事件,
  第一种方案,开profile守株待兔吧,显得太low了,至于profile的变种trace吧,垂垂老矣,也一直没怎么用过。
  第二种方案是开启TRACE flag(DBCC TRACEON (3605,1204,1222,-1))将死锁写入error log,也是个不错的选择。
  不过想到系统默认的扩展事件system_health已经捕获了死锁信息(sqlserver.xml_deadlock_report),
  就没必要再重新往error log记一次了,理论上从system_health中就能拿到死锁信息,因此尝试从system_health的ring_buffer target获取死锁信息。

How to Query Extended Events Target XML

 

三,以文件存储Target的数据

  由于sysem_health有两个输出的target,一个ring_buffer,一个是target_file,无奈下从event_file查询捕获的死锁信息,这里又是没问题的,正常捕获到了。

六,查询会话(session)和 target

原因分析:
  参考了sqlskill上的一篇文章,这篇文章深入地解析了这个问题,
  简单说就是:
  ring_buffer并没有“丢失”事件信息,至于为什么解析不出来,要从ring_buffer解析方式开始,ring_buffer扩展事件从sys.dm_xe_session_targets 这个DMV中解析的,
  受到sys.dm_xe_session_targets 这个DMV的目标数据列target_data字段只能容纳大约4 MB的XML数据的限制。
  当ring_buffer捕获的事件(内存中的二进制数据)转换为XML格式大于(大约)4MB的情况下,超过4MB的其他的事件会被被“截断”,
  从sys.dm_xe_session_targets解析出来的XML文件优先输出更早的事件,所以我们预期下的最近发生的事件是无法看到的。
  因此,正如上文中遇到的情况一样:“丢失”部分事件信息,并且没有最近的事件信息。

step2,设置会话属性(Set Session Properties)

彩民之家高手论坛 13

 五,使用SSMS查看Target数据

这里我们可以看到,二进制数据占用的内存大约为1.7MB,不过一旦序列化为XML,文件的大小就变为大约4.7MB,比二进制数据空间要大
问题的本质就在于,扩展事件生成的特点,决定了他紧凑的二进制格式的,但是序列化的格式化XML会为这些事件增加存储空间。

Step6,设置会话时间的过滤器(Set Session Event Filters)

CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH
(MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=10 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = start;

SQL Server 扩展事件捕获的消息,叫做Target,使用Target来存储Events,Target 能够把捕获的消息存储到文件中(扩展名是 .xel),或 memoy buffer 中(Ring Buffer),Target能够以同步或异步方式处理数据,事件的数据都是以XML格式存储。

我们可以观察扩展事件目标占用的内存和DMV中的XML二进制数据占用的内存情况,使用如下查询

彩民之家高手论坛 14

排查经过:
  从sysem_health的ring_buffer中目标中,并没有捕获到预期的曾经发生的死锁事件信息,
  在完全确认发生过死锁的情况下(并且是最近,或者是刚刚),为什么sysem_health“没有能够捕获到”?
  无奈之下单独开了一个捕获死锁事件的扩展事件Session(仅捕获sqlserver.xml_deadlock_report),target目标是存储在ring_buffer中
  在人为刻意地制造一个死锁之后,来检验sysem_health和自定义的捕获扩展事件捕获的死锁信息
  此时意想不到的情况发生了,自定义的扩展事件完整地捕获到了这个死锁信息,而system_health仍旧没有捕获到对应的死锁信息。
  什么情况?

2,从 sys.dm_xe_session_targets 中查看事件文件的存储路径

丢失事件

制定输出数据存储的目标(target),该tab中列出 Event File target 和 ring buffer target。

这意味着要使用数据,您必须打开XML并扫描事件,或编写XQuery以将XML解析为表格形式,这要求您知道事件会话中使用的事件,列和操作定义来真正访问数据。
如果我正在进行短期数据收集,并且不希望它保存到SQL Server 2012上的文件系统中的文件,我只需选择实时视图就可以将数据流式传输回UI的列表中,
在这种情况下,我不必处理XML并可以快速找到我感兴趣的信息。
对于任何长期任务,甚至查看system_health事件会话中的信息,我都使用file_target,UI可以读取和处理事件,无需手动执行任何XQuery。

step3,选择模板(Choose Template)

   如下自定义扩展事件脚本

2,查看会话target的配置

ring_buffer_event_count是RingBufferTarget根元素返回的XML文档的eventCount属性(译者注:ring_buffer_event_count是RingBufferTarget捕获到的事件的总数)
event_node_count是sys.dm_xe_session_targets 这个 DMV中返回的ingBufferTarget/event nodes中事件的个数(两者的差值就是所谓丢失的事件个数)
这里你可以看到ring_buffer target中一共有5000个事件,(原因是)system_health会话基于2012新的MAX_EVENTS_LIMIT选项设定在5000。
不过,仅仅有3574个事件被DMV中的XML输出了出来,剩下有1426个事件仍然不可用(不可见,无法解析出来),尽管他们是滞留在内存中的。
sys.dm_xe_session_targets 的XML文件优先输出更早的事件,所以我们预期下的最近发生的事件是无法看到的。

3,从xel文件中检查target的输出

译者注,如下是system_health中ring_buffer MAX_EVENTS_LIMIT选项设定在5000的值:

sys.dm_xe_session_targets (Transact-SQL).aspx)

  彩民之家高手论坛 15

使用TSQL创建扩展事件的过程比较复杂,但是,我们可以使用另外一种简单的方法:使用扩展时间的创建向导。

死锁捕获结果,sysem_health并没有捕获到预期的死锁事件,尽管他包括了sqlserver.xml_deadlock_report事件

Ring buffer target使用Memory buffer来存储Session Output,如果分配的memory buffer用完,target会将最老的Events删除,以容纳新的Events,使memory buffers中存储的是most recent data。

我从SQL Server中心的一篇文章中得到了下面的代码,它不起作用。我遇到的问题是,当我运行代码时,即使我知道应用程序中刚刚发生了一个死锁事件,它也不会显示任何死锁信息。
似乎我只在system_health会话中看到较旧的死锁,但从来没有看到最新的死锁。我打开了Trace 1222并以这种方式获取信息,那么为什么不这样做。

Target对于扩展事件产生的数据,总是先缓存在内存buffer中,等到内存buffers积累足够数量的数据之后,再将内存中的所有数据写入到文件中。文件中的数据滞后于内存 buffer,这就是异步写(Async Write),能够减少IO的次数,提高IO效率。事件文件类型的taget的扩展名是xel,以XML格式存储Target 数据,使用 sys.fn_xe_file_target_read_file 函数查看事件文件中存储的数据。

SELECT  xed.value('@timestamp', 'datetime') AS Creation_Date ,
        CAST(REPLACE(REPLACE(REPLACE(CAST(xed.query('.') AS NVARCHAR(MAX)),'&lt;', '<'),'&gt;', '>'),'&amp;apos;','''') AS xml) AS Extend_Event
FROM    ( SELECT    CAST([target_data] AS XML) AS Target_Data
          FROM      sys.dm_xe_session_targets AS xt
                    INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
          WHERE     xs.name = N'system_health'
                    AND xt.target_name = N'ring_buffer'
        ) AS XML_Data
        CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
        AS XEventData ( xed )
ORDER BY Creation_Date DESC

select * from sys.dm_xe_session_targets

SELECT    CAST([target_data] AS XML) AS Target_Data
FROM      sys.dm_xe_session_targets AS xt
                    INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
WHERE     xs.name = N'system_health'
          --AND xt.target_name = N'ring_buffer'

-- Parse the XML to show wait details
SELECT event_table.*
    FROM (
        SELECT CAST(event_data AS XML) xml_event_data 
        FROM sys.fn_xe_file_target_read_file(N'pathLogsystem_health*', NULL, NULL, NULL)
    ) AS event_table
    CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
WHERE  event_xml.value('(./@name)', 'varchar(1000)') IN ('xml_deadlock_report')   

sys.fn_xe_file_target_read_file (Transact-SQL).aspx)

 

扩展事件可以同步生成事件数据(并异步处理该数据),这为事件处理提供了灵活的解决方案。此外,扩展事件提供以下功能:

彩民之家高手论坛 16

  • 一种跨服务器系统处理事件的统一方法,同时使用户可以隔离特定的事件进行故障排除。
  • 基于 Transact-SQL 的完全可配置的事件处理机制。
  • 可以动态监视活动进程,同时对这些进程的影响最小。

Regular buffer:大多数时间下,扩展事件会话使用的是常规的buffer,这些buffer容量大,存储很多事件的信息。特别地,每一个扩展事件会话都会有三个或更多的buffer。常规buffer的分配是由SQL Server决定的,而SQL Server基于 MEMORY_PARTITION_MODE 选项来设置内存分区,常规buffer的size和 MAX_MEMORY选项的设置相同。

sys.dm_xe_sessions (Transact-SQL).aspx)

Ring buffer target将事件数据保存到内存中,事件数据以XML格式存储。一旦事件数据把分配的内存Buffers 用尽,那么最老的事件数据将被清除。

二,查看扩展事件捕获的消息

  • 第一种模式是严格地先进先出(first-in first-out ,FIFO),也就是说,当分配的内存被target耗尽时,从内存中移除创建时间最早的事件。
  • 第二种模式是per-event 先进先出模式,也就是说,每一种类型的事件都持有一个计数。在这种模式下,当分配的内存被target耗尽时,每个类型中创建时间最早的事件从内存中被移除。

当Target的名称是 event file 时,Session 输出的数据实际上是存储在Event file中。

SELECT name, target_name, CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
   ON (xe.address = xet.event_session_address)
WHERE xe.name = 'session_name'

SQL Server Extended Events Targets.aspx)

该Tab分为两个Pane,左边Pane用于搜索Event,在Event library中,输入需要进行追踪的Event 名称,在选择一个Event之后,Wizard会显示出该Event的Description和 Event Fields,Event fields 是描述Event输出的字段。右边Pane用于列出已经选中的Events 列表。

字段注释:

step4,选项捕获的事件(Select Events to Capture)

SQL Server 扩展事件(Extended Event)是用于服务器的常规事件处理系统,是追踪SQL Server系统运行状态的神器,同时也是一个日志记录工具,扩展事件完全可以取代SQL追踪(SQL Trace),扩展事件的设计功能:

buffer_policy_desc:用于表述当buffer耗尽时,扩展事件会话是如何处理新触发的事件:

1,存储模式

彩民之家高手论坛 17

select s.name as xe_session_name,
    st.target_name,
    st.execution_count,
    st.execution_duration_ms/st.execution_count as avg_execution_ms,
    st.target_data
from sys.dm_xe_session_targets st 
inner join sys.dm_xe_sessions s 
    on st.event_session_address=s.address

彩民之家高手论坛 18

参考文档:

Realistic troubleshooting example of extended events (XEvent) usage in SQL Server 2008 – Part 1

SELECT *, CAST(event_data AS XML) AS 'event_data_XML'
FROM sys.fn_xe_file_target_read_file('file_name*.xel', NULL, NULL, NULL)
  • 由于扩展事件引擎不识别事件,因此,引擎可以将任何事件绑定到任何目标,因为引擎不受事件内容约束。
  • 事件与事件使用者不同,后者在扩展事件中称为“目标”(Target),也就是说任何目标可以接收任何事件。此外,引发的任何事件均可供目标自动使用,这样可以记录或提供额外的事件上下文。
  • 事件不同于在事件激发时要执行的操作。因此,任何操作可以与任何事件相关联。
  • 谓词可以动态筛选事件的激发,从而增强了扩展事件基础结构的灵活性。
TAG标签:
版权声明:本文由彩民之家高手论坛发布于彩民之家高手论坛,转载请注明出处:SQL Server扩张事件的利用ring_buffer target时“错过”