--------------------------------------------------------------------------------
--  File        :   Demo 07_Analyze log shipping deadlock graphs with XQuery,2005+.sql
--
--  Purpose     :   This script uses XQuery to analyze the sample 
--                  "Logshipping deadlock graphs,20090612T1151.xdl" XDL file, 
--                  which was collected when troubleshooting frequent 
--                  deadlocks with log shipping jobs. 
--
--                  The server had about twenty databases that were being 
--                  log shipped and another thirty user databases that 
--                  weren't being log shipped:
--                      --  The log shipped databases were backed up every 
--                          5 minutes with BACKUP LOG.
--                      --  The non-log shipped databases were backed up 
--                          every 15 minutes with the 
--                          [msdb].[dbo].[prCustomerJobSelector] routine.
--                      --  The [msdb].[dbo].[prCustomerBackupCleanUp] 
--                          routine ran twice-a-day (at 2:00 a.m. and 
--                          6:50 a.m.) and, amongst other things, called the 
--                          [msdb].[dbo].[sp_delete_backuphistory] system 
--                          stored procedure, which prunes the contents of 
--                          the backup history tables in the [msdb] system 
--                          database.
--
--                  The log shipping backup jobs were failing frequently due 
--                  to deadlocks.
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--                  SQL Server 2012 (11.0)
--
--  Version     :   3.0009.0090.0000 (2012-05-10)
--------------------------------------------------------------------------------

USE [tempdb];

GO

DECLARE @DeadlockCount Int;
DECLARE @DeadlockGraph XML;
DECLARE @DeadlockGraphFilePath NVarChar(260);
DECLARE @DynamicSQL NVarChar(1024);
DECLARE @IntervalLength_seconds Int;
DECLARE @ReferenceDate DateTime;
DECLARE @ResultSetsToReturn Int;

SET @IntervalLength_seconds = 300;
SET @ReferenceDate = '1970-01-01 00:00:00.000';

--  Set the bitmask to show which parts of the demonstration to run and 
--  return (not all valid combinations shown):
--      1   Just the first result set
--      2   Just the second result set
--      4   Just the third result set
--      8   Just the fourth result set
--      15  All four result sets
SET @ResultSetsToReturn = 1;

