扩大事件,Server增添事件的运用ring_buffer

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

 

开始采用Extended Events 最大的阻碍之一是需要使用Xquery和XML知识用来分析数据。创建和运行会话可以用T-SQL完成,但是无论使用什么目标,数据都会被转换为XML。这个限制在SQL Server 2012中已经被移除,它已经实现了Extended Events 用户界面。在SQL Server 2014中也基本没有变化,如我们在进阶2中所见,这个UI提供了创建和管理会话功能。在本阶中,我们将探索捕获数据的查看和处理的功能。

--SQL Server 扩展事件具有高度可伸缩且高度可配置的体系结构,
--使用户能够按需收集解决性能问题或确定性能问题所需的信息。
--1. 性能损耗小
--2. 可配置性高
--3. 可捕获底层事件

  • 由于扩展事件引擎不识别事件,因此,引擎可以将任何事件绑定到任何目标,因为引擎不受事件内容约束。
  • 事件与事件使用者不同,后者在扩展事件中称为“目标”(Target),也就是说任何目标可以接收任何事件。此外,引发的任何事件均可供目标自动使用,这样可以记录或提供额外的事件上下文。
  • 事件不同于在事件激发时要执行的操作。因此,任何操作可以与任何事件相关联。
  • 谓词可以动态筛选事件的激发,从而增强了扩展事件基础结构的灵活性。

事情起因:
  排查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获取死锁信息。

查看数据

当创建并启动会话后,我们有两种选择查看事件数据:实时数据查看器和目标数据查看器。
为了完成例子中的内容,你可以创建Listing 1中所示的XE_SampleCapture会话。它将捕捉batch,Statement和Procdeure 完成事件以及相关事件字段:
query_hash - 标识具有相似逻辑但是查询计划不同的查询
query_plan_hash 标识具有不同的查询计划但有相似逻辑树的查询计划

这两字段分别关于查询指纹和计划指纹。我们使用这些字段主要用于分析Ad-Hoc负载,查找非常相似但却有细微区别的查询,如具有不同的值(difference values of their concatenated literals)。

CREATE EVENT SESSION [XE_SampleCapture] ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION ( sqlserver.database_id, sqlserver.query_hash,
    sqlserver.query_plan_hash )
    WHERE ( [sqlserver].[is_system] = ( 0 ) ) ),
ADD EVENT sqlserver.sp_statement_completed ( SET collect_statement = ( 1 )
    ACTION ( sqlserver.database_id, sqlserver.query_hash,
    sqlserver.query_plan_hash )
    WHERE ( [sqlserver].[is_system] = ( 0 ) ) ),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION ( sqlserver.database_id, sqlserver.query_hash,
    sqlserver.query_plan_hash )
    WHERE ( [sqlserver].[is_system] = ( 0 ) ) ),
ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sqlserver.database_id, sqlserver.query_hash,
    sqlserver.query_plan_hash )
    WHERE ( [sqlserver].[is_system] = ( 0 ) ) )
ADD TARGET package0.event_counter,
ADD TARGET package0.event_file ( SET filename = N'C:tempXE_SampleCapture' ,
                                  max_file_size = ( 512 ) ),
ADD TARGET package0.histogram ( SET filtering_event_name = N'sqlserver.sql_statement_completed' ,
                                 slots = ( 16 ) ,
                                 source = N'sqlserver.database_id' ),
ADD TARGET package0.ring_buffer ( SET max_events_limit = ( 10000 ) ,
                                   max_memory = ( 4096 ) )
WITH ( MAX_MEMORY = 16384 KB ,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
        MAX_DISPATCH_LATENCY = 30 SECONDS ,
        MAX_EVENT_SIZE = 0 KB ,
        MEMORY_PARTITION_MODE = NONE ,
        TRACK_CAUSALITY = ON ,
        STARTUP_STATE = OFF );
GO

