Deadlock Detection and Analysis

2019-05-30 · by Hannah Vernon · in tools, troubleshooting

Deadlock detection and analysis in SQL Server is important for the overall health of affected applications. This post shows how to setup an Extended Events session to capture deadlock details. It also provides a stored procedure to capture details from the Extended Events session. The stored procedure enables simplified permanent storage of those deadlock detection events. Next, we’ll set up a SQL Server Agent Job to run the stored procedure on an ongoing basis. Finally, we’ll see several examples of how to query the captured events. These queries support making the necessary changes to both the application and database design.

Deadlocks in SQL Server aren’t quite this bad. Allegory of death by Juan de Valdés Leal

Lets define what a SQL Server Deadlock is. We’ll also see why it’s important to mitigate their causes.

Background

So, what is a deadlock? A deadlock happens when two processes are competing for the same resources in such a way that neither session can ever complete. SQL Server’s sophisticated deadlock detection algorithm will quickly choose one of the deadlocked sessions for termination. This allows SQL Server to resolve the deadlock, allowing the other session to complete. The terminated session is rolled-back, and the following error is returned to the client:

Msg 1205, Level 13
Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The “cheapest to rollback” process involved in the deadlock is chosen as the deadlock victim. “Cheapest” is based on the number of log bytes written, but can be overridden with the SET DEADLOCK_PRIORITY statement.

Client software that is not well-written may not properly handle deadlock errors. For example, perhaps it doesn’t retry the deadlocked transaction, and either returns an error to the user, or just silently continues. This can leave the data in a state that is not consistent with business objectives. Clearly the far better option would be to eliminate the causes of deadlocks.

Required Configuration Change

Detecting when deadlocks occur is the first step towards mitigation. Once detected, we’d want to provide those detection details to Database Administrators for remediation. The first step is to enable the “blocked process threshold” system configuration option. This code performs that change:

IF (
    SELECT c.value_in_use
    FROM sys.configurations c
    WHERE c.name = N'blocked process threshold (s)'
    ) = 0
BEGIN
    EXEC sys.sp_configure @configname = N'blocked process threshold (s)', @configvalue = '5';
    RECONFIGURE
END

IF (

SELECT c.value_in_use

FROM sys.configurations c

WHERE c.name = N'blocked process threshold (s)'

) = 0

BEGIN

EXEC sys.sp_configure @configname = N'blocked process threshold (s)', @configvalue = '5';

RECONFIGURE

END

Creating the Extended Event session

Next, we create the Extended Events session which gathers XML data from SQL Server whenever a deadlock is detected:

/*
    Creates the "deadlocks" Extended Events session to capture deadlock events
    into a ring_buffer target.  Yes, I know, ring buffer.  See this site for some gotchas:
    https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/
*/
IF NOT EXISTS ( /* only create this session if one doesn't already exist
                   to prevent inadvertant loss of events.
                */
    SELECT 1
    FROM sys.server_event_sessions ss
    WHERE ss.name = N'deadlocks'
    )
BEGIN
    CREATE EVENT SESSION [deadlocks] ON SERVER 
    ADD EVENT sqlserver.xml_deadlock_report (
        ACTION (
              sqlserver.client_app_name
            , sqlserver.client_hostname
            , sqlserver.database_name
            )
        )
    ADD TARGET package0.ring_buffer(
        SET   max_memory        = 2048 /* Maximum amount of memory in KB to use. Old events are dropped
                                          when this value is reached. 0 means unbounded.
                                          2048 is recommended to avoid posible XML data truncation */
            , occurrence_number = 0    /* Preferred number of events of each type to keep. */
            , max_events_limit  = 0    /* Maximum number of events to store. Old events are 
                                          dropped when this value is reached. 0 means unbounded. */
        )
    WITH (
          MAX_MEMORY = 10 MB
        , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
        , MAX_DISPATCH_LATENCY = 5 SECONDS
        , MAX_EVENT_SIZE = 0 KB
        , MEMORY_PARTITION_MODE = NONE
        , TRACK_CAUSALITY = OFF
        , STARTUP_STATE = ON
        );
