Yet another SQL Server enthusiast

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS (
    SELECT    *
    FROM    sys.procedures p
    JOIN    sys.schemas s ON p.schema_id = s.schema_id
    WHERE    s.name = 'dbo'
    AND        p.name = 'sp_DeadlockAnalyzer'
)
BEGIN
    EXEC ('CREATE PROCEDURE dbo.sp_DeadlockAnalyzer AS SELECT 1');
END
GO

ALTER PROCEDURE dbo.sp_DeadlockAnalyzer
(
    @xdl XML – Deadlock graph event
)
AS
BEGIN
    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            ,
        ProcessWaiter_LockMode
    )
    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,
            y.CallStack,
            y.Process_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)
    SELECT    DISTINCT QUOTENAME('SPID ' + CONVERT(VARCHAR(11), p.SPID))
    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,
            s.Process_Id
    FROM    @Statements s
    ORDER BY s.IsVictim DESC, s.StatementNum;

    – Data access operators
    WITH XMLNAMESPACES (
        'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
        'http://www.w3.org/2001/XMLSchema' AS xsd,
        DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
    ),
    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.Process_Id,
            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
                            END,
                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
                            END,
                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;
END;
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC dbo.sp_DeadlockAnalyzer @xdl;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;