擴展事件 XML 查詢以顯示有關死鎖的相關資訊 - SQL Server 2014

在嘗試收集有關死鎖的資訊時，我遇到了這個連結，它提出了一個關於跟踪的問題：

SQL Server 配置文件跟踪中的列的解釋

答案涉及從擴展事件中讀取數據，並使用 XML。碰巧擴展事件 XML 發生了更改，並且此 blob上的此腳本不再適用於 SQL Server 2008 之後的任何版本。

有沒有可能，我們可以讓這個腳本適用於 SQL Server 2014？

當我在 sql 2014 上執行腳本時，我收到以下錯誤消息：

來自Shawn Melton的有問題的腳本副本在這裡：

;WITH xDeadlock (Contents)
AS
(
select CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph
FROM
   (select CAST(target_data as xml) as TargetData
   from sys.dm_xe_session_targets st
   join sys.dm_xe_sessions s on s.address = st.event_session_address
   where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
), Victims AS
(
SELECT    ID = Victims.List.value('@id', 'varchar(50)')
FROM xDeadlock
CROSS APPLY xDeadlock.Contents.nodes('//deadlock/victim-list/victimProcess') AS Victims (List)
), Locks AS
(
SELECT  --xDeadlock.DeadlockID,
       MainLock.Process.value('@id', 'varchar(100)') AS LockID,
       OwnerList.Owner.value('@id', 'varchar(200)') AS LockProcessId,
       REPLACE(MainLock.Process.value('local-name(.)', 'varchar(100)'), 'lock', '') AS LockEvent,
       MainLock.Process.value('@objectname', 'sysname') AS ObjectName,
       OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode,
       MainLock.Process.value('@dbid', 'INTEGER') AS Database_id,
       MainLock.Process.value('@associatedObjectId', 'BIGINT') AS AssociatedObjectId,
       MainLock.Process.value('@WaitType', 'varchar(100)') AS WaitType,
       WaiterList.Owner.value('@id', 'varchar(200)') AS WaitProcessId,
       WaiterList.Owner.value('@mode', 'varchar(10)') AS WaitMode
FROM    xDeadlock
       CROSS APPLY xDeadlock.Contents.nodes('//deadlock/resource-list') AS Locks (list)
       CROSS APPLY Locks.List.nodes('*') AS MainLock (Process)
       CROSS APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList (Owner)
       CROSS APPLY MainLock.Process.nodes('waiter-list/waiter') AS WaiterList (Owner)
), Process AS 
(
-- get the data from the process node
SELECT  --xDeadlock.DeadlockID,
       [Victim] = CONVERT(BIT, CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = ISNULL(Deadlock.Process.value('../../@victim', 'varchar(50)'), v.ID) 
                                    THEN 1
                                    ELSE 0
                               END),
       [LockMode] = Deadlock.Process.value('@lockMode', 'varchar(10)'), -- how is this different from in the resource-list section?
       [ProcessID] = Process.ID, --Deadlock.Process.value('@id', 'varchar(50)'),
       [KPID] = Deadlock.Process.value('@kpid', 'int'), -- kernel-process id / thread ID number
       [SPID] = Deadlock.Process.value('@spid', 'int'), -- system process id (connection to sql)
       [SBID] = Deadlock.Process.value('@sbid', 'int'), -- system batch id / request_id (a query that a SPID is running)
       [ECID] = Deadlock.Process.value('@ecid', 'int'), -- execution context ID (a worker thread running part of a query)
       [IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)'),
       [WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)'),
       [LogUsed] = Deadlock.Process.value('@logused', 'int'),
       [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
       [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
       [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
       [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
       [BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
       [BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime'),
       [InputBuffer] = Input.Buffer.query('.'),
       xDeadlock.[Contents],
       [QueryStatement] = Execution.Frame.value('.', 'varchar(max)'),
       TranCount = Deadlock.Process.value('@trancount', 'int')
FROM    xDeadlock
       CROSS APPLY xDeadlock.Contents.nodes('//deadlock/process-list/process') AS Deadlock (Process)
       CROSS APPLY (SELECT Deadlock.Process.value('@id', 'varchar(50)') ) AS Process (ID)
       LEFT JOIN Victims v ON Process.ID = v.ID
       CROSS APPLY Deadlock.Process.nodes('inputbuf') AS Input (Buffer)
       CROSS APPLY Deadlock.Process.nodes('executionStack') AS Execution (Frame)
)
-- get the columns in the desired order
SELECT  p.Victim,
       p.LockMode,
       LockedObject = NULLIF(l.ObjectName, ''),
       l.database_id,
       l.AssociatedObjectId,
       LockProcess = p.ProcessID,
       p.KPID,
       p.SPID,
       p.SBID,
       p.ECID,
       p.TranCount,
       l.LockEvent,
       LockedMode = l.LockMode,
       l.WaitProcessID,
       l.WaitMode,
       p.WaitResource,
       l.WaitType,
       p.IsolationLevel,
       p.LogUsed,
       p.ClientApp,
       p.HostName,
       p.LoginName,
       p.TransactionTime,
       p.BatchStarted,
       p.BatchCompleted,
       p.InputBuffer
FROM    
   Locks l
   JOIN Process p ON p.ProcessID = l.LockProcessID
--WHERE p.TransactionTime > '2013-10-01'
ORDER BY p.Victim DESC,
       p.ProcessId;

這是一張來自上述腳本的部分結果集的圖片，在 sql server 2008 上執行。

在 SQL Server 2012 中，擴展事件的事件 XML 輸出發生了巨大變化；足以破壞用於 SQL Server 2008 的任何程式碼。Jonathan Kehayias 可以找到這方面的文章： SQL Server 2012 中的擴展事件更改 – XML 數據元素的事件 XML

您需要將 CTE 更改為xDeadlock以下內容：

;WITH xDeadlock (Contents)
AS
(
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph 
FROM ( 
   SELECT XEvent.query('.') AS XEvent 
   FROM ( 
       SELECT CAST(target_data AS XML) AS TargetData 
       FROM sys.dm_xe_session_targets st 
       JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address 
       WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer' ) AS Data 
   CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) 
   ) AS src
), Vicitims AS...

我將腳本從那篇部落格文章移到了我的GitHub 儲存庫，你可以從這裡下載它，還有安排在今天早上發布的部落格文章以及連結。

引用自：https://dba.stackexchange.com/questions/114475