END
IF NOT EXISTS ( --XE sessions on show up in sys.dm_xe_sessions if they are running
    SELECT 1
    FROM sys.dm_xe_sessions xs
    WHERE xs.name = N'deadlocks'
    )
BEGIN
    ALTER EVENT SESSION [deadlocks] ON SERVER 
    STATE = START;
END
GO

Creates the "deadlocks" Extended Events session to capture deadlock events

into a ring_buffer target. Yes, I know, ring buffer. See this site for some gotchas:

https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

IF NOT EXISTS ( /* only create this session if one doesn't already exist

to prevent inadvertant loss of events.

SELECT 1

FROM sys.server_event_sessions ss

WHERE ss.name = N'deadlocks'

)

BEGIN

CREATE EVENT SESSION [deadlocks] ON SERVER

ADD EVENT sqlserver.xml_deadlock_report (

ACTION (

sqlserver.client_app_name

, sqlserver.client_hostname

, sqlserver.database_name

)

ADD TARGET package0.ring_buffer(

SET max_memory = 2048 /* Maximum amount of memory in KB to use. Old events are dropped

when this value is reached. 0 means unbounded.

2048 is recommended to avoid posible XML data truncation */

, occurrence_number = 0 /* Preferred number of events of each type to keep. */

, max_events_limit = 0 /* Maximum number of events to store. Old events are

dropped when this value is reached. 0 means unbounded. */

)

WITH (

MAX_MEMORY = 10 MB

, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY = 5 SECONDS

, MAX_EVENT_SIZE = 0 KB

, MEMORY_PARTITION_MODE = NONE

, TRACK_CAUSALITY = OFF

, STARTUP_STATE = ON

);

END

IF NOT EXISTS ( --XE sessions on show up in sys.dm_xe_sessions if they are running

SELECT 1

FROM sys.dm_xe_sessions xs

WHERE xs.name = N'deadlocks'

)

BEGIN

ALTER EVENT SESSION [deadlocks] ON SERVER

STATE = START;

END

Creating the dba_admin Database

This code will create a dba_admin database to store the captured deadlock events. You may want to modify the CREATE DATABASE statement to include path details for the data and log files. That would allow you to specify the maximum file sizes and file growth increments.

IF DB_ID(N'dba_admin') IS NULL
BEGIN
    CREATE DATABASE dba_admin;
    ALTER DATABASE dba_admin SET RECOVERY SIMPLE;
    BACKUP DATABASE dba_admin TO DISK = N'NUL:';
END
GO

IF DB_ID(N'dba_admin') IS NULL

BEGIN

CREATE DATABASE dba_admin;

ALTER DATABASE dba_admin SET RECOVERY SIMPLE;

BACKUP DATABASE dba_admin TO DISK = N'NUL:';

END

Creating the Stored Procedure

Next we’ll create the dbo.GatherDeadlockEvents stored procedure. This stored procedure captures deadlock detection events from the Extended Events session. It saves the events in the dba_admin database. It uses the Extended Events timestamp and the killed process ID to only capture deadlock events from the ring buffer that haven’t previously been captured. There is a very small potential to miss events if they occur extremely quickly, and have the same process ID.

