【监控笔记】【2.2】扩展事件—

【监控笔记】【2.2】扩展事件——死锁监控

如何监控死锁？

【1】windows性能监控器（Performance Monitor）

　　windows性能监控器（Performance Monitor）

　　Object:SQLServer:Locks

　　Counter:Number of Deadlocks/sec

　　Instance:_Total

--查看自上次启动服务，死锁发生的次数
select * from sys.dm_os_performance_counters
where counter_name like '%dead%'

【2】开启跟踪标识记录死锁信息到错误日志（跟踪标识集合）

相关生产实践引用参考：如何捕获和记录SQL死锁

DBCC TRACEON (3605,1204,1222,-1)  

3605 将DBCC的结果输出到错误日志。
1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。
1222 返回参与死锁的锁的资源和类型，以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 2005及以上可用)。
-1 以全局方式打开指定的跟踪标记。

以上跟踪标志作用域都是全局，即在SQL Server运行过程中，会一直发挥作用，直到SQL Server重启
如果要确保SQL Server在重启后自动开启这些标志，可以在SQL Server服务启动选项中，使用 /T 启动选项指定跟踪标志在启动期间设置为开。
(位于SQL Server配置管理器->SQL Server服务->SQL Server->属性->高级->启动参数)

【3】SQL Prifiler 和服务端的 SQL Trace

相关生产实践引用参考：如何捕获和记录SQL死锁

Trace Event Class : Locks Event Name : Deadlock Graph，会给出xml图示

【4】默认扩展事件（System Health）

从MSSQL 2008 开始出现的新功能。

相关生产实践引用参考：https://www.cnblogs.com/lyhabc/articles/4028073.html

相关参考：SQL Server扩展事件system_health会话总结

默认扩展事件查询死锁语句

DECLARE @SessionName SysName

SELECT @SessionName = 'system_health'

IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN
DROP TABLE #Events
END

 
DECLARE @Target_File NVarChar(1000)
, @Target_Dir NVarChar(1000)
, @Target_File_WildCard NVarChar(1000)

SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = @SessionName
AND t.target_name = 'event_file'


SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('', REVERSE(@Target_File)))

SELECT @Target_File_WildCard = @Target_Dir + '' + @SessionName + '_*.xel'

--Keep this as a separate table because it's called twice in the next query. You don't want this running twice.

SELECT DeadlockGraph = CAST(event_data AS XML)
, DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)
INTO #Events
FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F
WHERE event_data like '<event name="xml_deadlock_report%'

 

;WITH Victims AS

(

SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)')
, e.DeadlockID
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)

)

, DeadlockObjects AS

(
SELECT DISTINCT e.DeadlockID
, ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)')

FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources)
)

SELECT *
FROM
(
SELECT e.DeadlockID
, TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime')
, DeadlockGraph
, DeadlockObjects = substring((SELECT (', ' + o.ObjectName)
FROM DeadlockObjects o
WHERE o.DeadlockID = e.DeadlockID
ORDER BY o.ObjectName
FOR XML PATH ('')
), 3, 4000)
, Victim = CASE WHEN v.VictimID IS NOT NULL
THEN 1 ELSE 0 END
, SPID = Deadlock.Process.value('@spid', 'int')
, ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)')
, LockMode = Deadlock.Process.value('@lockMode', 'char(1)')
, Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)')
, ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29)
WHEN 'SQLAgent - TSQL JobStep (Job '
THEN 'SQLAgent Job: ' + 
(
SELECT name FROM msdb..sysjobs sj 
WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + 
' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67
)
ELSE Deadlock.Process.value('@clientapp', 'varchar(100)')
END
, HostName = Deadlock.Process.value('@hostname', 'varchar(20)')
, LoginName = Deadlock.Process.value('@loginname', 'varchar(20)')
, InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
FROM #Events e

CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process)

LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')

) X

ORDER BY DeadlockID DESC

【5】自定义跟踪死锁的扩展事件

【5.1】T-SQL实现

CREATE EVENT SESSION [DeadLock_test] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report 
ADD TARGET package0.event_file(
SET filename=N'C:sql_serverxeDealLock_test.xel',
max_file_size=(10),
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

ALTER EVENT SESSION [DeadLock_test] ON SERVER STATE=START
GO

--查看
select * from sys.dm_xe_sessions

-- 扩展事件元数据信息
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)

【5.2】基于SSMS的GUI实现

　　（1）实例-》管理-》扩展事件-》右击会话-》新建会话向导