【监控笔记】【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】的事件

       

    再次尝试死锁测试,结果信息

      

 

【6】sql server使用T-SQL读取扩展事件

sql server使用T-SQL读取扩展事件

posted @ 2019-06-03 20:24  郭大侠1  阅读(1001)  评论(0编辑  收藏  举报