USE dba_admin;
GO
IF OBJECT_ID(N'dbo.GatherDeadlockEvents', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GatherDeadlockEvents;
END
GO
/*
    Gathers deadlock events from the ring buffer target
    into the dbo.deadlock_xml_events table.
    Should be ran from a frequently occurring SQL Server
    Agent job.
*/
CREATE PROCEDURE dbo.GatherDeadlockEvents
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    IF OBJECT_ID(N'dbo.deadlock_xml_events', N'U') IS NULL
    CREATE TABLE dbo.deadlock_xml_events
    (
          xeTimeStamp datetimeoffset NOT NULL
        , xeProcessID varchar(20) NOT NULL
        , xeXML XML NOT NULL
        , CONSTRAINT deadlock_xml_events_pk
            PRIMARY KEY CLUSTERED (xeTimeStamp, xeProcessID)
    );

    IF OBJECT_ID(N'tempdb..#xmlResults', N'U') IS NULL
    CREATE TABLE #xmlResults
    (
          xeTimeStamp datetimeoffset NOT NULL
        , xeProcessID varchar(20) NOT NULL
        , xeXML XML NOT NULL
        , PRIMARY KEY CLUSTERED (xeTimeStamp, xeProcessID)
    );

    TRUNCATE TABLE #xmlResults;

    DECLARE @target_data xml;
    SELECT @target_data = CONVERT(xml, target_data)
    FROM sys.dm_xe_sessions AS s 
    JOIN sys.dm_xe_session_targets AS t 
        ON t.event_session_address = s.address
    WHERE s.name = N'deadlocks'
        AND t.target_name = N'ring_buffer';

    ;WITH src AS 
    (
        SELECT xeXML = xm.s.query('.')
        FROM @target_data.nodes('/RingBufferTarget/event') AS xm(s)
    )
    INSERT INTO #xmlResults (xeXML, xeTimeStamp, xeProcessID)
    SELECT src.xeXML
        , [xeTimeStamp] = src.xeXML.value('(/event/@timestamp)[1]', 'datetimeoffset(7)')
        , xeProcessID = src.xeXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)')
    FROM src

    INSERT INTO dbo.deadlock_xml_events (xeProcessID, xeTimeStamp, xeXML)
    SELECT xr.xeProcessID
        , xr.xeTimeStamp
        , xr.xeXML
    FROM #xmlResults xr
    WHERE NOT EXISTS (
        SELECT 1
        FROM dbo.deadlock_xml_events dxe
        WHERE dxe.xeTimeStamp = xr.xeTimeStamp
            AND dxe.xeProcessID = xr.xeProcessID
        );
END
GO

USE dba_admin;

IF OBJECT_ID(N'dbo.GatherDeadlockEvents', N'P') IS NOT NULL

BEGIN

DROP PROCEDURE dbo.GatherDeadlockEvents;

END

Gathers deadlock events from the ring buffer target

into the dbo.deadlock_xml_events table.

Should be ran from a frequently occurring SQL Server

Agent job.

CREATE PROCEDURE dbo.GatherDeadlockEvents

BEGIN

SET XACT_ABORT, NOCOUNT ON;

IF OBJECT_ID(N'dbo.deadlock_xml_events', N'U') IS NULL

CREATE TABLE dbo.deadlock_xml_events

(

xeTimeStamp datetimeoffset NOT NULL

, xeProcessID varchar(20) NOT NULL

, xeXML XML NOT NULL

, CONSTRAINT deadlock_xml_events_pk

PRIMARY KEY CLUSTERED (xeTimeStamp, xeProcessID)

);

IF OBJECT_ID(N'tempdb..#xmlResults', N'U') IS NULL

CREATE TABLE #xmlResults

(

xeTimeStamp datetimeoffset NOT NULL

, xeProcessID varchar(20) NOT NULL

, xeXML XML NOT NULL

, PRIMARY KEY CLUSTERED (xeTimeStamp, xeProcessID)

);

TRUNCATE TABLE #xmlResults;

DECLARE @target_data xml;

SELECT @target_data = CONVERT(xml, target_data)

FROM sys.dm_xe_sessions AS s

JOIN sys.dm_xe_session_targets AS t

ON t.event_session_address = s.address

WHERE s.name = N'deadlocks'

AND t.target_name = N'ring_buffer';

;WITH src AS

(

SELECT xeXML = xm.s.query('.')

FROM @target_data.nodes('/RingBufferTarget/event') AS xm(s)

)

INSERT INTO #xmlResults (xeXML, xeTimeStamp, xeProcessID)

SELECT src.xeXML

, [xeTimeStamp] = src.xeXML.value('(/event/@timestamp)[1]', 'datetimeoffset(7)')

, xeProcessID = src.xeXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)')

FROM src

INSERT INTO dbo.deadlock_xml_events (xeProcessID, xeTimeStamp, xeXML)

SELECT xr.xeProcessID

, xr.xeTimeStamp

, xr.xeXML

FROM #xmlResults xr

WHERE NOT EXISTS (

SELECT 1

FROM dbo.deadlock_xml_events dxe

WHERE dxe.xeTimeStamp = xr.xeTimeStamp

AND dxe.xeProcessID = xr.xeProcessID

);

END

