SQLServer 扩展事件(Extended Events)

SQL Server 扩展事件具有高度可伸缩且高度可配置的体系结构，使用户能够按需收集解决性能问题或确定性能问题所需的信息。扩展事件是使用非常少的性能资源的轻型性能监视系统。扩展事件可以同步生成事件数据（并异步处理该数据），这为事件处理提供了灵活的解决方案。sqlserver 2008 开始了扩展事件功能，到 sqlserver 2012 后，扩展事件进行了可视化的界面操作。

官方导航相关信息：

主题	说明
​​SQL Server 扩展事件简介​​	介绍扩展事件并说明如何能使用该功能。
​​SQL Server 扩展事件概念​​	介绍了与扩展事件有关的主要概念。
​​使用 SQL Server 扩展事件​​	总结了用于扩展事件的 DDL 语句和视图。
​​SQL Server 扩展事件操作指南主题​​	包含如何获取事件相关信息的操作指南主题，这些信息可用于创建和更改扩展事件会话。同时提供了端到端的扩展事件会话以说明如何使用此功能监控系统活动。

​​SQL Server 扩展事件包​​	介绍了扩展事件包，扩展事件会话运行期间这些包中的对象将用于获取和处理数据。
​​SQL Server 扩展事件目标​​	介绍了在事件会话期间可接收数据的事件使用者。
​​SQL Server 扩展事件引擎​​	介绍了可实现和管理扩展事件会话的引擎。
​​SQL Server 扩展事件会话​​	介绍了扩展事件会话。

扩展事件可以说是sql server  profiler  的改造，将其放到系统管理中，可以用sql 脚本实现多样化监控和处理。

对于扩展事件与 profiler 的操作比较，参考一位大侠博客：​​SQLSERVER2012里的扩展事件初尝试 ​​

扩展事件 “包”  有几个相关概念：事件，目标，操作，类型，谓词，映射 （​​更多参考： SQL Server 扩展事件包​​）

（概念参考官方说明，开始有些难以理解）

事件：

事件是程序（例如 SQL Server）的执行路径中的相关监视点。事件触发即表明已经到达相关点，并具有自事件触发以来的状态信息。

目标:

目标是指事件使用者。目标在触发事件的线程中同步处理事件或在系统提供的线程中异步处理事件。通常，在必须保持特定数据排序时将使用同步处理。扩展事件提供了多个目标，您可以根据需要将其用于定向事件输出。（更多穿单裤：​​ SQL Server 扩展事件目标​​）

操作:

操作是对事件做出的一个编程方式的响应或一系列响应。操作与事件绑定在一起，并且每个事件都可能具有唯一的一组操作。

类型：

由于数据是排列在一起的字节集合，因此需要使用字节集合的长度和特征来解释这些数据。该信息将封装在 Type 对象中。
下面是为包对象提供的类型：event，action，target，pred_source，pred_compare，type

谓词：

谓词是一组逻辑规则，用于在处理事件时计算这些事件。这可以使扩展事件用户根据特定条件有选择地捕获事件数据。

映射：

映射表用于将内部值映射到字符串，这使用户可以知道该值代表什么。用户可以获得关于内部值真正含义的说明，而不是只能够获取数值。

相关概念：​​扩展事件引擎​​​，​​扩展事件会话​​  可参考官方文档。

看概念很难理解，创建一个示例说明吧。功能是：查询超过 3 秒的执行语句

--  删除事件会话
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'es_slow_query')
    DROP EVENT SESSION [es_slow_query] ON SERVER 
GO

--  创建事件会话
CREATE EVENT SESSION [es_slow_query] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlos.task_time,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id)
    WHERE ([duration]>=(3000000))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlos.task_time,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id)
    WHERE ([duration]>=(3000000))) 
ADD TARGET package0.event_file(
    SET filename=N'D:\Program Files\Microsoft SQL Server\slow_query',   --存储路径及文件名前缀
    max_file_size=(10),     --最大文件大小，单位MB
    max_rollover_files=(4)) --启用文件滚动存储的最大文件数
WITH (
    MAX_MEMORY=4096 KB,             --最大内存大小
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,--事件保留模式：ALLOW_SINGLE_EVENT_LOSS/ALLOW_MULTIPLE_EVENT_LOSS/NO_EVENT_LOSS
    MAX_DISPATCH_LATENCY=30 SECONDS,--最大调度滞后事件，单位秒。0秒为无限制。
    MAX_EVENT_SIZE=0 KB,            --最大事件大小
    MEMORY_PARTITION_MODE=NONE,     --内存分区模式：无(NONE)/每个节点(PER_NODE)/每个cpu（PER_CPU）
    TRACK_CAUSALITY=OFF,            --因果关系跟踪，跟踪事件彼此相关的方式
    STARTUP_STATE=ON)               --服务器启动时是否启动事件会话
