SQL Server扩展事件（Extended Events）-- 使用扩展事件不同目标示例

远行的心

SQL Server扩展事件（Extended Events）-- 使用扩展事件不同目标示例
　　下面的查询返回服务器的所有目标：
SELECT p.name AS PackageName,　　
o.name AS TargetName,
　　
o.description AS TargetDescription
　　
FROM sys.dm_xe_objects o
　　
INNER JOIN sys.dm_xe_packages p
　　
ON o.package_guid = p.guid
　　
WHERE o.object_type = 'target'
　　
AND (p.capabilities IS NULL OR p.capabilities  1)
　　
ORDER BY PackageName, TargetName;
　　每个目标都有一个可配置的参数列表，有些列是强制性的，有些是可选的。下面的查询返回了服务器的所有可用目标的参数。
SELECT p.name AS PackageName,　　
o.name AS TargetName,
　　
c.name AS ParameterName,
　　
c.type_name AS ParameterType,
　　
case c.capabilities_desc
　　
when 'mandatory' then 'yes'
　　
else 'no'
　　
end AS [Required]
　　
FROM sys.dm_xe_objects o
　　
INNER JOIN sys.dm_xe_packages p
　　
ON o.package_guid = p.guid
　　
INNER JOIN sys.dm_xe_object_columns c
　　
ON o.name = c.object_name
　　
WHERE o.object_type = 'target'
　　
AND (p.capabilities IS NULL OR p.capabilities  1)
　　
ORDER BY PackageName, TargetName, [Required] desc;
　　行动可以绑定在特定的事件或扩展事件。它们同步包含在触发事件的线程上，因此可能在有些情况下会对性能有影响。行动可以被用于：
　　l 聚合事件数据
　　l 追加额外数据到事件数据
　　l 捕获堆栈转储和观察数据
　　l 用变量存储状态信息在当前上下文
　　l 停止服务执行创建调试检查点
　　由于行动是同步执行的，它们应该在需要的时候才被用来捕获额外的信息。有些行动像package0.debug_break不应该被用在生产环境。下面的查询返回服务器上所有可用的行动。
SELECT p.name AS PackageName,　　
o.name AS ActionName,
　　
o.description AS ActionDescription
　　
FROM sys.dm_xe_objects o
　　
INNER JOIN sys.dm_xe_packages p
　　
ON o.package_guid = p.guid
　　
WHERE o.object_type = 'action'
　　
AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
　　
ORDER BY PackageName, ActionName;
　　事件数据通过字节的集合串在一起。为了说明数据，类型提供了长度和字节分类集合用于扩展事件引擎。大多数列有容易识别的类型，如int32、unicode_string、float32、boolean等，在大多数编程语言中是相当常见的。但也有些SQL Server中特别的类型，如callstack、sos_context、database_context等。
　　下面的查询返回所有可用的类型。
SELECT p.name AS PackageName,　　
o.name AS TypeName,
　　
o.description AS TypeDescription
　　
FROM sys.dm_xe_objects o
　　
INNER JOIN sys.dm_xe_packages p
　　
ON o.package_guid = p.guid
　　
WHERE o.object_type = 'type'
　　
AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
　　
ORDER BY PackageName, TypeName;
　　在对象负载中的每个列都有一个相应的数据类型。当定义谓词的时候，这是很明显的。类型并不用于会话定义的一部分。
　　谓词用于在事件触发的时候动态过事件，决定哪些事件符合规则而被触发。这使得事件会话更有针对性的设置针对特定的问题，而不是收集所有的事件跟踪后期再过滤。对于特定的目标像ringbuffer，这非常重要，因为当内存满的时候，事件会开始被截断。
　　谓词是布尔类型的表达式，用于过滤全局状态数据和本地事件数据。本地会话数据被一个事件的负载或列定义在它的列架构中。全局状态数据被可用的对象或谓词源列定义，可从sys.dm_xe_objects视图的object_type等于pred_source查到。
SELECT p.name AS PackageName,　　
o.name AS ColumnName,
　　
o.description AS ColumnDescription
　　
FROM sys.dm_xe_objects o
　　
INNER JOIN sys.dm_xe_packages p
　　
ON o.package_guid = p.guid
　　
WHERE o.object_type = 'pred_source'
　　
AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
　　
ORDER BY PackageName, ColumnName;
　　另外，有些谓词可以存储允许事件每N次计数时触发，或者第一次发生N次计数时触发。这可以用于基于样例事件捕获。可以用于帮助从频繁发生的事件中剔除触发很少的事件。
　　谓词通过数学计算的标准操作来实现。有些操作集合叫做比较操作符可以用于过滤。下面的查询可以找到比较操作符集合：
SELECT p.name AS PackageName,　　
o.name AS ComparatorName,
　　
o.description AS ComparatorDescription
　　
FROM sys.dm_xe_objects o
　　
INNER JOIN sys.dm_xe_packages p
　　
ON o.package_guid = p.guid
　　
WHERE o.object_type = 'pred_compare'
　　
AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
　　
ORDER BY PackageName, ComparatorName;
　　比较操作符的语法为：
　　Package_name.predicate_comparator_name(,)
　　比较操作符和源列必需有相同的数据类型，源列的数据类型不能被转换。下面的脚本展示了如何使用比较操作符：
　　Ring Buffer示例
　　内存中的环形缓冲，临时存储事件数据到内存
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='BufferManagerWatcher')　　
DROP EVENT SESSION [BufferManagerWatcher] ON SERVER;
　　
CREATE EVENT SESSION [BufferManagerWatcher]
　　
ON SERVER
　　
ADD EVENT sqlserver.buffer_manager_page_life_expectancy(
　　
WHERE (([package0].[less_than_equal_uint64]([count],(3000)))))
　　
ADD TARGET package0.ring_buffer(
　　
SET max_memory=4096)
　　上面的事件在Page Life Expectancy降到3000微秒以下时被触发。
　　匹配提供了一个查找数据的内部值，让最终用户能知道值的意思。下面的查询提供了可用的匹配和值定义：
SELECT name, map_key, map_value　　
FROM sys.dm_xe_map_values
　　
ORDER BY name, map_key
　　匹配像类型一样是对扩展事件上下文的表述。它们并不直接用于扩展事件会话的定义。它们提供分配给谓词去过滤事件或计算目标数据的值的一个查找渠道。
　　会话是事件和相关的动作、谓词、会话目标的集合。一个会话可以有用一个或多个事件和目标。