This code runs the stored procedure once in order to create the dbo.deadlock_xml_events table.

EXEC dbo.GatherDeadlockEvents;

1	EXEC dbo.GatherDeadlockEvents;

Creating a SQL Server Agent Job

Here, we’re creating a SQL Server Agent Job to run the dbo.GatherDeadlockEvents stored procedure once every 5 minutes.

/*
    Creates a SQL Server Agent job to run the dbo.GatherDeadlockEvents stored procedure.
    The job is scheduled to run every 5 minutes.
*/
DECLARE @JobID uniqueidentifier = NULL;
DECLARE @ScheduleID uniqueidentifier = NULL;

IF NOT EXISTS (
    SELECT 1 
    FROM msdb.dbo.sysjobs sj 
    WHERE sj.name = N'DBA : Gather Deadlock Events'
    )
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM msdb.dbo.syscategories sc 
        WHERE sc.name = N'DBA Tools'
        )
    BEGIN
        EXEC msdb.dbo.sp_add_category @class = 'JOB'
            , @type = 'LOCAL'
            , @name = N'DBA Tools';
    END

    EXEC msdb.dbo.sp_add_job @job_name = N'DBA : Gather Deadlock Events'
        , @enabled = 1
        , @description = N'Gathers deadlock XML event data from the deadlocks Extended Events Session, and persists the data in dbo.deadlock_xml_events.'
        , @start_step_id = 1
        , @category_name = N'DBA Tools'
        , @owner_login_name = N'sa'
        , @notify_level_eventlog = 0
        , @notify_level_email = 2
        , @notify_level_netsend = 0
        , @notify_level_page = 0
        , @notify_email_operator_name = N'DBA'
        , @notify_netsend_operator_name = NULL
        , @notify_page_operator_name = NULL
        , @delete_level = 0
        , @originating_server = N'(local)'
        , @job_id = @JobID OUTPUT;

    EXEC msdb.dbo.sp_add_jobstep @job_id = @JobID
        , @step_id = 1
        , @step_name = N'Gather Deadlocks'
        , @subsystem = N'TSQL'
        , @command = 'EXEC dbo.GatherDeadlockEvents;'
        , @on_success_action = 1
        , @on_fail_action = 2
        , @database_name = N'dba_admin'
        , @database_user_name = NULL;

    EXEC msdb.dbo.sp_add_schedule @schedule_name = N'DBA : Gather Deadlock Events Schedule'
        , @enabled = 1
        , @freq_type = 4 --daily
        , @freq_interval = 1 --every day
        , @freq_subday_type = 0x04 -- every @freq_subday_interval minutes
        , @freq_subday_interval = 5
        , @owner_login_name = N'sa'
        , @schedule_id = @ScheduleID OUTPUT
        , @originating_server = N'(local)';

    EXEC msdb.dbo.sp_attach_schedule @job_id = @JobID
        , @schedule_id = @ScheduleID;

    EXEC msdb.dbo.sp_add_jobserver @job_name = N'DBA : Gather Deadlock Events', @server_name = N'(local)';
END
GO

Creates a SQL Server Agent job to run the dbo.GatherDeadlockEvents stored procedure.

The job is scheduled to run every 5 minutes.

DECLARE @JobID uniqueidentifier = NULL;

DECLARE @ScheduleID uniqueidentifier = NULL;

IF NOT EXISTS (

SELECT 1

FROM msdb.dbo.sysjobs sj

WHERE sj.name = N'DBA : Gather Deadlock Events'

)

BEGIN

IF NOT EXISTS (

SELECT 1

FROM msdb.dbo.syscategories sc

WHERE sc.name = N'DBA Tools'

)

BEGIN

EXEC msdb.dbo.sp_add_category @class = 'JOB'

, @type = 'LOCAL'

, @name = N'DBA Tools';

END

EXEC msdb.dbo.sp_add_job @job_name = N'DBA : Gather Deadlock Events'

, @enabled = 1

, @description = N'Gathers deadlock XML event data from the deadlocks Extended Events Session, and persists the data in dbo.deadlock_xml_events.'

, @start_step_id = 1

, @category_name = N'DBA Tools'

, @owner_login_name = N'sa'

