捕捉后台作业中的死锁事件？

Question

我目前正在用分析器捕捉死锁事件。(死锁、死锁图、死锁链)。

到目前为止还不错，但要做到这一点，客户端计算机需要不断地运行和在线。

==>是否有一种方法可以直接在Server上捕获和收集死锁事件作为后台作业？

Answer 1

死锁事件由system_health扩展事件跟踪捕获，因此不需要以交互方式运行Profiler或创建SQL来捕获信息。

下面是从文件目标检索死锁信息的示例查询：

--Get xml_deadlock_report events from system_health session file target
WITH
      --get trace folder path and append session name with wildcard (assumes base file name is same as session name)
      all_trace_files AS (
        SELECT path + '\system_health*.xel' AS FileNamePattern
        FROM sys.dm_os_server_diagnostics_log_configurations
        )
      --get xml_deadlock_report events from all system_health trace files
    , deadlock_reports AS (
        SELECT CAST(event_data AS xml) AS deadlock_report_xml
        FROM all_trace_files
        CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS trace_records
        WHERE trace_records.object_name like 'xml_deadlock_report'
    )
SELECT TOP 10
      deadlock_report_xml.value('(/event/@timestamp)[1]', 'datetime2') AS UtcTimestamp
    , deadlock_report_xml AS DeadlockReportXml
FROM deadlock_reports;

Answer 2

使用system_health的另一个选项是为死锁使用专用的XE会话。它允许您决定要存储多少数据，等等。下面是这样一个XE会话的示例：

CREATE EVENT SESSION [Deadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'R:\Deadlocks',max_file_size=(1024),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [Deadlocks] ON SERVER 
 WITH (STARTUP_STATE=ON)

ALTER EVENT SESSION Deadlocks 
ON SERVER
STATE = START