　　会话拥有独立的事件和目标配置。上例中，ring_buffer目标用于两个会话但是有用不同的内存配置。定义在一个会话中的事件独立于其他实例的不同会话的的同名事件。
　　Event File示例
　　errorlog_written事件：当SQL Server错误日志写入的时候触发。
　　步骤1：
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FileTargetDemo')　　
DROP EVENT SESSION [FileTargetDemo] ON SERVER;
　　
CREATE EVENT SESSION [FileTargetDemo]
　　
ON SERVER
　　
ADD EVENT sqlserver.errorlog_written
　　
ADD TARGET package0.asynchronous_file_target(
　　
SET filename='c:\FileTargetDemo.etl', metadatafile='c:\FileTargetDemo.mta')
　　
ALTER EVENT SESSION [FileTargetDemo]
　　
ON SERVER
　　
STATE=START
　　步骤2：
DBCC CHECKDB(‘AdventureWorks2012’)　　步骤3：
SELECT name, target_name, CAST(target_data AS XML) target_data,　　
(SELECT c.column_name + '=' + c.column_value + ', '
　　
FROM sys.dm_xe_session_object_columns c
　　
WHERE s.address = c.event_session_address
　　
AND t.target_name = c.object_name
　　
AND c.object_type = 'target'
　　
ORDER BY column_id
　　
for xml path('')) AS options
　　
FROM sys.dm_xe_sessions s
　　
INNER JOIN sys.dm_xe_session_targets t
　　
ON s.address = t.event_session_address
　　
WHERE s.name = 'FileTargetDemo'
　　

　　
SELECT *
　　
FROM sys.fn_xe_file_target_read_file('c:\FileTargetDemo*etl', 'c:\FileTargetDemo*mta', null, null)
　　步骤4：
ALTER EVENT SESSION [FileTargetDemo]　　
ON SERVER
　　
STATE=STOP
　　Synchronous Event Counter示例
　　记录会话中触发的事件计数
　　步骤1：
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='EventCounterTargetDemo')　　
DROP EVENT SESSION [EventCounterTargetDemo] ON SERVER;
　　
CREATE EVENT SESSION [EventCounterTargetDemo]
　　
ON SERVER
　　
ADD EVENT sqlserver.sql_statement_starting,
　　
ADD EVENT sqlserver.sql_statement_completed
　　
ADD TARGET package0.synchronous_event_counter
　　
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
　　
ALTER EVENT SESSION [EventCounterTargetDemo]
　　
ON SERVER
　　
STATE=START
　　步骤2：
SELECT name, target_name, CAST(target_data AS XML) target_data　　
FROM sys.dm_xe_sessions s
　　
INNER JOIN sys.dm_xe_session_targets t
　　
ON s.address = t.event_session_address
　　
WHERE s.name = 'EventCounterTargetDemo'
　　步骤3：
ALTER EVENT SESSION [EventCounterTargetDemo]　　
ON SERVER
　　
STATE=STOP
　　Event Pairing示例

