SQL Server 使用扩展事件(Extended Event)监控死锁

使用扩展事件监视 SQL Server 中的死锁

Extended Event是一个系统监控工具,有助于从 SQL Server 收集事件和系统信息。借助 XEvent,我们还可以从 SQL Server 捕获死锁信息。首先,我们将启动 SQL Server Management Studio 并在Management文件夹下导航到Session右键单击会话文件夹并选择新建会话。

在 SQL Server 中创建扩展事件

新建会话屏幕中,我们将为会话命名并选中会话创建后立即启动事件会话复选框;因此,会话将在创建过程完成后启动。

监视 SQL Server 中的死锁

在“事件”选项卡上,我们选择要捕获的事件。对于本次会议,我们将选择以下事件:

  • database_xml_deadlock_report
  • lock_deadlock
  • lock_deadlock_chain
  • scheduler_monitor_deadlocks_ring_buffer_recorded
  • xml_deadlock_report
  • xml_deadlock_report_filtered

在 SQL Server 中配置扩展事件

我们将单击配置按钮并选择将与事件一起捕获的全局事件:

  • client app name
  • client connection id
  • client hostname
  • database id
  • database name
  • nt username
  • username
  • sql text
  • username

扩展事件的全局字段

Data Storage选项卡上,我们将选择 event_file 类型来存储捕获的数据,然后单击OK按钮。

扩展事件的数据存储设置

会话将被创建,然后自动启动以捕获死锁事件。

 

使用 system_health 会话监控 SQL Server 中的死锁

由于错误消息,数据库管理员决定研究死锁问题。错误消息显然表明存在死锁问题。作为第一步,他决定检查 system_health 会话是否存在死锁。

事务(进程 ID XX)在锁定资源上与另一个进程发生死锁,并已被选为死锁牺牲品。重新运行事务。

system_health是SQL Server的默认扩展事件会话,它会自动启动数据库引擎启动时。system_health 会话收集各种系统数据,其中之一是死锁信息。以下查询读取 system_health 会话的 .xel 文件并提供有关发生的死锁问题的信息。system_health 会话可以是找出死锁问题的一个很好的起点。下面的查询有助于找出 system_health 会话捕获的死锁问题。

DECLARE @xelfilepath NVARCHAR(260)
SELECT @xelfilepath = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SELECT @xelfilepath = @xelfilepath + N'system_health_*.xel'
DROP TABLE IF EXISTS  #TempTable
SELECT CONVERT(XML, event_data) AS EventData
        INTO #TempTable FROM sys.fn_xe_file_target_read_file(@xelfilepath, NULL, NULL, NULL)
         WHERE object_name = 'xml_deadlock_report'
SELECT EventData.value('(event/@timestamp)[1]', 'datetime2(7)') AS UtcTime,
            CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,
      EventData.value('(event/@timestamp)[1]', 'VARCHAR(50)')), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LocalTime,
            EventData.query('event/data/value/deadlock') AS XmlDeadlockReport
     FROM #TempTable
     ORDER BY UtcTime DESC;

 

借助 system_session 检测 SQL Server 中的死锁

当我们点击XmlDeadlockReport列的任意一行时,就会出现死锁报告。

死锁报告的 XML 格式

 

posted @ 2021-10-12 10:33  VicLW  阅读(806)  评论(0编辑  收藏  举报