BEGIN TRY
    SET @DeadlockGraphFilePath = N'E:\tnbarkhouse\Presentations\Presentation_Deadlocks\Sample files\Logshipping deadlock graphs,20090612T1151.xdl';

    --  The OpenRowset() T-SQL function requires a string literal for the 
    --  file path, so use dynamic SQL to avoid hardcoding a path (other than 
    --  in the above assignment to the @DeadlockGraphFilePath variable... in 
    --  a non-demonstration script, the value would be passed into the 
    --  script as a parameter).
    SET @DynamicSQL = N'SELECT @XMLDocument = Cast([BulkColumn] AS XML)' +
        NChar(10) +
        N'FROM OpenRowset' +
        NChar(10) +
        N'(' +
        NChar(10) +
        N'    BULK ''' +
        @DeadlockGraphFilePath +
        N''',' +
        NChar(10) +
        N'    SINGLE_BLOB' +
        NChar(10) +
        N') AS [XMLDocument];';

    --  Execute the dynamic SQL and pull the result into the @XMLDocument 
    --  local variable.
    EXECUTE [master].[sys].[sp_executesql]
        @stmt = @DynamicSQL,
        @params = N'@XMLDocument XML OUTPUT',
        @XMLDocument = @DeadlockGraph OUTPUT;

    --  Count the number of deadlock graphs in the XDL file.
    SELECT @DeadlockCount = DeadlockList.Graphs.value(N'count(deadlock)', N'Int')
    FROM @DeadlockGraph.nodes(N'/deadlock-list') AS DeadlockList(Graphs);

    PRINT N'File            :   ' +
        @DeadlockGraphFilePath +
        NChar(10) +
        N'Deadlock count  :   ' +
        Cast(@DeadlockCount AS NVarChar);

    --  "Shred" the Deadlock Graph XML, if it hasn't already been cached.
    IF ((SELECT Object_ID(N'[tempdb].[dbo].[#Demo07_ShreddedPartiesToTheDeadlocks]')) IS NULL)
    BEGIN
        --  Create a local temporary table to cache the results.
        CREATE TABLE [#Demo07_ShreddedPartiesToTheDeadlocks] (
            [Victim_SPID] Int,
            [Process0_SPID] Int,
            [Process1_SPID] Int,
            [Process2_SPID] Int,
            [Process0_LastBatchStarted] DateTime,
            [Process1_LastBatchStarted] DateTime,
            [Process2_LastBatchStarted] DateTime,
            [Process0_InputBuffer] NVarChar(255),
            [Process1_InputBuffer] NVarChar(255),
            [Process2_InputBuffer] NVarChar(255)
        );

        --  Extract the parties to the deadlock.
        INSERT INTO [#Demo07_ShreddedPartiesToTheDeadlocks]
        SELECT [Victim_SPID],
            [Process0_SPID],
            [Process1_SPID],
            [Process2_SPID],
            [Process0_LastBatchStarted],
            [Process1_LastBatchStarted],
            [Process2_LastBatchStarted],
            Replace([Process0_InputBuffer], N'-- keep 30 days of backup history', N''),
            Replace([Process1_InputBuffer], N'-- keep 30 days of backup history', N''),
            Replace([Process2_InputBuffer], N'-- keep 30 days of backup history', N'')
        FROM (
            SELECT (
                    Case
                        WHEN (DeadlockList.Graphs.value(N'@victim', N'VarChar(15)') = DeadlockList.Graphs.value(N'(process-list/process[1]/@id)[1]', N'VarChar(15)')) THEN DeadlockList.Graphs.value(N'(process-list/process[1]/@spid)[1]', N'Int')
                        WHEN (DeadlockList.Graphs.value(N'@victim', N'VarChar(15)') = DeadlockList.Graphs.value(N'(process-list/process[2]/@id)[1]', N'VarChar(15)')) THEN DeadlockList.Graphs.value(N'(process-list/process[2]/@spid)[1]', N'Int')
                        WHEN (DeadlockList.Graphs.value(N'@victim', N'VarChar(15)') = DeadlockList.Graphs.value(N'(process-list/process[3]/@id)[1]', N'VarChar(15)')) THEN DeadlockList.Graphs.value(N'(process-list/process[3]/@spid)[1]', N'Int')
                        ELSE NULL
                    END
                ) AS [Victim_SPID],
                DeadlockList.Graphs.value(N'(process-list/process[1]/@spid)[1]', N'Int') AS [Process0_SPID],
                DeadlockList.Graphs.value(N'(process-list/process[2]/@spid)[1]', N'Int') AS [Process1_SPID],
                DeadlockList.Graphs.value(N'(process-list/process[3]/@spid)[1]', N'Int') AS [Process2_SPID],
                Cast(Replace(DeadlockList.Graphs.value(N'(process-list/process[1]/@lastbatchstarted)[1]', N'Char(23)'), 'T', ' ') AS DateTime) AS [Process0_LastBatchStarted],
                Cast(Replace(DeadlockList.Graphs.value(N'(process-list/process[2]/@lastbatchstarted)[1]', N'Char(23)'), 'T', ' ') AS DateTime) AS [Process1_LastBatchStarted],
                Cast(Replace(DeadlockList.Graphs.value(N'(process-list/process[3]/@lastbatchstarted)[1]', N'Char(23)'), 'T', ' ') AS DateTime) AS [Process2_LastBatchStarted],
                RTrim(LTrim(Replace(DeadlockList.Graphs.value(N'(process-list/process[1]/inputbuf)[1]', N'NVarChar(255)'), NChar(10), N''))) AS [Process0_InputBuffer],
                RTrim(LTrim(Replace(DeadlockList.Graphs.value(N'(process-list/process[2]/inputbuf)[1]', N'NVarChar(255)'), NChar(10), N''))) AS [Process1_InputBuffer],
                RTrim(LTrim(Replace(DeadlockList.Graphs.value(N'(process-list/process[3]/inputbuf)[1]', N'NVarChar(255)'), NChar(10), N''))) AS [Process2_InputBuffer]
            FROM @DeadlockGraph.nodes(N'/deadlock-list/deadlock') AS DeadlockList(Graphs)
        ) AS [_ProcessDetails];
    END;

    If ((@ResultSetsToReturn & 1) = 1)
    BEGIN
        --  Display the parties to the deadlock.
        SELECT [Victim_SPID],
            [Process0_SPID],
            [Process1_SPID],
            [Process2_SPID],
            [Process0_LastBatchStarted],
            [Process1_LastBatchStarted],
            [Process2_LastBatchStarted],
            [Process0_InputBuffer],
            [Process1_InputBuffer],
            [Process2_InputBuffer]
        FROM [#Demo07_ShreddedPartiesToTheDeadlocks];
    END;

    If ((@ResultSetsToReturn & 2) = 2)
    BEGIN
        --  Summarize the times at which the deadlocks occur.
        WITH [CTE_RoundedDeadlockTimes]
        AS (
            SELECT DateAdd(
                second,
                ((DateDiff(second, @ReferenceDate, [Process0_LastBatchStarted]) / @IntervalLength_seconds) * @IntervalLength_seconds),
                @ReferenceDate
            ) AS [RoundedDeadlockTime]
            FROM [#Demo07_ShreddedPartiesToTheDeadlocks]
        )
        SELECT [RoundedDeadlockTime],
            Count([RoundedDeadlockTime]) AS [DeadlockCount]
        FROM [CTE_RoundedDeadlockTimes]
        GROUP BY [RoundedDeadlockTime]
        ORDER BY [RoundedDeadlockTime] ASC;
    END;

    If ((@ResultSetsToReturn & 4) = 4)
    BEGIN
        --  Since the parties to the deadlock are doing one of two things:
        --      1.  Backing up the transaction log, or
        --      2.  a customer stored procedure that ultimately calls the 
        --          [msdb].[dbo].[sp_delete_backuphistory] system stored 
        --          procedure, 
        --  I wanted to see which operation typically survived the deadlock 
        --  intervention by SQL Server.
        --  NOTE: In order to maintain compatibility with SQL Server 2005, 
        --  I chose to use the Cast/Convert method to truncate the date, 
        --  instead of using the Date data type.
        WITH [CTE_ProcessOperations]
        AS (
            SELECT DateAdd(
                    second,
                    ((DateDiff(second, @ReferenceDate, [Process0_LastBatchStarted]) / @IntervalLength_seconds) * @IntervalLength_seconds),
                    @ReferenceDate
                ) AS [RoundedDeadlockTime],
                [Process0_SPID],
                [Process1_SPID],
                [Process2_SPID],
                (
                    Case
                        WHEN ([Process0_InputBuffer] IS NULL) THEN NULL
                        WHEN ([Process0_InputBuffer] LIKE N'BACKUP LOG%') THEN N'Backup log (log shipping)'
                        WHEN ([Process0_InputBuffer] LIKE N'%prCustomerJobSelector%') THEN N'Backup log (customer job)'
                        WHEN ([Process0_InputBuffer] LIKE N'%prCustomerBackupCleanUp%') THEN N'History cleanup job'
                        ELSE NULL
                    END
                ) AS [Process0_Operation],
                (
                    Case
                        WHEN ([Process1_InputBuffer] IS NULL) THEN NULL
                        WHEN ([Process1_InputBuffer] LIKE N'BACKUP LOG%') THEN N'Backup log (log shipping)'
                        WHEN ([Process1_InputBuffer] LIKE N'%prCustomerJobSelector%') THEN N'Backup log (customer job)'
                        WHEN ([Process1_InputBuffer] LIKE N'%prCustomerBackupCleanUp%') THEN N'History cleanup job'
                        ELSE NULL
                    END
                ) AS [Process1_Operation],
                (
                    Case
                        WHEN ([Process2_InputBuffer] IS NULL) THEN NULL
                        WHEN ([Process2_InputBuffer] LIKE N'BACKUP LOG%') THEN N'Backup log (log shipping)'
                        WHEN ([Process2_InputBuffer] LIKE N'%prCustomerJobSelector%') THEN N'Backup log (customer job)'
                        WHEN ([Process2_InputBuffer] LIKE N'%prCustomerBackupCleanUp%') THEN N'History cleanup job'
                        ELSE NULL
                    END
                ) AS [Process2_Operation],
                [Victim_SPID]
            FROM [#Demo07_ShreddedPartiesToTheDeadlocks]
        )
        SELECT [RoundedDeadlockTime],
            [Process0_Operation],
            [Process1_Operation],
            [Process2_Operation],
            [Victim_SPID],
            (
                Case [Victim_SPID]
                    WHEN [Process0_SPID] THEN [Process0_Operation]
                    WHEN [Process1_SPID] THEN [Process1_Operation]
                    WHEN [Process2_SPID] THEN [Process2_Operation]
                END
            ) AS [VictimOperation]
        FROM [CTE_ProcessOperations];
    END;

    If ((@ResultSetsToReturn & 8) = 8)
    BEGIN
        --  Summarize the resources that were the source of contention.
        SELECT [PageLock_DatabaseID],
            [PageLock_ObjectName],
            [ObjectLock_DatabaseID],
            [ObjectLock_ObjectName],
            Count(*) AS [DeadlockCount]
        FROM (
            SELECT DeadlockList.Graphs.value(N'(resource-list/pagelock/@dbid)[1]', N'Int') AS [PageLock_DatabaseID],
                DeadlockList.Graphs.value(N'(resource-list/pagelock/@objectname)[1]', N'SysName') AS [PageLock_ObjectName],
                DeadlockList.Graphs.value(N'(resource-list/objectlock/@dbid)[1]', N'Int') AS [ObjectLock_DatabaseID],
                DeadlockList.Graphs.value(N'(resource-list/objectlock/@objectname)[1]', N'SysName') AS [ObjectLock_ObjectName]
            FROM @DeadlockGraph.nodes(N'/deadlock-list/deadlock') AS DeadlockList(Graphs)
        ) AS [_DeadlockResources]
        GROUP BY [PageLock_DatabaseID],
            [PageLock_ObjectName],
            [ObjectLock_DatabaseID],
            [ObjectLock_ObjectName];
    END;
END TRY
BEGIN CATCH
    --  Output the error information.
    PRINT N'Error ' +
        Cast(Error_Number() AS NVarChar) +
        N': ' +
        Error_Message();
END CATCH;

GO

/*
    --  The temporary table will be automatically destroyed when the 
    --  connection is closed.  If you want to explicitly clean up from the 
    --  demonstration, you should run the following.
    DROP TABLE [#Demo07_ShreddedPartiesToTheDeadlocks];
*/