　　找出有触发启动的事件但是没有触发相应的完成的事件的事件。对于跟踪Lock Acquired和Lock>　　步骤1：
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='PairMatchingTargetDemo')　　
DROP EVENT SESSION [PairMatchingTargetDemo] ON SERVER;
　　
CREATE EVENT SESSION [PairMatchingTargetDemo]
　　
ON SERVER
　　
ADD EVENT sqlserver.database_transaction_begin(
　　
ACTION (sqlserver.session_id)),
　　
ADD EVENT sqlserver.database_transaction_end(
　　
ACTION (sqlserver.session_id))
　　
ADD TARGET package0.pair_matching(
　　
SET begin_event='sqlserver.database_transaction_begin', begin_matching_actions='sqlserver.session_id', end_event='sqlserver.database_transaction_end', end_matching_actions='sqlserver.session_id')
　　
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
　　
ALTER EVENT SESSION [PairMatchingTargetDemo]
　　
ON SERVER
　　
STATE=START;
　　步骤2：
SELECT name, target_name, CAST(target_data AS XML) target_data　　
FROM sys.dm_xe_sessions s
　　
INNER JOIN sys.dm_xe_session_targets t
　　
ON s.address = t.event_session_address
　　
WHERE s.name = 'PairMatchingTargetDemo'
　　步骤3：
ALTER EVENT SESSION [PairMatchingTargetDemo]　　
ON SERVER
　　
STATE=STOP;
　　Event Bucketing示例
　　用于根据事件负载列或行动分组来分组事件的发生。下面示例以wait_type来分组wait_info事件。
　　步骤1：
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='BucketingTargetDemo')　　
DROP EVENT SESSION [BucketingTargetDemo] ON SERVER;
　　
CREATE EVENT SESSION [BucketingTargetDemo]
　　
ON SERVER
　　
ADD EVENT sqlos.wait_info
　　
ADD TARGET package0.asynchronous_bucketizer(
　　
SET filtering_event_name='sqlos.wait_info', source_type=0, source='wait_type')
　　
WITH (MAX_DISPATCH_LATENCY = 2 SECONDS)
　　
ALTER EVENT SESSION [BucketingTargetDemo]
　　
ON SERVER
　　
STATE=START
　　步骤2：
SELECT name, target_name, CAST(target_data AS XML) target_data　　
FROM sys.dm_xe_sessions s
　　
INNER JOIN sys.dm_xe_session_targets t
　　
ON s.address = t.event_session_address
　　
WHERE s.name = ' BucketingTargetDemo '
　　步骤3：
ALTER EVENT SESSION [BucketingTargetDemo]　　
ON SERVER
　　
STATE=STOP;
　　Event Tracing for Windows(ETW)示例
　　ETW目标文件是一个外部文件，不会被SQL Server内部使用。外部的ETW通常被用来跟踪与内部发生事件相关的外部发生事件。
　　步骤1：
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='ETWTargetDemo')　　
DROP EVENT SESSION [ETWTargetDemo] ON SERVER;
　　
CREATE EVENT SESSION [ETWTargetDemo]
　　
ON SERVER
　　
ADD EVENT sqlserver.sql_statement_starting (
　　
WHERE sqlserver.database_id = 2),
　　
ADD EVENT sqlserver.sql_statement_completed (
　　
WHERE sqlserver.database_id = 2),
　　
ADD EVENT sqlserver.file_written (
　　
WHERE sqlserver.database_id = 2),
　　
ADD EVENT sqlserver.file_write_completed (
　　
WHERE sqlserver.database_id = 2)
　　
ADD TARGET package0.etw_classic_sync_target (
　　
SET default_etw_session_logfile_path = N'C:\sqletwtarget.etl')
　　同时，在Windows命令行启动一个ETW会话对于磁盘和文件操作收集外部ETW数据
　　logman start "NT Kernel Logger" -p "Windows Kernel Trace" (disk,file) -ets -o C:\systemetw.etl -bs 1024 -ct system
　　步骤2：
ALTER EVENT SESSION [ETWTargetDemo]　　
ON SERVER
　　
STATE=START
　　步骤3：
　　导入批量数据到tempdb。
　　步骤4：
ALTER EVENT SESSION [ETWTargetDemo]　　
ON SERVER
　　
STATE=STOP
　　步骤5：
　　从命令行停止系统ETW收集和扩展事件外部ETW会话。
　　logman update "NT Kernel Logger" -fd -ets
　　logman stop "NT Kernel Logger" -ets
　　logman update XE_DEFAULT_ETW_SESSION -fd -ets
　　logman stop XE_DEFAULT_ETW_SESSION –ets
　　步骤6：
　　用tracerpt命令连接两个.etl文件生成一个.csv文件。
　　tracerpt c:\sqletwtarget.etl c:\systemetw.etl -y -o sqletw_results.csv
　　备注：
　　C:\>logman /?
　　C:\>tracerpt /?