2

While trying to get together information about deadlocks I came across this link which asks a question regarding a trace:

Interpretation of a column in SQL Server profile trace

The answer dealt with reading data from the Extended Events, and uses XML. It happens that the Extended Events XML changed and this script on this blob no longer works for any version after SQL Server 2008.

Is there a possibility, that we could have this script working for SQL Server 2014?

When I run the script on sql 2014, I get the error message below:

enter image description here

A copy of the script in question, from Shawn Melton is here:

;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;

This is a picture with a partial result set from the above scrip, run on sql server 2008.

enter image description here

  • So we don't have to construct a full repro, can you give a clue about what "no longer works" means? – Aaron Bertrand Sep 8 '15 at 14:56
  • The query runs without error for me, which leads me to believe that the XML is balking on something specific to your environment, e.g. a table named with an invalid character. – Aaron Bertrand Sep 8 '15 at 15:08
  • @AaronBertrand on sql 2014? I will have to double check but running it on a different server, which I will do in 30 min. I will let you know then. – Marcello Miorelli Sep 8 '15 at 15:11
  • Yes, on SQL Server 2014. But it returns an empty result set, so I'm not certain where the issue may be. It definitely sounds data-related, not version-related. – Aaron Bertrand Sep 8 '15 at 15:12
3

In SQL Server 2012 the event XML output of Extended Events changed dramatically; enough where it broke any code used for SQL Server 2008. A write of this can be found by Jonathan Kehayias: Extended Events Changes in SQL Server 2012 – Event XML for XML data elements

You will need to change the CTE for xDeadlock to the following:

;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...

enter image description here

I moved the script from that blog post to my GitHub repository and you can download it from here, have blog post scheduled for this morning with the link as well.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.