【监控笔记】【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会话总结
相关参考:http://www.mssqlmct.cn/t-sql/?post=95&tdsourcetag=s_pctim_aiomsg
默认扩展事件查询死锁语句
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_server\xe\DealLock_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)实例-》管理-》扩展事件-》右击会话-》新建会话向导
(2)输入扩展事件会话名称 -》下一步
(3)不使用模板 -》下一步
(4)选择死锁事件 -》放到右边-》下一步
(5)捕获全局字段不填写-》下一步
(6)设置会话事件筛选器忽略 -》下一步
(7)保存到文件 -》下一步
(8)可以查看设置的摘要信息,以及生成脚本 -》完成
(9)查看创建脚本与启动,右击该会话-》启动会话。 脚本:右击该会话-》编写会话脚本为-》create
(10)模拟死锁后查看,双击会话名称打开如下图
【5.3】深入进阶(死锁案例)
【5.3.1】Lock:Deadlock
这个事件可以用来验证死锁牺牲品。这个时间说明什么时候请求需要一个锁,但被取消作为一个死锁牺牲品
【5.3.2】Lock:Deadlock chain
这个事件类用于监控死锁状态。但存在一个死锁时该事件被处罚。
通过在实例级别监控这个事件,我们能够识别哪些对象处于死锁中,是否在应用程序中存在因死锁导致的性能问题。
【5.3.3】操作演示
管理-》扩展事件-》会话-》找到我们上面创建的 Deallock_monitor-》右击属性-》加上【5.3.1】~【5.3.2】的事件
再次尝试死锁测试,结果信息