GO

--  启用（停止）事件会话（START / STOP）
ALTER EVENT SESSION [es_slow_query] ON SERVER STATE=START
GO

首先了解的是扩展事件元数据：

--  针对每个加载到服务器地址空间的模块返回一行
SELECT * FROM sys.dm_os_loaded_modules t1
WHERE EXISTS(SELECT * FROM sys.dm_xe_packages t2 WHERE t1.base_address=t2.module_address)

--  对事件包显示的每个对象都返回一行( 可以是 事件/操作/目标/谓词/类型)
--  这里只查看 “操作”信息
SELECT * FROM sys.dm_xe_objects 
WHERE name = 'task_time' AND object_type='action'
AND package_guid IN(SELECT guid FROM sys.dm_xe_packages WHERE name IN('sqlos'))
UNION ALL
SELECT * FROM sys.dm_xe_objects 
WHERE name IN( 'database_name','nt_username','sql_text','transaction_id') AND object_type='action'
AND package_guid IN(SELECT guid FROM sys.dm_xe_packages WHERE name IN('sqlserver'))
/*
sys.dm_xe_objects 查看包内容：
sqlos，sqlserver 和 package0 均为包名称。

sqlserver.rpc_completed 为包中的事件；
sqlos.task_time,sqlserver.database_name 为包中的操作
package0.event_file 为包中的目标
[duration] 为包中的谓词，只是包名称没写出来
*/

--  【每个包对应的每个内容！】
SELECT T1.name,T1.description,T2.name,T2.object_type,T2.description
FROM sys.dm_xe_packages t1
INNER JOIN sys.dm_xe_objects t2 ON T1.guid=T2.package_guid

--  返回一个内部数字键到可读文本的映射 （当前无信息）
SELECT * FROM sys.dm_xe_map_values
WHERE object_package_guid IN(SELECT guid FROM sys.dm_xe_packages WHERE name IN('sqlos','sqlserver'))
AND name IN( 'task_time' ,'database_name','nt_username','sql_text','transaction_id')

SELECT * FROM sys.dm_xe_map_values T0
INNER JOIN sys.dm_xe_objects T1 ON T0.object_package_guid=T1.package_guid AND T0.name=T1.name

--  返回所有对象的架构信息
SELECT * FROM sys.dm_xe_object_columns T0
INNER JOIN sys.dm_xe_objects T1 ON T0.object_package_guid=T1.package_guid AND T0.name=T1.name
WHERE object_package_guid IN(SELECT guid FROM sys.dm_xe_packages WHERE name IN('sqlos','sqlserver'))
AND T0.name IN( 'task_time' ,'database_name','nt_username','sql_text','transaction_id')
AND T1.object_type='action'

SELECT * FROM sys.dm_xe_object_columns T0
INNER JOIN sys.dm_xe_objects T1 ON T0.object_package_guid=T1.package_guid AND T0.name=T1.name

上面查询的是元数据信息，下面将查看当前扩展事件会话信息：（就不截图了）

--  为每个服务器级事件通知对象返回一行
SELECT * FROM sys.server_event_notifications

--  扩展事件
SELECT * FROM sys.dm_xe_sessions WHERE name = 'es_slow_query'
SELECT * FROM sys.server_event_sessions WHERE name = 'es_slow_query'


--  跟踪的事件
SELECT * FROM sys.dm_xe_session_events
WHERE event_session_address IN (SELECT address FROM sys.dm_xe_sessions WHERE name = 'es_slow_query')

SELECT * FROM sys.server_event_session_events 
WHERE event_session_id in(SELECT event_session_id FROM sys.server_event_sessions WHERE name = 'es_slow_query')


--  跟踪事件字段
SELECT * FROM sys.dm_xe_session_event_actions
WHERE event_session_address IN (SELECT address FROM sys.dm_xe_sessions WHERE name = 'es_slow_query')

SELECT * FROM sys.server_event_session_actions t1
WHERE EXISTS(SELECT * FROM sys.server_event_sessions T2 WHERE T1.event_session_id=T2.event_session_id AND name = 'es_slow_query')

