SQLServer 擴充事件(Extended Events)

SQL Server 擴充事件具有高度可伸縮且高度可配置的體系結構，使使用者能夠按需收集解決性能問題或确定性能問題所需的資訊。擴充事件是使用非常少的性能資源的輕型性能監視系統。擴充事件可以同步生成事件資料（并異步處理該資料），這為事件處理提供了靈活的解決方案。sqlserver 2008 開始了擴充事件功能，到 sqlserver 2012 後，擴充事件進行了可視化的界面操作。

官方導航相關資訊：

主題

說明

SQL Server 擴充事件簡介

介紹擴充事件并說明如何能使用該功能。

SQL Server 擴充事件概念

介紹了與擴充事件有關的主要概念。

SQL Server 擴充事件包	介紹了擴充事件包，擴充事件會話運作期間這些包中的對象将用于擷取和處理資料。
SQL Server 擴充事件目标	介紹了在事件會話期間可接收資料的事件使用者。
SQL Server 擴充事件引擎	介紹了可實作和管理擴充事件會話的引擎。
SQL Server 擴充事件會話	介紹了擴充事件會話。

使用 SQL Server 擴充事件

總結了用于擴充事件的 DDL 語句和視圖。

SQL Server 擴充事件操作指南主題

包含如何擷取事件相關資訊的操作指南主題，這些資訊可用于建立和更改擴充事件會話。同時提供了端到端的擴充事件會話以說明如何使用此功能監控系統活動。

擴充事件可以說是sql server profiler 的改造，将其放到系統管理中，可以用sql 腳本實作多樣化監控和處理。

對于擴充事件與 profiler 的操作比較，參考一位大俠部落格：SQLSERVER2012裡的擴充事件初嘗試

擴充事件 “包” 有幾個相關概念：事件，目标，操作，類型，謂詞，映射（更多參考： SQL Server 擴充事件包）

SQLServer 擴充事件(Extended Events)

（概念參考官方說明，開始有些難以了解）

事件：

事件是程式（例如 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)

SQLServer 擴充事件(Extended Events)

--	對事件包顯示的每個對象都傳回一行( 可以是 事件/操作/目标/謂詞/類型)
--	這裡隻檢視 “操作”資訊
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] 為包中的謂詞，隻是包名稱沒寫出來
*/

SQLServer 擴充事件(Extended Events)

--	【每個包對應的每個内容！】
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

SQLServer 擴充事件(Extended Events)

上面查詢的是中繼資料資訊，下面将檢視目前擴充事件會話資訊：（就不截圖了）

--	為每個伺服器級事件通知對象傳回一行
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)

SQLServer 擴充事件(Extended Events)

<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裡的擴充事件初嘗試

本文位址：http://blog.csdn.net/kk185800961/article/details/49725903

SQLServer 擴充事件(Extended Events)

繼續閱讀

SqlServer同步資料庫-分發-訂閱

幾種SqlServer資料庫分頁方式

SQLServer資料庫比對工具用什麼好。

C#連接配接sqlserver資料庫

我的職業生涯（四）

.NET開發人員的瓶頸和職業發展

資料庫拼接列字段拼接列字段

windows8.1上安裝sqlserver2005後找不到啟動程式

hibernate.cfg.xml檔案連接配接mySql、Oracle、SqlServer配置hibernate.cfg.xml檔案連接配接mySql、Oracle、SqlServer配置

asp連接配接資料庫的代碼方法

MS SQL Server 資料庫（基礎篇）

SQL Server不存在或通路被拒絕"問題的解決"SQL Server不存在或通路被拒絕"問題的解決

asp 連接配接資料庫的方法

ms sqlserver常用sql語句

金蝶雲星空即時庫存查詢SQL語句SQLServer

解決"T-SQL無法附加到程序"錯誤的補充,T-SQLcan not attach to process