【监控笔记】【2.1】扩展事件

转自：http://blog.csdn.net/kk185800961/article/details/49725903

强烈建议参考：https://www.cnblogs.com/ljhdo/p/5657906.html

相关参考：

1. SQL Server 扩展事件（Extented Events）从入门到进阶（1）——从SQL Trace到Extented Events
2. SQL Server 扩展事件（Extented Events）从入门到进阶（2）——在GUI中创建基础扩展事件
3. SQL Server 扩展事件（Extented Events）从入门到进阶（3）——通过界面操作Extented Event
4. SQL Server 扩展事件（Extented Events）从入门到进阶（4）——扩展事件引擎——基本概念

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

 　　

 

官方导航相关信息：

主题

说明

 

 

【1】扩展事件的概念

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

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

 

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

 　　

 

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

【1.1】事件

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

【1.2】目标

　　目标是指事件使用者。目标在触发事件的线程中同步处理事件或在系统提供的线程中异步处理事件。

　　通常，在必须保持特定数据排序时将使用同步处理。扩展事件提供了多个目标，您可以根据需要将其用于定向事件输出。（更多穿单裤： SQL Server 扩展事件目标）

　　保存会话数据：

　　　　（1）ETW_classic_sync_target:以ETW（Event Tracing for windows）格式输出事件

　　　　（2）Event_counter：输出事件到内存记录绘画中以跟踪事件触发的次数

　　　　（3）Event_file：输出到磁盘上的一个目录文件中

　　　　（4）Histogram：输出事件到内存，基于域和行动分组计数事件

　　　　（5）Pair_matching：输出事件到内存，跟踪基于目标配置的一定量的事件数据

　　　　（6）Ring_buffer：输出事件到内存，跟踪基于目标配置的一定量的事件数据

【1.3】操作

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

【1.4】类型

　　由于数据是排列在一起的字节集合，因此需要使用字节集合的长度和特征来解释这些数据。该信息将封装在 Type 对象中。

下面是为包对象提供的类型：event，action，target，pred_source，pred_compare，type

【1.5】谓词

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

【1.6】映射

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

 

 

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

【2】 配置查看

--包信息详情
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_objects --查看所有类型，比如：事件/动作/目标 等等相关信息
-- 案例 select * from sys.dm_xe_objects where object_type='event' and object_name=''   查看具体事件

--查看已经存在的扩展事件信息
SELECT * FROM sys.dm_xe_sessions --扩展事件信息（事件）
SELECT * FROM sys.dm_xe_session_events    --扩展事件对应的跟踪参数信息（谓词）
SELECT * FROM sys.dm_xe_session_event_actions    --跟踪事件字段，所属事件，所属包（动作）
SELECT * FROM sys.dm_xe_session_object_columns  --绑定到会话的对象的配置值
SELECT * FROM sys.dm_xe_session_targets            --目标信息

 

【3】总的系统视图

--    扩展事件元数据信息
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)

 

 

【4】案例解析：查询超过 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

【4.1】扩展事件元数据

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

--    针对每个加载到服务器地址空间的模块返回一行
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

 

【4.2】扩展事件会话信息

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

-- 为每个服务器级事件通知对象返回一行
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>

 

 

 

【官方案例参考】

如何确定哪些查询持有锁

如何查找具有最多锁定的对象

如何获取 ADD TARGET 参数的可配置参数

如何获取所有事件的字段

如何使用扩展事件监视系统活动

如何查看注册包的事件

如何查看已注册包的扩展事件目标

 

更多参考：
扩展事件

SQLSERVER2012里的扩展事件初尝试

 

本文转自：http://blog.csdn.net/kk185800961/article/details/49725903

【sql server使用T-SQL读取扩展事件】

sql server使用T-SQL读取扩展事件