SQLServer扩展事件(ExtendedEvents)--使用system_health默认跟
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/
安装好后,可以按如图方式找到扩展事件管理界面:
而在SQL Server 2012版本中,则通过如图方式可以找到该界面:
我们右键点击“system_health”,生成脚本,我们可以看到该会话的内容如下(SQL Server 2012版本):
CREATE EVENT SESSION [system_health] ON SERVER ADD 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](63) AND [wait_type](96) AND [wait_type](174) AND [wait_type](30000) AND [wait_type](5000) 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_report ADD 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.
下找到脚本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_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 = '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 s INNER JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address WHERE 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_text FROM @x.nodes('//RingBufferTarget/event') AS t(e) WHERE t.e.value('@name', 'varchar(50)') = 'error_reported'
对于system_health最有帮助的用途之一是跟踪死锁。对于目标ringbuffer,存储多少数据依赖于被监控机器上的该目标的容量,以及产生最大数量的设置相关,这些将在每个会话的定义中。你可以在system_health会话的输出中找到过去的死锁记录。
所有查询都会在system_health输出中,可以通过运行下面的代码获得一个死锁报表。
-- SQL Server 2008 R2 WITH SystemHealth AS ( SELECT CAST(target_data as xml) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE 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 DeadLockGraph FROM SystemHealth CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent) WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report' ORDER BY Creation_Date DESC
Exec SPSO_UrgentOrder_Allocation 'C','SYSTEM','' Proc [Database Id = 5 Object Id = 1841441634]
-- SQL Server 2012 WITH SystemHealth AS ( SELECT CAST(target_data as xml) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE 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 DeadLockGraph FROM SystemHealth CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent) WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report' ORDER BY Creation_Date DESC
SELECT * FROM [Person].[Address] WHERE [AddressID]=@1 SELECT * FROM Person.Address WHERE AddressID = 20 --Window 2 USE AdventureWorks2012 BEGIN TRAN UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 25 WAITFOR 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 --Window1 USE AdventureWorks2012 BEGIN TRAN UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 20 WAITFOR 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/
上一篇: PHP语言发展历史
下一篇: oracle如何设置序列当前值