Yet another SQL Server enthusiast


sp_DeadlockAnalyzer v02

Filed under: Uncategorized — ---- @ 7:07 AM

I have made following changes in sp_DeadlockAnalyzer:

  • “Resources” record-set includes resources (mainly objects – tables or indexes views, indexes), pivoted SPIDs and locks (acqr = acquired / owner process, wait = waiter process) for a better understanding.
  • “SQL Statements” record-set includes now the source line of every statement.
  • “Data access operators” record-set includes those operators which access resources

Sample output:


Source code:

Code Snippet
USE master;


    SELECT    *
    FROM    sys.procedures p
    JOIN    sys.schemas s ON p.schema_id = s.schema_id
    WHERE = 'dbo'
    AND = 'sp_DeadlockAnalyzer'
    EXEC ('CREATE PROCEDURE dbo.sp_DeadlockAnalyzer AS SELECT 1');

ALTER PROCEDURE dbo.sp_DeadlockAnalyzer
    @xdl XML – Deadlock graph event
    IF OBJECT_ID(N'tempdb.dbo.#Resources') IS NOT NULL
        DROP TABLE #Resources;
    IF OBJECT_ID(N'tempdb.dbo.#Processes') IS NOT NULL
        DROP TABLE #Processes;

    – Information about resources
    CREATE TABLE #Resources (
        Id                            INT IDENTITY PRIMARY KEY,
        Resource_LockGranularity    SYSNAME NOT NULL,
        Resource_DatabaseId            INT NOT NULL,
        Resource_HoBtID                BIGINT NOT NULL,
        Resource_ObjectName            SYSNAME NULL,
        Resource_IndexName            SYSNAME NULL,
        ProcessOwner_Id                SYSNAME NOT NULL,
        ProcessOwner_LockMode        SYSNAME NOT NULL,

        ProcessWaiter_Id            SYSNAME NOT NULL,
        ProcessWaiter_LockMode        SYSNAME NOT NULL

    INSERT    #Resources (
        Resource_LockGranularity    ,
        Resource_DatabaseId            ,
        Resource_HoBtID                ,
        Resource_ObjectName            ,
        Resource_IndexName            ,
        ProcessOwner_Id                ,
        ProcessOwner_LockMode        ,

        ProcessWaiter_Id            ,
    SELECT    Resource_LockGranularity= x.XmlCol.value('local-name(.)','SYSNAME'),
            Resource_Database        = x.XmlCol.value('@dbid','INT'),
            Resource_HoBtID            = x.XmlCol.value('@hobtid','BIGINT'),
            Resource_ObjectName        = QUOTENAME(PARSENAME(x.XmlCol.value('@objectname','SYSNAME'), 3)) + '.' + QUOTENAME(PARSENAME(x.XmlCol.value('@objectname','SYSNAME'), 2)) + '.' + QUOTENAME(PARSENAME(x.XmlCol.value('@objectname','SYSNAME'), 1)),
            Resource_IndexName        = QUOTENAME(x.XmlCol.value('@indexname','SYSNAME')),

            ProcessOwner_Id            = own.XmlCol.value('@id', 'SYSNAME'),
            ProcessOwner_LockMode    = own.XmlCol.value('@mode', 'SYSNAME'),

            ProcessWaiter_Id        = wtr.XmlCol.value('@id', 'SYSNAME'),
            ProcessWaiter_Mode        = wtr.XmlCol.value('@mode', 'SYSNAME')
    FROM    @xdl.nodes('deadlock-list/deadlock/resource-list/*') x(XmlCol)
    OUTER APPLY x.XmlCol.nodes('owner-list/owner') own(XmlCol)
    OUTER APPLY x.XmlCol.nodes('waiter-list/waiter') wtr(XmlCol);

    CREATE TABLE #Processes (
        SPID             INT NOT NULL,
        IsVictim         BIT NOT NULL,
        Database_Name     SYSNAME NOT NULL,
        InputBuffer         XML NULL,
        TransactionName  SYSNAME NULL,
        IsolationLevel     SYSNAME NULL,
        Snapshot_Isolation_State SYSNAME NULL,
        DeadlockPriority SMALLINT NULL,
        LogUsed             INT NULL,
        ClientApp         SYSNAME NULL,
        HostName         SYSNAME NULL,
        LoginName         SYSNAME NULL,
        Database_Id         INT NOT NULL,
        CallStack         XML NULL,
        Process_Id         SYSNAME PRIMARY KEY
    INSERT     #Processes
    SELECT    y.SPID, y.IsVictim,
            QUOTENAME(DB_NAME(y.Database_Id)) AS Database_Name, y.InputBuffer,
            y.TransactionName, y.IsolationLevel, db.snapshot_isolation_state_desc,  y.DeadlockPriority, y.LogUsed,
            y.ClientApp, y.HostName, y.LoginName, y.Database_Id,
    FROM (
        SELECT    Process_Id    = x.XmlCol.value('(@id)[1]', 'SYSNAME'),
                SPID        = x.XmlCol.value('(@spid)[1]', 'INT'),
                IsVictim = x.XmlCol.exist('(.)[(@id)[1] = (../../@victim)[1]]'),

                – CurrentDatabase=  DB_NAME(x.XmlCol.value('(@currentdb)[1]', 'SMALLINT')) + N' (' + x.XmlCol.value('(@currentdb)[1]', 'NVARCHAR(5)') + N')',
                Database_Id    =  x.XmlCol.value('(@currentdb)[1]', 'SMALLINT'),
                InputBuffer    = x.XmlCol.query('inputbuf'),

                TransactionName    = x.XmlCol.value('(@transactionname)[1]', 'SYSNAME'),
                IsolationLevel = x.XmlCol.value('(@isolationlevel)[1]', 'SYSNAME'),
                DeadlockPriority = x.XmlCol.value('(@priority)[1]', 'SMALLINT'),
                LogUsed        = x.XmlCol.value('(@logused)[1]', 'INT'),
                ClientApp    = x.XmlCol.value('(@clientapp)[1]', 'SYSNAME'),
                HostName    = x.XmlCol.value('(@hostname)[1]', 'SYSNAME'),
                LoginName    = x.XmlCol.value('(@loginname)[1]', 'SYSNAME'),
                CallStack    = x.XmlCol.query('./executionStack')
        FROM    @xdl.nodes('deadlock-list/deadlock/process-list/process') x(XmlCol)
    ) y INNER JOIN sys.databases db ON y.Database_Id = db.database_id;

    DECLARE @DistinctProcesses TABLE (SPID_Description VARCHAR(16) PRIMARY KEY);
    INSERT    @DistinctProcesses (SPID_Description)
    FROM    #Processes p;
    DECLARE @DistinctProcessesList NVARCHAR(4000);
    SET        @DistinctProcessesList = '';
    SELECT    @DistinctProcessesList = @DistinctProcessesList + ', ' + dp.SPID_Description
    FROM    @DistinctProcesses dp;
    SET        @DistinctProcessesList = STUFF(@DistinctProcessesList, 1, 2, '');

    DECLARE @SqlStatement NVARCHAR(MAX);
    SET        @SqlStatement = N'
    SELECT    t.Resource_ObjectName, t.Resource_IndexName, t.Resource_LockGranularity,
            ' + @DistinctProcessesList + ',
            t.Resource_DatabaseId, t.Resource_HoBtID
    FROM (
        SELECT    x.Resource_LockGranularity, x.Resource_ObjectName, x.Resource_IndexName,
                ''SPID '' + CONVERT(VARCHAR(11), y.SPID) AS SPID_Description, y.LockInfo,
                x.Resource_DatabaseId, x.Resource_HoBtID
        FROM (
            SELECT    r.Resource_LockGranularity, r.Resource_ObjectName, r.Resource_IndexName,
                    r.ProcessOwner_Id, ''acqr '' + r.ProcessOwner_LockMode AS OwnerLockInfo,
                    r.ProcessWaiter_Id, ''wait '' + r.ProcessWaiter_LockMode AS WaitwerLockInfo,
                    r.Resource_DatabaseId, r.Resource_HoBtID
            FROM    #Resources r
        ) x
        CROSS APPLY (
            SELECT    p.SPID, x.OwnerLockInfo
            FROM    #Processes p WHERE p.Process_Id = x.ProcessOwner_Id
            UNION ALL
            SELECT    p.SPID, x.WaitwerLockInfo
            FROM    #Processes p WHERE p.Process_Id = x.ProcessWaiter_Id
        ) y(SPID, LockInfo)
    ) z
    PIVOT( MAX(z.LockInfo) FOR z.SPID_Description IN (' + @DistinctProcessesList + ') ) t';
    EXEC (@SqlStatement);

    – Information about server processes / conections including SQL batches
    SELECT * FROM #Processes ORDER BY IsVictim DESC;

    – Statements and execution plans
    DECLARE @Statements TABLE (
        Process_Id            SYSNAME NOT NULL,
        SPID                INT NOT NULL,
        IsVictim            BIT NOT NULL,
        [Statement]            SYSNAME NOT NULL,    
        [SqlHandle]            VARBINARY(64) NULL,
        [Text]                NVARCHAR(4000) NULL,
        Line                INT NULL,
        StmtStartOffset        INT NULL,
        StmtEndOffset        INT NULL,
        StatementNum        INT NOT NULL,
        PlanHandle            VARBINARY(64) NULL,
        QueryPlan            XML NULL
    INSERT    @Statements
    SELECT    y.*, qs.plan_handle AS PlanHandle, pln.query_plan AS QueryPlan
    FROM (
        SELECT    Process_Id        = x.Process_Id,
                SPID            = x.SPID,
                IsVictim= x.IsVictim,
                [Statement]        = y.XmlCol.value('(@procname)[1]', 'SYSNAME'),
                [SqlHandle]        = CONVERT(VARBINARY(64), y.XmlCol.value('(@sqlhandle)[1]', 'VARCHAR(128)'), 1),
                [Text]            = y.XmlCol.value('(text())[1]', 'NVARCHAR(4000)'),
                Line            = y.XmlCol.value('(@line)[1]', 'INT'),
                StmtStartOffset    = ISNULL(y.XmlCol.value('(@stmtstart)[1]', 'INT'), 0),
                StmtEndOffset    = ISNULL(y.XmlCol.value('(@stmtend)[1]', 'INT'), -1),
                StatementNum    = ROW_NUMBER() OVER(ORDER BY y.XmlCol DESC)
        FROM    #Processes x
        OUTER APPLY x.CallStack.nodes('executionStack/frame') y(XmlCol)
    ) y
    LEFT JOIN sys.dm_exec_query_stats qs ON y.SqlHandle = qs.sql_handle  AND y.StmtStartOffset = qs.statement_start_offset AND y.StmtEndOffset = qs.statement_end_offset
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) pln

    SELECT    s.SPID, s.IsVictim,
            s.[Statement], s.[Text], s.Line,
            s.QueryPlan, s.PlanHandle, s.SqlHandle,
    FROM    @Statements s
    ORDER BY s.IsVictim DESC, s.StatementNum;

    – Data access operators
        '' AS xsi,
        '' AS xsd,
        DEFAULT ''
    QueryPlans AS (
        SELECT    x.Process_Id, x.SPID, x.IsVictim, x.QueryPlan
        FROM (
            SELECT    s.Process_Id, s.SPID, s.IsVictim, s.QueryPlan, ROW_NUMBER() OVER(PARTITION BY BINARY_CHECKSUM(CONVERT(NVARCHAR(MAX),s.QueryPlan)) ORDER BY @@SPID) AS RowNum
            FROM    @Statements s
            WHERE    s.QueryPlan IS NOT NULL
        ) x
        WHERE    x.RowNum = 1
    SELECT    b.SPID, b.IsVictim,
            b.StatementType, b.[Statement],
            b.LogicalOp, b.PhysicalOp,
            b.ObjectName, b.IndexName, b.IndexKind,
            b.Warnings, b.MissingIndexes,
            b.BatchId, b.StatementId, b.NodeId
    FROM (
        SELECT    a.Process_Id, a.SPID, a.IsVictim,
                a.BatchId, a.StatementId, a.NodeId,
                a.StatementType, a.[Statement], /*a.ParamStatement,*/
                LogicalOp = CASE
                                WHEN a.TableReferenceId = -1 AND a.IndexKind = N'Clustered' AND a.LogicalOp = 'Clustered Index Seek' THEN 'Key Lookup'
                                ELSE a.LogicalOp
                PhysicalOp = CASE
                                WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Insert', 'Table Insert') THEN 'Index Insert'
                                WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Update', 'Table Update') THEN 'Index Update'
                                WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Delete', 'Table Delete') THEN 'Index Delete'
                                WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Merge', 'Table Merge') THEN 'Index Merge'
                                ELSE a.PhysicalOp
                a.ObjectName, a.IndexName, a.IndexKind,
                a.Warnings, a.MissingIndexes
        FROM (
            SELECT    – batch_XmlCol = batch.XmlCol.query('.'),
                    BatchId            = DENSE_RANK() OVER(ORDER BY batch.XmlCol),
                    StatementId        = stmt.XmlCol.value('(@StatementId)[1]', 'INT'),
                    NodeId            = oper.XmlCol.value('(@NodeId)[1]', 'INT'),

                    StatementType    = stmt.XmlCol.value('(@StatementType)[1]', 'SYSNAME'),
                    [Statement]        = stmt.XmlCol.value('(@StatementText)[1]', 'NVARCHAR(4000)'),
                    ParamStatement    = stmt.XmlCol.value('(@ParameterizedText)[1]', 'NVARCHAR(4000)'),

                    LogicalOp        = oper.XmlCol.value('(@LogicalOp)[1]', 'SYSNAME'),
                    PhysicalOp        = oper.XmlCol.value('(@PhysicalOp)[1]', 'SYSNAME'),
                    [TableReferenceId]= objt.XmlCol.value('(@TableReferenceId)[1]', 'INT'),
                    [IndexKind]        = objt.XmlCol.value('(@IndexKind)[1]', 'SYSNAME'),

                    [ObjectName]    = ISNULL(objt.XmlCol.value('(@Database)', 'SYSNAME') + '.' + objt.XmlCol.value('(@Schema)', 'SYSNAME') + '.', '') + objt.XmlCol.value('(@Table)[1]', 'SYSNAME'),
                    [IndexName]        = ISNULL(objt.XmlCol.value('(@Index)', 'SYSNAME'), ''),

                    Warnings        = wrng.XmlCol.query('.'),
                    MissingIndexes    = misx.XmlCol.query('.'),

                    Process_Id        = xp.Process_Id,
                    SPID            = xp.SPID,
                    IsVictim= xp.IsVictim
            FROM    QueryPlans xp
            CROSS APPLY xp.QueryPlan.nodes('//Batch') batch(XmlCol)
            CROSS APPLY batch.XmlCol.nodes('Statements/StmtSimple') stmt(XmlCol)
            OUTER APPLY stmt.XmlCol.nodes('QueryPlan/Warnings') wrng(XmlCol)
            OUTER APPLY stmt.XmlCol.nodes('QueryPlan/MissingIndexes') misx(XmlCol)
            OUTER APPLY stmt.XmlCol.nodes('QueryPlan//RelOp') oper(XmlCol) – Operators
            –OUTER APPLY oper.XmlCol.nodes('(.//Object)[1]') objt(XmlCol)
            OUTER APPLY oper.XmlCol.nodes('./*/Object') objt(XmlCol)
        ) a
    ) b
    WHERE b.PhysicalOp IN  (
        'Table Insert', 'Table Update', 'Table Delete', 'Table Merge', 'Table Scan',
        'Clustered Index Insert', 'Clustered Index Update', 'Clustered Index Delete', 'Clustered Index Merge', 'Clustered Index Scan', 'Clustered Index Seek',
        'Index Insert', 'Index Update', 'Index Delete', 'Index Merge', 'Index Scan', 'Index Seek',
        'RID Lookup' – Key Lookup = Clustered Index Seek
    )    AND EXISTS (
        SELECT    *
        FROM    #Resources r
        WHERE    (r.ProcessOwner_Id = b.Process_Id OR r.ProcessWaiter_Id = b.Process_Id)
        AND        r.Resource_ObjectName = b.ObjectName
        AND        r.Resource_IndexName = b.IndexName
    ORDER BY IsVictim DESC, SPID, BatchId, StatementId, NodeId;

Sample usage: Create a trace with SQL Profiler including [Deadlock graph event]. After intercepting a deadlock event you have to use [Extract event data] command to save on local computer the XML content of [Deadlock graph event] into a *.xdl file (ex. D:\DbEvents\deadlock18.xdl). Then, you have to execute the stored procedure:

Code Snippet
DECLARE @xdl XML – Deadlock graph event
SELECT    @xdl = x.BulkColumn
FROM    OPENROWSET(BULK 'D:\DbEvents\deadlock18.xdl', SINGLE_NCLOB) x;

EXEC dbo.sp_DeadlockAnalyzer @xdl;

Note #1: The type of resource can be keylock or pagelock (at this moment).

Note #2: I’ve tested this procedure only with SQL Profiler – SQL Server 2012.

Note #3: This procedure should be executed on the same SQL instance.

Note #4: Next update will analyze also triggers execution plans. A future version will try to correlate data access operators and type of SQL statements  (ex. SELECT -> S locks, UPDATE -> U or X lock on target table, etc.).

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at


Get every new post delivered to your Inbox.

%d bloggers like this: