欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

SQLServer扩展事件(ExtendedEvents)--使用system_health默认跟

程序员文章站 2024-01-08 08:56:11
...

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/

安装好后,可以按如图方式找到扩展事件管理界面:

SQLServer扩展事件(ExtendedEvents)--使用system_health默认跟

SQLServer扩展事件(ExtendedEvents)--使用system_health默认跟

而在SQL Server 2012版本中,则通过如图方式可以找到该界面:

SQLServer扩展事件(ExtendedEvents)--使用system_health默认跟

我们右键点击“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.\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_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'

SQLServer扩展事件(ExtendedEvents)--使用system_health默认跟

对于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

SQLServer扩展事件(ExtendedEvents)--使用system_health默认跟







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

SQLServer扩展事件(ExtendedEvents)--使用system_health默认跟


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/