gpt4 book ai didi

sql-server - 如何查找sql​​ server ro14中的死锁原因?

转载 作者:行者123 更新时间:2023-12-02 00:29:33 28 4
gpt4 key购买 nike

在 SQL 服务器数据库中遇到死锁,可以在 SQL 日志中看到死锁条目。如何使用日志条目查找此死锁的原因?

最佳答案

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

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

--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 - 如何查找sql​​ server ro14中的死锁原因?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52422764/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com