如何在sql server ro14中找到死锁原因? [英] How to find the deadlock reason in sql server ro14?

问题描述

在 SQL Server 数据库中遇到死锁，可以在 SQL 日志中看到死锁条目.如何使用日志条目来查找此死锁的原因?

Facing a deadlock in SQL server database and can see deadlock entry in SQL logs. How the log entries can be used to find the reason for this deadlock?

推荐答案

死锁信息默认由 system_health 扩展事件跟踪捕获.无需打开额外的跟踪标志.

Deadlock information is captured by the system_health Extended Events trace by default. No need to turn on additional trace flags.

来自 xml_deadlock 事件的信息可以从 SSMS 对象资源管理器(管理-->扩展事件-->会话--system_health)或使用 T-SQL 查看.下面是从文件目标获取死锁 xml 的示例查询.您还可以将死锁 xml 保存到具有 xdl 扩展名的文件中，然后在 SSMS 中打开该文件以查看死锁的图形视图.

Information from the xml_deadlock event can be viewed from SSMS Object Explorer (Management-->Extended Events-->Sessions--system_health) or with T-SQL. Below is an example query to get the deadlock xml from the file target. You can also save the deadlock xml to a file with an xdl extension and open the file in SSMS for a graphical view of the deadlock.

--get xml_deadlock_report from system_health session file target
WITH
      --get full path to current system_health trace file
      CurrentSystemHealthTraceFile AS (
        SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
        FROM sys.dm_xe_session_targets
        WHERE
            target_name = 'event_file'
            AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
    )
      --get trace folder name and add base name of system_health trace file with wildcard
    , BaseSystemHealthFileName AS (
        SELECT 
            REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
        FROM CurrentSystemHealthTraceFile
        )
      --get xml_deadlock_report events from all system_health trace files
    , DeadLockReports AS (
        SELECT CAST(event_data AS xml) AS event_data
        FROM BaseSystemHealthFileName
        CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS xed
        WHERE xed.object_name like 'xml_deadlock_report'
    )
--display 10 most recent deadlocks
SELECT TOP 10
      DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS LocalTime
    , event_data AS DeadlockReport
FROM DeadLockReports
ORDER BY LocalTime ASC;

这篇关于如何在sql server ro14中找到死锁原因?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！