--  绑定到会话的对象的配置值
SELECT * FROM sys.dm_xe_session_object_columns t1
WHERE EXISTS(SELECT * FROM sys.dm_xe_objects t2 where t1.object_package_guid=t2.package_guid and t1.object_name=t2.name )
AND event_session_address IN (SELECT address FROM sys.dm_xe_sessions WHERE name = 'es_slow_query')


--  目标信息
SELECT * FROM sys.dm_xe_session_targets
WHERE event_session_address IN (SELECT address FROM sys.dm_xe_sessions WHERE name = 'es_slow_query')

SELECT * FROM sys.server_event_session_targets t1
WHERE EXISTS(SELECT * FROM sys.server_event_sessions T2 WHERE T1.event_session_id=T2.event_session_id AND name = 'es_slow_query')

SELECT * FROM sys.server_event_session_fields t1
WHERE EXISTS(SELECT * FROM sys.server_event_sessions T2 WHERE T1.event_session_id=T2.event_session_id AND name = 'es_slow_query')


--  读取目标文件信息
SELECT type, data FROM sys.fn_MSxe_read_event_stream (N'D:\Program Files\Microsoft SQL Server\slow_query*.xel', 1)

SELECT * FROM master.sys.fn_xe_file_target_read_file('D:\Program Files\Microsoft SQL Server\slow_query*.xel', NULL, NULL, NULL)



--  查询结果
SELECT object_name as event,file_name,convert(xml, event_data) as xml_data
FROM master.sys.fn_xe_file_target_read_file('D:\Program Files\Microsoft SQL Server\slow_query*', NULL, NULL, NULL)

<event name="sql_batch_completed" package="sqlserver" timestamp="2015-11-08T16:29:52.355Z">
  <data name="cpu_time">
    <value>0</value>
  </data>
  <data name="duration">
    <value>4023208</value>
  </data>
  <data name="physical_reads">
    <value>16</value>
  </data>
  <data name="logical_reads">
    <value>76</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>2</value>
  </data>
  <data name="result">
    <value>0</value>
    <text>OK</text>
  </data>
  <data name="batch_text">
    <value>begin tran 
update tab set name='kk' where id =1
waitfor delay '00:00:04'
update tab set name='kk' where id =2
commit tran
</value>
  </data>
  <action name="transaction_id" package="sqlserver">
    <value>0</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <value>begin tran 
update tab set name='kk' where id =1
waitfor delay '00:00:04'
update tab set name='kk' where id =2
commit tran
</value>
  </action>
  <action name="nt_username" package="sqlserver">
    <value>HZC\Administrator</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>Demo</value>
  </action>
  <action name="task_time" package="sqlos">
    <value>54284</value>
  </action>
</event>

总的系统视图如下：

--  扩展事件元数据信息
SELECT * FROM sys.dm_xe_packages
SELECT * FROM sys.dm_xe_objects
SELECT * FROM sys.dm_xe_map_values
SELECT * FROM sys.dm_xe_object_columns
SELECT * FROM sys.dm_xe_session_event_actions
SELECT * FROM sys.dm_xe_sessions
SELECT * FROM sys.dm_xe_session_events
SELECT * FROM sys.dm_xe_session_targets
SELECT * FROM sys.dm_xe_session_object_columns

--  当前扩展事件信息
SELECT * FROM sys.server_event_notifications
SELECT * FROM sys.server_event_sessions
SELECT * FROM sys.server_event_session_events
SELECT * FROM sys.server_event_session_actions
SELECT * FROM sys.server_event_session_targets
SELECT * FROM sys.server_event_session_fields
SELECT * FROM master.sys.fn_MSxe_read_event_stream (N'deadlock*.xel', 1)
SELECT * FROM master.sys.fn_xe_file_target_read_file(N'deadlock*.xel', NULL, NULL, NULL)

SELECT object_name as event,convert(xml, event_data) as xml_data
FROM master.sys.fn_xe_file_target_read_file(N'D:\deadlock*.xel', NULL, NULL, NULL)

官方案例参考：

• ​​如何确定哪些查询持有锁​​
• ​​如何查找具有最多锁定的对象​​
• ​​如何获取 ADD TARGET 参数的可配置参数​​
• ​​如何获取所有事件的字段​​
• ​​如何使用扩展事件监视系统活动​​
• ​​如何查看注册包的事件​​
• ​​如何查看已注册包的扩展事件目标​​

更多参考：

​​扩展事件​​

​​SQLSERVER2012里的扩展事件初尝试​​