为了生成一些样本数据,我执行了Jonathan Kehayias 为 AdventureWorks创建的随机负载生成脚本(

 

--SQL Server 扩展事件包
--包是用于SQL Server 扩展事件对象的容器。
--扩展事件包有三种类型
----package0 - 扩展事件系统对象。这是默认包。
----sqlserver - SQL Server 相关对象。
----sqlos - SQL Server 操作系统(SQLOS) 相关对象。
--查看扩展事件包
SELECT * FROM sys.dm_xe_packages P
 
--每个事件包包括:事件,目标,操作,类型,谓词,映射
 
 
--通道
--通道用于标识事件的用户
 
--关键字
--关键字是特定于应用程序的,并且使得对相关事件的分组更加细化,
--这样您能更容易地指定和检索要在会话中使用的事件
--获取关键字信息
select map_value Keyword from sys.dm_xe_map_values
where name = 'keyword_map'
 
--目标
--目标是指事件使用者。目标在触发事件的线程中同步处理事件
--或在系统提供的线程中异步处理事件。通常,在必须保持特定
--数据排序时将使用同步处理。扩展事件提供了多个目标,您可
--以根据需要将其用于定向事件输出
 
--操作
--操作是对事件做出的一个编程方式的响应或一系列响应。
--操作与事件绑定在一起,并且每个事件都可能具有唯一的一组操作。
 
--谓词
--谓词是一组逻辑规则,用于在处理事件时计算这些事件。
--这可以使扩展事件用户根据特定条件有选择地捕获事件数据。
 
--类型
--由于数据是排列在一起的字节集合,因此需要使用字节集合的长度和特征来解释这些数据
SELECT
DISTINCT OB.object_type
FROM sys.dm_xe_objects OB
 
--映射
--映射表用于将内部值映射到字符串,这使用户可以知道该值代表什么。
--用户可以获得关于内部值真正含义的说明,而不是只能够获取数值。
SELECT map_key, map_value
FROM sys.dm_xe_map_values
 
--查看扩展事件
SELECT xp.[name] AS [Package_Name], xo.*
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xp.[guid] = xo.[package_guid] 
WHERE xo.[object_type] = 'action'
ORDER BY xp.[name];
 
--查看某个扩展对象的列
SELECT *
FROM sys.dm_xe_object_columns

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

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

实时数据查看器

Live Data Viewer总是可用的,无论会话设置了什么类型的目标,甚至是没有设置目标,它会简单的显示出捕获的数据。打开查看器仅需右键选择任何运行中的会话并选择"Watch Live Data"。你可以使用上面创建的XE_SampleCapture会话,如果喜欢也可以使用system_health会话。图1显示了一些数据样例。

图片 1

Figure 1

初始化后可能不会显示任何数据,尤其是你尝试打开system_health 会话时,因为事件显示在查看器中的速度主要取决于你收集的事件和谓词。如果你有一个可以快速捕捉数据的会话,那么一旦事件会话的调度周期到达,或者缓冲区被填充,事件会被调度到查看器和会话中定义的其他目标。

调度周期和内存缓冲
Extended Events引擎首先将事件数据流写入指定会话的中间缓冲区。调度周期是一个会话级别的选项,用于定义将事件数据从内存写入目标的频率,以秒为单位。

除非你导出数据,否则实时查看器中的数据并不会被持久化。你必须停止实时查看器的数据推送后才能使用这个选项(通过 Extended Events主菜单中的Stop Data Feed)。选择实时数据查看器窗口,转到Extended Events | Export to来选择导出目标。

你可以随时停止数据推送或者关闭查看器,同时在前一阶中我们提到过,SQL Server如果检测对性能有负面影响时,查看器会自动停止。

 

 
--会话会话是一种将多个扩展事件对象链接到
--一起进行处理的方法—事件包含动作,将被目标所消耗。
--会话可链接任何注册的数据包中的对象,任何数量的会话
--都可以使用同一个事件、动作等。
--查看会话
SELECT * FROM sys.dm_xe_sessions;
Go
 
--在性能考虑方面涉及到
--1.那些事件需要被监控,这些事件的触发频率
--2.如何消耗事件,使用异步还是同步
--3.如果处理事件丢失(如果没有足够的内存来缓冲某个事件,是否可将其直接丢弃或强制其不丢失)
 
--事件的周期
 
--1.执行检查以查看是否有任何扩展事件会话正在监控该事件。如果没有,控制权将返给包含该事件的代码,然后继续进行处理。
--2.确定事件的负载,将所需的全部信息都收集到内存中—换言之,构建事件的负载。
--3.如果为该事件定义了任何谓词,则执行它们。此时,谓词结果可能是不应消耗该事件。如果是这种情况,控制权将返给包含该事件的代码,然后继续进行处理。
--4.此时系统已得知事件将被消耗,因此将执行链接到该事件的所有动作。现在事件已具有完整的负载,已为消耗做好准备。
--5.将事件提供给同步目标(如果有的话)。
--6.如果存在任何异步目标,将会缓冲该事件以便随后进行处理。
--7.控制权将返给包含该事件的代码,然后继续进行处理。

图片 2

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

   如下自定义扩展事件脚本

目标数据查看器

另外一个查看会话数据的方式是右键点击目标,然后然后选择View Target Data…选项。这个菜单根据选择的目标显示不同的视图

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

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;

查看 event_file目标数据

如果是选择了一个event_file 目标,则显示已经被写入硬盘的目标文件中的事件数据。这些信息不会更新,仅显示打开文件那一刻已经存在的数据。

图片 3

Figure 2

对于一个event_file目标,这个选项与实施数据查看器对比的好处是它不需要SQL Server同时将数据流写入目标和Management Studio。它仅仅读取已经被目标消费后的数据。

event_file 目标捕捉的数据可以在事件会话停止后在SQL Server Management Studio 中查看,因为他们会以.xel文件保存在硬盘上。你可以拖拽一个.xel文件到SSMS中,或者使用 File | Open | File…菜单选项打开一个.xel文件。system_health会话的默认文件路径在默认的错误日志文件夹中。

这个查看器看起来极为相似,除了windows窗口中的文件名不一样 (XE_SampleCapture_0_131061823727010000.xel), 实时查看器显示Live data和目标查看器显示event_file。

图片 4

Figure 3

一,创建扩展事件的会话

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

查看ring_buffer目标数据

当使用ring_buffer目标是,View Target Data选项不是非常有帮助,因为它以整个的XML链接格式显示。

图片 5

Figure 4

点击链接后,数据以xml格式显示,但是对于分析也不是很有帮助。

图片 6

Figure 5

当使用ring_buffer 目标是,我们需要使用XQuery分析事件数据。

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

图片 7

查看event_counter, histogram 和pair_matching 目标数据

最后三种目标event_counter, histogram, 和pair_matching,需要你查看目标数据或者使用XQuery分析数据,这是仅有的两种查看聚合数据的方式。使用我们的XE_SampleCapture会话,或其他具有histogram 目标的会话,右键选择histogram 目标并打开目标数据视图,如图6所示的输出。

图片 8

Figure 6

histogram 目标数据视图开始可能是空的,也可能会有数据。这个查看器需要被刷新才会显示最新数据。为了手动跟新它,可以在histogram 目标窗口中右键点击Refresh,或者你可以选择 Refresh Interval 让窗口定时更新。

图片 9

Figure 7

histogram, event_counter 和pair_matching等三种目标在内存中保存数据,一旦你停止事件会话,内中种的数据也会飞释放,因而视图中的数据也会消失。如果你需要保存数据,你可以通过右键的Copy选项,或者右键的Export to CSV选项导出.csv文件(请参考图7)。

你可以在停止一个会话后选择复制或者保存当前显示在目标数据查看器中的数据,但是对于这些目标,一旦你在停止会话后刷新了查看器,数据将会丢失。

图片 10

Figure 8

图片 11

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

显示选项:定制列

无论哪种查看器,你都可以定制显示的列。默认情况下,只有事件名和时间戳显示在顶部的面板中,因为这两列是所有所有事件所共有的。当你选中顶部面板中的列时,详细面板会显示所有捕获的字段。想要在顶部面板中产看任何字段,你可以在详细面板中右键选择所需的列,并选择Show Column in Table。在图9中,我们添加了duration 字段到顶部面板中。

图片 12

Figure 9

我们可以重复以上步骤将我们需要的字段都添加到顶部面板中。这些列可以通过拖拽列名进行左右移动位置。或者,你可以通过 Extended Events工具栏上的Choose Columns… 按钮,或者Extended Events | Choose Columns… 菜单选项来选择列或者配置列的顺序。如果你没有看Extended Events工具栏,可以使用View | Toolbars菜单来选择。

图片 13

Figure 10

在查看器中可以使用的技巧之一就是合并列,换句话说你可以根据已有的两列或更多列来创建一个新列。这对捕捉查询语句存储在不同字段的事件非常有帮助。例如sp_statement_completed 事件使用statement 字段存储查询语句,batch_completed 事件使用batch_text 字段。如果你同时将这两个字段都加到顶部面板中,你会发现对于batch_completed 事件statement 字段总是为NULL,同样,对于sp_statement_completed事件batch_text 字段也为NULL。

图片 14

Figure 11

数据在不同的字段中会阻碍我们的分析,但是如果将他们合并为一列缓解了这一问题。在Choose Columns… 窗口的右下角你可以找到一个Merged columns。选择New,为新列提供一个名字,并选择你要合并的列,如图12所示。

图片 15

Figure 12

新的合并列将以[QueryText]为名显示在查看器中。注意合并列选项专为字符串连接设计,因此如果你选择了非字符串数据类型,并不是所有的数据都可以正常显示。例如,如果你尝试连接logical_reads 和database_id,它金辉显示logical_reads。

在你根据所需的字段定制查看器后,在需要的情况下,你可以通过 Extended Events菜单来保存配置,或者通过Display Settings按钮。

 图片 16

Figure 13

配置文件以.viewsetting 文件格式保存,然后在以后需要查看或分析event_file 目标时,通过同样的菜单打开这个文件。这个文件可以被共享或者保存在多个用户可以访问的网络地址中。最后,一旦顶部面板中显示了所有你需要的列后,如果你要在查看器中移除详细面板,可以选择Extended Events | Show Details Pane菜单。

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

 

分析数据

查看器提供了一个我们所熟悉的事件视图。但是与Profiler UI不同,在这里我们有能力对列进行排序并过滤数据,extended events查看器提供了更好的数据分析选项,我们不再需要将数据在导出到表中,或者使用第三方工具。

图片 17

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

排序

你可以做的最简单的事情之一就是对事件排序。仅需右键选择列名然后选择Sort Ascending 或Sort Descending排序(你也可以直接点击列名排序,再次点击的时候以反序排序)。当你想要快速的找到执行时间最长,IO最高的事件时,对UI上的数据排序是最简单的方式。

step3,选择模板(Choose Template)

  图片 18

过滤

使用工具栏上的Filters… 按钮(或者Extended Events | Filter…菜单)对查看器中的数据进行过滤。我们可以使用时范围或者其他任何字段进行过滤,包括为事件收集的全局字段。进一步,我们可以通过使用AND,OR和其他运算(=, <>, Like, Not Like, Null等)创建复杂的条件,就像我们在谓词中一样。

图片 19

Figure 14

应用过滤后,结果集可以被保存在新的.xel文件中。如果原始的.xel文件被关闭时没有移除过滤条件,请注意重新打开文件时,这些过滤依然生效。一个好的习惯是检查当前显示的事件数量,来判断是否有过滤条件被应用在了分析上,如图15所示。

图片 20

 

Figure 15
可以通过filters 窗口或者选择Extended Events | Clear All Filters菜单来移除过滤条件。

图片 21

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

合并.xel 文件

在我们经常需要过滤结果来分析数据的同时,在一些情况下,我们也需要从多个文件中抓取数据进行全面的分析。一个例子,当我们在跨越多个节点的Availability Group中排除故障时,你可能为每一个节点设置一个会话,然后将多个节点中的目标文件合并在一起查看。为完成这个目标,通过SSMS选择File | Open | Merge Extended Events Files菜单,找到文件路径并将他们添加到窗口中,然后选择OK。

图片 22

Figure 16

合并后的文件将按照一个结果集输出进行分析,同时你可以保存为单独的.xel文件。

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

原因分析:
  参考了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文件优先输出更早的事件,所以我们预期下的最近发生的事件是无法看到的。
  因此,正如上文中遇到的情况一样:“丢失”部分事件信息,并且没有最近的事件信息。

搜索

Profiler UI中也提供了搜索功能,这对于搜索查询语句中的字符尤为有用。Extended events 查看器提供了同样的功能,位于工具栏中的望远镜图标(或选择Extended Events | Find菜单)。

图片 23

Figure 17

在Find窗口中你可以搜索任何已经添加到顶部面板的字段。如果你没有在查看器的顶部面板添加任何额外的字段,默认情况下仅可以搜索name 和timestamp列。每次仅可以在一个列中进行搜索,你可以选择其他的标准的搜索选项,包括匹配大小写和全字匹配,同时也可以使用通配符或者正则表达式。

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

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

标记行

当找到含有我们感兴趣的数据行时,我们可以在查看器中使用书签标记他们(熟悉使用Profiler的用户过去可能经常会写下行号,这都已经成为过去时了)。选中我们感兴趣的行,然后点击工具栏中的Bookmark 按钮(或者点击右键菜单中的Toggle Bookmark)即可。书签图标会显示在查看器的最左边。

 图片 24

图片 25

Figure 18

当你标记完所有感兴趣的行后,你可以使用工具栏上的向前向后导航按钮快速的访问他们(再不需要无止境的滚动鼠标了)。这些按钮使你可以轻松的浏览整个文件,仅停止在那些感兴趣的事件上。如果过你想要移除某个书签,仅需右键点击行后选择Toggle Bookmark。如果你想要删除所有书签可以使用Clear All Bookmarks按钮。请注意,当你关闭文件并再次打开时所有的书签都会丢失。

图片 26

 

分组

当我们在事件数据中合并列、过滤数据和查找指定值时,可能很有趣并且对我们分析数据有帮助,但是它们不能帮我们从整体上找到数据的发展趋势或者异常现象。进入分组,这个分析选项是extended events 查看器远远超越Profiler的原因之一。对于查看器顶部面板中的任何字段选,你都可以右键点击它并选择Group by this column。

图片 27

图片 28

Figure 19

你也可以使用Grouping… 按钮或者 Extended Events | Grouping… 选项,他们的好处是你可以轻松的使用多个列作为分组条件。在查看器中,在我已经使用其中一列进行分组后,如果我右键选择另外一列进行分组,原来的分组条件将被移除。在很多情况下你可能会需要根据多个列进行分组,这时,使用分组窗口将允许你选择多列,并设置列的顺序。

图片 29

图片 30

Figure 20

通用的分组字段包括:

  • event (事件名称)
  • object_name or object_id
  • database_name or database_id
  • login fields (e.g. username, nt_username)
  • client_app_name
  • client_hostname

在duration, logical_reads 或CPU等字段上分组没有任何意义。因为他们提供的是事件的度量指标,但你并不是在寻找那些20ms中完成的事件,或者需要特定IO数量的事件。 而是在某一服务器,或者特别的程序,或数据库的事件中寻找某些模式或者异常情况。

尝试使用Statement(或Batch_Text)进行分组的情况会经常遇到,因为我们想找到那些查询会被频繁的执行。当我们使用SQL Trace 捕获数据时,第三方工具可以为我们提供这些分析,如ClearTrace 和ReadTrace。这些工具会格式化文本数据并进行聚合,一次你可以查看指定的查询或存储过程的执行频率。

不幸的是,Extended Events不具有格式化数据功能。如果你尝试在statement 或batch_text (或我们定义的 QueryText) 字段上进行分组,你最终得到的结果可能是所有数据都只有一行。

图片 31

Figure 21

你需要在query_hash 或query_plan_hash字段上进行分组,而不是在查询文本上。这两个字段在 SQL Server 2008 中被添加到sys.dm_exec_query_stats 管理视图,通常也被称为"query fingerprints"。

具有相同文本的查询具有相同的query_hash,因此可以使用它作为分组条件查看一个查询的执行频率。

图片 32

Figure 22

一旦你可以将相同的查询进行分组,你接下来就可以对每个分组计算平均值,最大值,最小值等额外的分析。

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

 

聚合

最后一个分析工具是使用UI上的聚合选项。在应用聚合前你必须使用至少一个字段进行分组。对于非数字字段,数学计算无法被应用(如,batch_text, database_id, xml_report),唯一可用的聚合选项是COUNT。对于数字字段,额外的选项包括SUM, MIN, MAX, 和AVG。我们可以在字段上点击右键并选择 Calculate Aggregation…,应用聚合选项。你可以可以使用工具栏中的 Aggregation…按钮,或者Extended Events | Aggregation…菜单。

图片 33

Figure 23

图24展示了聚合后的结果。

图片 34

Figure 24

你可以为不同的字段应用不同的聚合方法,如AVG 应用到duration, MAX应用到logical_reads,但是对于一个字段每次只能应用一个聚合选项。也就是对于一个字段你仅可以查看AVG 或者只查看MAX。这里有一个有趣的变通方式可以为相同的字段应用不同的聚合选项。你可以创建一个合并列来表示那一列。例如,如果你即想看duration的平均值也想看duration的最大值,你可以首先将AVG 聚合函数应用到duration列上,然后我们在duration 上创建一个合并列(稍不同的名字),另外一列选择一个总是为零的字段如connection_reset_option。一旦你创建了合并列,你就可以在它上面应用MAX 了。

 图片 35

图片 36

Figure 25

关闭和重新打开文件时,合并列是被持久化的。但是分组和聚合却不可以,他们并不会被保存在.viewsetting文件中。

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

 

总结

SQL Server 2012中,用户最大的胜利是它引入了目标数据查看器,这意味着我们不在需要T-SQL和XQuery就可以分析事件数据了。目标数据查看器是的我们可以产看除ring_buffer 以外所有的目标事件数据(还有etw_classic_sync_target,它仅可以通过 ETW 工具进行查看)。

另外, Extended events UI中比Profiler UI提供了更多重要的数据分析选项。除了搜索和过滤,我们现在可以对数据进行排序,分组,聚合等复杂的分析, 而不再需要将数据导入表中并使用SQL进行分析,或者使用ReadTrace这样的第三方工具。

图片 37

 

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

如下是译文,原文地址:

图片 38

 

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

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

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

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

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

丢失事件

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

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

图片 39

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

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

实际情况是,事件(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服务器上的系统健康事件会话中包含的事件数量,我可以使用下面的查询来查看信息。

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

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;

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

图片 40

三,以文件存储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文件优先输出更早的事件,所以我们预期下的最近发生的事件是无法看到的。

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

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

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

图片 41

事件文件类型的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,并用该文件存储事件数据。

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

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

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

图片 42

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

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

SELECT *, CAST(event_data AS XML) AS 'event_data_XML'
FROM sys.fn_xe_file_target_read_file('file_name*.xel', NULL, NULL, NULL)

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

四,Ring Buffer Target

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

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

 

1,存储模式

没有UI的支持

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

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

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

图片 43

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

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

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'

当心内存的使用

 五,使用SSMS查看Target数据

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

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

 

图片 44

六,查询会话(session)和 target

1,查看扩展事件的Session 配置

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

字段注释:

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

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

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

2,查看会话target的配置

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

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

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

 

 

参考文档:

SQL Server Extended Events Targets.aspx)

sys.dm_xe_sessions (Transact-SQL).aspx)

sys.dm_xe_session_targets (Transact-SQL).aspx)

sys.fn_xe_file_target_read_file (Transact-SQL).aspx)

How to Query Extended Events Target XML

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

Using Xquery to query Extended Events asynchronous file target results

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:扩大事件,Server增添事件的运用ring_buffer

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。