--------------------------------------------------------------------------------
--  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 a customer's log shipping jobs.
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   3.0006.0090.0053 (2010-11-17)
--------------------------------------------------------------------------------

USE [tempdb];

GO

DECLARE @DeadlockCount Int;
DECLARE @DeadlockGraph XML;
DECLARE @DeadlockGraphFileName NVarChar(255);
DECLARE @DynamicSQL NVarChar(1024);
DECLARE @ResultSetsToReturn Int;

DECLARE @PartiesToTheDeadlocks Table
(
	[Victim_SPID] Int,
    [Process0_SPID] Int,
    [Process1_SPID] Int,
    [Process2_SPID] Int,
    [Process0_LastBatchStarted] DateTime,
    [Process1_LastBatchStarted] DateTime,
    [Process2_LastBatchStarted] DateTime,
    [Process0_InputBuffer] NVarChar(2048),
    [Process1_InputBuffer] NVarChar(2048),
    [Process2_InputBuffer] NVarChar(2048)
);

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

    --  Use a value of 7 for the complete demonstration to run.
	SET @ResultSetsToReturn = 1;

	--	The OpenRowset() T-SQL function requires a string literal for the file 
	--	name, so use dynamic SQL to avoid hardcoding a path (other than in the 
	--	above assignment to the @DeadlockGraphFileName variable... but that's 
	--	a little clearer).
	SET @DynamicSQL = N'SELECT @XMLDocument = Cast([BulkColumn] AS XML)' +
		NChar(10) +
		N'FROM OpenRowset' +
		NChar(10) +
		N'(' +
		NChar(10) +
		N'    BULK ''' +
		@DeadlockGraphFileName +
		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('count(deadlock)', 'Int')
	FROM @DeadlockGraph.nodes('/deadlock-list') AS DeadlockList(Graphs);

	PRINT 'File            :   ' +
		@DeadlockGraphFileName +
		Char(10) +
		'Deadlock count  :   ' +
		Cast(@DeadlockCount AS VarChar);

    --  Extract the parties to the deadlock.
    INSERT INTO @PartiesToTheDeadlocks
	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('@victim', 'NVarChar(15)') = DeadlockList.Graphs.value('(process-list/process[1]/@id)[1]', 'NVarChar(15)')) THEN DeadlockList.Graphs.value('(process-list/process[1]/@spid)[1]', 'Int')
					WHEN (DeadlockList.Graphs.value('@victim', 'NVarChar(15)') = DeadlockList.Graphs.value('(process-list/process[2]/@id)[1]', 'NVarChar(15)')) THEN DeadlockList.Graphs.value('(process-list/process[2]/@spid)[1]', 'Int')
					WHEN (DeadlockList.Graphs.value('@victim', 'NVarChar(15)') = DeadlockList.Graphs.value('(process-list/process[3]/@id)[1]', 'NVarChar(15)')) THEN DeadlockList.Graphs.value('(process-list/process[3]/@spid)[1]', 'Int')
					ELSE NULL
				END
			) AS [Victim_SPID],
			DeadlockList.Graphs.value('(process-list/process[1]/@spid)[1]', 'Int') AS [Process0_SPID],
			DeadlockList.Graphs.value('(process-list/process[2]/@spid)[1]', 'Int') AS [Process1_SPID],
			DeadlockList.Graphs.value('(process-list/process[3]/@spid)[1]', 'Int') AS [Process2_SPID],
			Cast(Replace(DeadlockList.Graphs.value('(process-list/process[1]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DateTime) AS [Process0_LastBatchStarted],
			Cast(Replace(DeadlockList.Graphs.value('(process-list/process[2]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DateTime) AS [Process1_LastBatchStarted],
			Cast(Replace(DeadlockList.Graphs.value('(process-list/process[3]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DateTime) AS [Process2_LastBatchStarted],
			RTrim(LTrim(Replace(DeadlockList.Graphs.value('(process-list/process[1]/inputbuf)[1]', 'NVarChar(2048)'), NChar(10), N''))) AS [Process0_InputBuffer],
			RTrim(LTrim(Replace(DeadlockList.Graphs.value('(process-list/process[2]/inputbuf)[1]', 'NVarChar(2048)'), NChar(10), N''))) AS [Process1_InputBuffer],
			RTrim(LTrim(Replace(DeadlockList.Graphs.value('(process-list/process[3]/inputbuf)[1]', 'NVarChar(2048)'), NChar(10), N''))) AS [Process2_InputBuffer]
		FROM @DeadlockGraph.nodes('/deadlock-list/deadlock') AS DeadlockList(Graphs)
	) AS [_ProcessDetails];

	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 @PartiesToTheDeadlocks;
	END;

	If ((@ResultSetsToReturn & 2) = 2)
	BEGIN
		--	Since the parties to the deadlock are doing one of two things:
		--		1.  BACKUP 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.
		SELECT Cast(Convert(VarChar, [Process0_LastBatchStarted], 112) AS DateTime) AS [Day],
			[Victim_SPID],
			(
				Case
					WHEN ([Process0_InputBuffer] LIKE N'%prCustomer%') THEN [Process0_SPID]
					WHEN ([Process1_InputBuffer] LIKE N'%prCustomer%') THEN [Process1_SPID]
					WHEN ([Process2_InputBuffer] LIKE N'%prCustomer%') THEN [Process2_SPID]
					ELSE NULL
				END
			) AS [ProcessDeletingBackupHistory_SPID],
			(
				Case WHEN [Victim_SPID] = (
						Case
							WHEN ([Process0_InputBuffer] LIKE N'%prCustomer%') THEN [Process0_SPID]
							WHEN ([Process1_InputBuffer] LIKE N'%prCustomer%') THEN [Process1_SPID]
							WHEN ([Process2_InputBuffer] LIKE N'%prCustomer%') THEN [Process2_SPID]
							ELSE NULL
						END
					) THEN 'Yes'
					ELSE 'No'
				END
			) AS [ProcessDeletingBackupHistory_Killed]
		FROM @PartiesToTheDeadlocks;
	END;

	If ((@ResultSetsToReturn & 4) = 4)
	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('(resource-list/pagelock/@dbid)[1]', 'Int') AS [PageLock_DatabaseID],
				DeadlockList.Graphs.value('(resource-list/pagelock/@objectname)[1]', 'SysName') AS [PageLock_ObjectName],
				DeadlockList.Graphs.value('(resource-list/objectlock/@dbid)[1]', 'Int') AS [ObjectLock_DatabaseID],
				DeadlockList.Graphs.value('(resource-list/objectlock/@objectname)[1]', 'SysName') AS [ObjectLock_ObjectName]
			FROM @DeadlockGraph.nodes('/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 VarChar) +
        N': ' +
        Error_Message();
END CATCH;

GO
