sql server扩展事件（extended events）-- 使用system_health默认跟踪会话监控死锁 自sql server 2008以后，提供了扩展事件（extended events）来跟踪系统分析定位问题。默认的system_health会话一直在运行，可以帮助你更快的定位问题。 运行如下脚本可以看
sql server扩展事件（extended events）-- 使用system_health默认跟踪会话监控死锁 
自sql server 2008以后，提供了扩展事件（extended events）来跟踪系统分析定位问题。默认的system_health会话一直在运行，可以帮助你更快的定位问题。
运行如下脚本可以看到system_health扩展事件会话：
select * from sys.dm_xe_sessions
即便是你没有启动任何扩展事件会话，这个查询也会返回一行system_health会话。
sql server 2012版本之前，并不提供管理扩展事件会话的图形界面，你可以从这里下载sql server 2008 extended events ssms addin插件：http://extendedeventmanager.codeplex.com/
安装好后，可以按如图方式找到扩展事件管理界面：
650) this.width=650; title=clip_image001 style=max-width:90% alt=clip_image001 src=http://www.68idc.cn/help/uploads/allimg/151111/1215594023-0.jpg border=0 style=max-width:90% />
650) this.width=650; title=clip_image002 style=max-width:90% alt=clip_image002 src=http://www.68idc.cn/help/uploads/allimg/151111/121559e28-1.jpg border=0 style=max-width:90% />
而在sql server 2012版本中，则通过如图方式可以找到该界面：
650) this.width=650; title=clip_image003 style=max-width:90% alt=clip_image003 src=http://www.68idc.cn/help/uploads/allimg/151111/1215591x6-2.jpg border=0 style=max-width:90% />
我们右键点击“system_health”，生成脚本，我们可以看到该会话的内容如下（sql server 2012版本）：
create event session [system_health] on serveradd event sqlclr.clr_allocation_failure(action(package0.callstack,sqlserver.session_id)),add event sqlclr.clr_virtual_alloc_failure(action(package0.callstack,sqlserver.session_id)),add event sqlos.memory_broker_ring_buffer_recorded,add event sqlos.memory_node_oom_ring_buffer_recorded(action(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)),add event sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,add event sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,add event sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,add event sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,add event sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,add event sqlos.scheduler_monitor_system_health_ring_buffer_recorded,add event sqlos.wait_info(action(package0.callstack,sqlserver.session_id,sqlserver.sql_text)where ([duration]>(15000) and ([wait_type]>(31) and ([wait_type]>(47) and [wait_type](365) and [wait_type](372) and [wait_type](377) and [wait_type](420) and [wait_type](426) and [wait_type](432) and [wait_type](45000) and ([wait_type]>(382) and [wait_type](423) and [wait_type](434) and [wait_type](442) and [wait_type](451) and [wait_type](484) and [wait_type]=(20) or ([error_number]=(17803) or [error_number]=(701) or [error_number]=(802) or [error_number]=(8645) or [error_number]=(8651) or [error_number]=(8657) or [error_number]=(8902)))),add event sqlserver.security_error_ring_buffer_recorded(set collect_call_stack=(1)),add event sqlserver.sp_server_diagnostics_component_result(set collect_data=(1)where ([sqlserver].[is_system]=(1) and [component](4))),add event sqlserver.xml_deadlock_reportadd target package0.event_file(set filename=n'system_health.xel',max_file_size=(5),max_rollover_files=(4)),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=120 seconds,max_event_size=0 kb,memory_partition_mode=none,track_causality=off,startup_state=on)go
你也可以在sql server的安装目录：c:\program files\microsoft sql server\mssql11.\mssql\install
下找到脚本u_tables.sql文件。
从定义可以看到，会话的输出包含callstack、sessionid、tsql和tsql call stack
且当安全等级大于20或者错误号为17803等。它们与内存压力相关、non-yielding scheduler问题、死锁和一些类型的等待。
会话输出被捕获到遵从fifo规则的ring_buffer中，ring_buffer是一个内存使用者，它以二进制格式存储捕获数据。当事件会话启用的时候，数据即可被捕获。当停止会话的时候，分配给ring_buffer的内存被释放，且数据消失。注意：对于sql server 2012之前，system_health的目标只有ring_buffer，从sql server 2012开始，增加了event_file的输出。
你可以通过关联sys.dm_xe_session_targets和sys.dm_xe_sessions视图来查看ring_buffer或event_file的内容，并转换二进制数据为xml格式。
select name, target_name, cast(target_data as xml) target_datafrom sys.dm_xe_sessions sinner join sys.dm_xe_session_targets ton s.address = t.event_session_addresswhere s.name = 'system_health'go
注意：event_file的输出是文件的存储路径，而ring_buffer的输出是捕获到的数据。
在ring_buffer中，每一个事件元素都有一个数据子集和一个动作子集。这些动作是在会话的定义中。数据元素包含了每个事件的数据类型列的所有值。这些列可通过sys.dm_xe_object_columns视图输出。让我们解析xml格式以表格格式查看内容。因为每个事件返回数据列的不同集合。下面给一个error_reported事件的例子。
declare @x xml =(select cast(target_data as xml)from sys.dm_xe_sessions sinner join sys.dm_xe_session_targets ton s.address = t.event_session_addresswhere s.name = 'system_health' and t.target_name = 'ring_buffer')select t.e.value('@name', 'varchar(50)') as eventname,t.e.value('@timestamp', 'datetime') as dateandtime,t.e.value('(data[@name=error]/value)[1]', 'int') as errno,t.e.value('(data[@name=severity]/value)[1]', 'int') as severity,t.e.value('(data[@name=message]/value)[1]', 'varchar(max)') as errmsg,t.e.value('(action[@name=sql_text]/value)[1]', 'varchar(max)') as sql_textfrom @x.nodes('//ringbuffertarget/event') as t(e)where t.e.value('@name', 'varchar(50)') = 'error_reported'
650) this.width=650; title=clip_image004 style=max-width:90% alt=clip_image004 src=http://www.68idc.cn/help/uploads/allimg/151111/1215593200-3.jpg border=0 style=max-width:90% />
对于system_health最有帮助的用途之一是跟踪死锁。对于目标ringbuffer，存储多少数据依赖于被监控机器上的该目标的容量，以及产生最大数量的设置相关，这些将在每个会话的定义中。你可以在system_health会话的输出中找到过去的死锁记录。
所有查询都会在system_health输出中，可以通过运行下面的代码获得一个死锁报表。
-- sql server 2008 r2with systemhealthas (select cast(target_data as xml) as targetdatafrom sys.dm_xe_session_targets stjoin sys.dm_xe_sessions son s.address = st.event_session_addresswhere name = 'system_health'and st.target_name = 'ring_buffer')select xeventdata.xevent.value('@timestamp','datetime')as creation_date,cast(xeventdata.xevent.value('(data/value)[1]','varchar(max)') as xml) as deadlockgraphfrom systemhealthcross apply targetdata.nodes('//ringbuffertarget/event') as xeventdata (xevent)where xeventdata.xevent.value('@name','varchar(4000)') = 'xml_deadlock_report'order by creation_date desc
650) this.width=650; title=clip_image005 style=max-width:90% alt=clip_image005 src=http://www.68idc.cn/help/uploads/allimg/151111/1215593619-4.jpg border=0 style=max-width:90% />
exec spso_urgentorder_allocation 'c','system','' proc [database id = 5 object id = 1841441634]
-- sql server 2012with systemhealthas (select cast(target_data as xml) as targetdatafrom sys.dm_xe_session_targets stjoin sys.dm_xe_sessions son s.address = st.event_session_addresswhere name = 'system_health'and st.target_name = 'ring_buffer')select xeventdata.xevent.value('@timestamp','datetime')as creation_date, xeventdata.xevent.query('(data/value/deadlock)[1]') as deadlockgraphfrom systemhealthcross apply targetdata.nodes('//ringbuffertarget/event') as xeventdata (xevent)where xeventdata.xevent.value('@name','varchar(4000)') = 'xml_deadlock_report'order by creation_date desc
650) this.width=650; title=clip_image006 style=max-width:90% alt=clip_image006 src=http://www.68idc.cn/help/uploads/allimg/151111/1215592s7-5.jpg border=0 style=max-width:90% />
select * from [person].[address] where [addressid]=@1 select * from person.address where addressid = 20 --window 2use adventureworks2012begin tranupdate person.address set addressline1 = 'new address' where addressid = 25waitfor delay '0:0:30'select * from person.address where addressid = 20 select * from [person].[address] where [addressid]=@1 select * from person.address where addressid = 25 --window1use adventureworks2012begin tranupdate person.address set addressline1 = 'new address' where addressid = 20waitfor delay '0:0:30'select * from person.address where addressid = 25
查看process-list的inputbuf子元素，可以看到导致死锁的代码片段，process-list显示所有死锁参与者的进程id。process元素包含spid、数据库id、登录名、隔离级别、客户端应用程序名。resource-list元素包含在死锁中的资源。查看owner-list和waiter-list元素可以看到这两个进程如何互相阻塞。
尝试将该xml的输出保存为xdl文档，用ssms打开异常。目前有两个选择可以以图形方式打开死锁图表：sql sentry plan explorer pro 和 sql server 2012 management studio，详见：https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/