, @notify_level_eventlog = 0

, @notify_level_email = 2

, @notify_level_netsend = 0

, @notify_level_page = 0

, @notify_email_operator_name = N'DBA'

, @notify_netsend_operator_name = NULL

, @notify_page_operator_name = NULL

, @delete_level = 0

, @originating_server = N'(local)'

, @job_id = @JobID OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @JobID

, @step_id = 1

, @step_name = N'Gather Deadlocks'

, @subsystem = N'TSQL'

, @command = 'EXEC dbo.GatherDeadlockEvents;'

, @on_success_action = 1

, @on_fail_action = 2

, @database_name = N'dba_admin'

, @database_user_name = NULL;

EXEC msdb.dbo.sp_add_schedule @schedule_name = N'DBA : Gather Deadlock Events Schedule'

, @enabled = 1

, @freq_type = 4 --daily

, @freq_interval = 1 --every day

, @freq_subday_type = 0x04 -- every @freq_subday_interval minutes

, @freq_subday_interval = 5

, @owner_login_name = N'sa'

, @schedule_id = @ScheduleID OUTPUT

, @originating_server = N'(local)';

EXEC msdb.dbo.sp_attach_schedule @job_id = @JobID

, @schedule_id = @ScheduleID;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'DBA : Gather Deadlock Events', @server_name = N'(local)';

END

Sample Analysis Code

The following sample code surfaces details about the actions involved in deadlock events.

/*
    Deadlock Analysis Samples
*/
USE dba_admin;
DECLARE @StartTime datetimeoffset;
DECLARE @EndTime datetimeoffset;
DECLARE @Offset int;
SET @StartTime = DATEADD(HOUR, -4, GETDATE()); --modify these to suit your needs
SET @EndTime = GETDATE();
SET @Offset = DATEDIFF(MINUTE, GETDATE(), GETUTCDATE());
SET @StartTime = DATEADD(MINUTE, @Offset, @StartTime);
SET @EndTime = DATEADD(MINUTE, @Offset, @EndTime);

SELECT StartTime = CONVERT(varchar(30), @StartTime, 127), EndTime = CONVERT(varchar(30), @EndTime, 127)

SELECT [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')
    , [1_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/executionStack/frame/text())[1]', 'nvarchar(4000)')
    , [1_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/inputbuf/text())[1]', 'nvarchar(4000)')
    , [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')
    , [2_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/text())[1]', 'nvarchar(4000)')
    , [2_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/inputbuf/text())[1]', 'nvarchar(4000)')
FROM #xmlResults x
WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1
    AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1
    AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1;

;WITH src AS
(    
SELECT [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')
    , [1_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/executionStack/frame/text())[1]', 'nvarchar(4000)')
    , [1_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/inputbuf/text())[1]', 'nvarchar(4000)')
    , [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')
    , [2_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/text())[1]', 'nvarchar(4000)')
    , [2_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/inputbuf/text())[1]', 'nvarchar(4000)')
FROM #xmlResults x
WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1
    AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1
    AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1
)
SELECT src.[1]
    , src.[1_code]
    , src.[1_input_buffer]
    , src.[2]
    , src.[2_code]
    , src.[2_input_buffer]
    , [Number of Deadlocks] = COUNT(1)
FROM src
GROUP BY src.[1]
    , src.[2]
    , src.[1_code]
    , src.[2_code]
    , src.[1_input_buffer]
    , src.[2_input_buffer];

;WITH src AS
(    
SELECT EventDate = DATEADD(HOUR, 0 - @Offset, CONVERT(datetime, x.xeXML.value('(/event/@timestamp)[1]', 'datetimeoffset')))
    , [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')
    , [1_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/executionStack/frame/text())[1]', 'nvarchar(4000)')
    , [1_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/inputbuf/text())[1]', 'nvarchar(4000)')
    , [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')
    , [2_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/text())[1]', 'nvarchar(4000)')
    , [2_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/inputbuf/text())[1]', 'nvarchar(4000)')
FROM #xmlResults x
WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1
    AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1
    AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1
)
SELECT src.EventDate
    , src.[1]
    , src.[1_code]
    , src.[1_input_buffer]
    , src.[2]
    , src.[2_code]
    , src.[2_input_buffer]
FROM src
ORDER BY src.EventDate DESC;



;WITH src AS
(
SELECT dt = x.xeXML.value('(/event/@timestamp)[1]', 'datetime')
    , [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')
    , [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')
    , [3] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[3]', 'nvarchar(128)')
    , [4] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[4]', 'nvarchar(128)')
    , [5] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[5]', 'nvarchar(128)')
    , [6] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[6]', 'nvarchar(128)')
    , [7] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[7]', 'nvarchar(128)')
    , [8] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[8]', 'nvarchar(128)')
    , [9] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[9]', 'nvarchar(128)')
    , [10] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[10]', 'nvarchar(128)')
    , [11] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[11]', 'nvarchar(128)')
    , [12] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[12]', 'nvarchar(128)')
    , [13] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[13]', 'nvarchar(128)')
    , [14] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[14]', 'nvarchar(128)')
    , [15] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[15]', 'nvarchar(128)')
    , [16] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[16]', 'nvarchar(128)')
    , [17] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[17]', 'nvarchar(128)')
    , [18] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[18]', 'nvarchar(128)')
    , [19] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[19]', 'nvarchar(128)')
    , [20] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[20]', 'nvarchar(128)')
FROM dbo.deadlock_xml_events x
WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1
    AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1
    AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1
)
SELECT ProcName = t.x
    , [Count] = COUNT(1)
FROM (
SELECT *
FROM src
UNPIVOT
([x] FOR [ProcName] IN (
      [1] 
    , [2] 
    , [3] 
    , [4] 
    , [5] 
    , [6] 
    , [7] 
    , [8] 
    , [9] 
    , [10]
    , [11]
    , [12]
    , [13]
    , [14]
    , [15]
    , [16]
    , [17]
    , [18]
    , [19]
    , [20]
    )) upvt
) t
GROUP BY t.x

;WITH src AS
(
SELECT dt = x.xeXML.value('(/event/@timestamp)[1]', 'datetime')
    , [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')
    , [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')
    , [3] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[3]', 'nvarchar(128)')
    , [4] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[4]', 'nvarchar(128)')
    , [5] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[5]', 'nvarchar(128)')
    , [6] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[6]', 'nvarchar(128)')
    , [7] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[7]', 'nvarchar(128)')
    , [8] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[8]', 'nvarchar(128)')
    , [9] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[9]', 'nvarchar(128)')
    , [10] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[10]', 'nvarchar(128)')
    , [11] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[11]', 'nvarchar(128)')
    , [12] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[12]', 'nvarchar(128)')
    , [13] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[13]', 'nvarchar(128)')
    , [14] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[14]', 'nvarchar(128)')
    , [15] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[15]', 'nvarchar(128)')
    , [16] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[16]', 'nvarchar(128)')
    , [17] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[17]', 'nvarchar(128)')
    , [18] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[18]', 'nvarchar(128)')
    , [19] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[19]', 'nvarchar(128)')
    , [20] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[20]', 'nvarchar(128)')
FROM dbo.deadlock_xml_events x
WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1
    AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1
    AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1
)
SELECT *
FROM src
UNPIVOT
([x] FOR [ProcName] IN (
      [1] 
    , [2] 
    , [3] 
    , [4] 
    , [5] 
    , [6] 
    , [7] 
    , [8] 
    , [9] 
    , [10]
    , [11]
    , [12]
    , [13]
    , [14]
    , [15]
    , [16]
    , [17]
    , [18]
    , [19]
    , [20]
    )) upvt

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

Deadlock Analysis Samples

USE dba_admin;

DECLARE @StartTime datetimeoffset;

DECLARE @EndTime datetimeoffset;

DECLARE @Offset int;

SET @StartTime = DATEADD(HOUR, -4, GETDATE()); --modify these to suit your needs

SET @EndTime = GETDATE();

SET @Offset = DATEDIFF(MINUTE, GETDATE(), GETUTCDATE());

SET @StartTime = DATEADD(MINUTE, @Offset, @StartTime);

SET @EndTime = DATEADD(MINUTE, @Offset, @EndTime);

SELECT StartTime = CONVERT(varchar(30), @StartTime, 127), EndTime = CONVERT(varchar(30), @EndTime, 127)

SELECT [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')

, [1_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/executionStack/frame/text())[1]', 'nvarchar(4000)')

, [1_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/inputbuf/text())[1]', 'nvarchar(4000)')

, [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')

, [2_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/text())[1]', 'nvarchar(4000)')

, [2_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/inputbuf/text())[1]', 'nvarchar(4000)')

FROM #xmlResults x

WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1

AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1

AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1;

;WITH src AS

(

SELECT [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')

, [1_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/executionStack/frame/text())[1]', 'nvarchar(4000)')

, [1_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/inputbuf/text())[1]', 'nvarchar(4000)')

, [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')

, [2_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/text())[1]', 'nvarchar(4000)')

, [2_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/inputbuf/text())[1]', 'nvarchar(4000)')

FROM #xmlResults x

WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1

AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1

AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1

)

SELECT src.[1]

, src.[1_code]

, src.[1_input_buffer]

, src.[2]

, src.[2_code]

, src.[2_input_buffer]

, [Number of Deadlocks] = COUNT(1)

FROM src

GROUP BY src.[1]

, src.[2]

, src.[1_code]

, src.[2_code]

, src.[1_input_buffer]

, src.[2_input_buffer];

;WITH src AS

(

SELECT EventDate = DATEADD(HOUR, 0 - @Offset, CONVERT(datetime, x.xeXML.value('(/event/@timestamp)[1]', 'datetimeoffset')))

, [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')

, [1_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/executionStack/frame/text())[1]', 'nvarchar(4000)')

, [1_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[1]/inputbuf/text())[1]', 'nvarchar(4000)')

, [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')

, [2_code] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/executionStack/frame/text())[1]', 'nvarchar(4000)')

, [2_input_buffer] = x.xeXML.value('(/event/data/value/deadlock/process-list/process[2]/inputbuf/text())[1]', 'nvarchar(4000)')

FROM #xmlResults x

WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1

AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1

AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1

)

SELECT src.EventDate

, src.[1]

, src.[1_code]

, src.[1_input_buffer]

, src.[2]

, src.[2_code]

, src.[2_input_buffer]

FROM src

ORDER BY src.EventDate DESC;

;WITH src AS

(

SELECT dt = x.xeXML.value('(/event/@timestamp)[1]', 'datetime')

, [1] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'nvarchar(128)')

, [2] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'nvarchar(128)')

, [3] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[3]', 'nvarchar(128)')

, [4] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[4]', 'nvarchar(128)')

, [5] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[5]', 'nvarchar(128)')

, [6] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[6]', 'nvarchar(128)')

, [7] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[7]', 'nvarchar(128)')

, [8] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[8]', 'nvarchar(128)')

, [9] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[9]', 'nvarchar(128)')

, [10] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[10]', 'nvarchar(128)')

, [11] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[11]', 'nvarchar(128)')

, [12] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[12]', 'nvarchar(128)')

, [13] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[13]', 'nvarchar(128)')

, [14] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[14]', 'nvarchar(128)')

, [15] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[15]', 'nvarchar(128)')

, [16] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[16]', 'nvarchar(128)')

, [17] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[17]', 'nvarchar(128)')

, [18] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[18]', 'nvarchar(128)')

, [19] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[19]', 'nvarchar(128)')

, [20] = x.xeXML.value('(/event/data/value/deadlock/process-list/process/executionStack/frame/@procname)[20]', 'nvarchar(128)')

FROM dbo.deadlock_xml_events x

WHERE x.xeXML.exist('/event[@name="xml_deadlock_report"]') = 1

AND x.xeXML.exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1

AND x.xeXML.exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1

)

SELECT ProcName = t.x

, [Count] = COUNT(1)

FROM (

SELECT *

FROM src

UNPIVOT

([x] FOR [ProcName] IN (

[1]

, [2]

, [3]

, [4]

, [5]

, [6]

, [7]

, [8]

, [9]

, [10]

, [11]

, [12]

, [13]

, [14]

, [15]

, [16]

, [17]

, [18]

, [19]

, [20]

)) upvt

) t

GROUP BY t.x