Hi Mates,
Today I am going to share a script which would help you to load the deadlock information in to table.
In my environment we have vendor Application which is OLTP based database however it has some special considerations like on the most widely used tables they have more than 25 indexes and also we need to disable Lock Escalation on them.
By looking at this we can predict that this database is being prone to deadlocks and to help them in identifying I made use of below script which will load the information in to nicely formatted table. Schedule the script as a job on daily basis. At the very bottom of the script I am renaming the table to that particular date collection.
set quoted_identifier ON
if exists(select * from sys.tables where name='Deadlockcollection')
drop table Deadlockcollection
DECLARE @deadlock TABLE (
DeadlockID INT IDENTITY PRIMARY KEY CLUSTERED,
DeadlockGraph XML
);
-- use below to load a deadlock trace file
/*
DECLARE @file VARCHAR(500);
SELECT @file = REVERSE(SUBSTRING(REVERSE([PATH]), CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
FROM sys.traces
WHERE is_default = 1; -- get the system default trace, use different # for other active traces.
-- or just SET @file = 'your trace file to load';
INSERT INTO @deadlock (DeadlockGraph)
SELECT TextData
FROM ::FN_TRACE_GETTABLE(@file, DEFAULT)
WHERE TextData LIKE '<deadlock-list>%';
*/
-- or read in a deadlock file - doesn't have to have a "xdl" extension.
/*INSERT INTO @deadlock (DeadlockGraph)
SELECT *
FROM OPENROWSET(BULK 'I:\Ntirety\Alaska National Insurance Company\T20130724.0122.xdl', SINGLE_BLOB) UselessAlias;
*/
-- or read in the deadlock from the system_health XE file target
WITH cte1 AS
(
SELECT target_data = convert(XML, target_data)
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'collect-Deadlocks'
), cte2 AS
(
SELECT [FileName] = FileEvent.FileTarget.value('@name', 'varchar(1000)')
FROM cte1
CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent(FileTarget)
), cte3 AS
(
SELECT event_data = CONVERT(XML, t2.event_data)
FROM cte2
CROSS APPLY sys.fn_xe_file_target_read_file(cte2.[FileName], NULL, NULL, NULL) t2
WHERE t2.object_name = 'xml_deadlock_report'
)
INSERT INTO @deadlock(DeadlockGraph)
SELECT Deadlock = Deadlock.Report.query('.')
FROM cte3
CROSS APPLY cte3.event_data.nodes('//event/data/value/deadlock') Deadlock(Report);
WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health'
AND st.target_name = 'ring_buffer')
INSERT INTO @deadlock(DeadlockGraph)
SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'
/*
-- or read in the deadlock from SQL Sentry deadlock collection
INSERT INTO @deadlock(DeadlockGraph)
SELECT deadlockxml
FROM dbo.PerformanceAnalysisTraceDeadlock
*/
-- use below to load individual deadlocks.
INSERT INTO @deadlock VALUES ('');
-- Insert the deadlock XML in the above line!
-- Duplicate as necessary for additional graphs.
WITH CTE AS
(
SELECT DeadlockID,
DeadlockGraph
FROM @deadlock
), Victims AS
(
SELECT ID = Victims.List.value('@id', 'varchar(50)')
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('//deadlock/victim-list/victimProcess') AS Victims (List)
), Locks AS
(
-- Merge all of the lock information together.
SELECT CTE.DeadlockID,
MainLock.Process.value('@id', 'varchar(100)') AS LockID,
OwnerList.Owner.value('@id', 'varchar(200)') AS LockProcessId,
REPLACE(MainLock.Process.value('local-name(.)', 'varchar(100)'), 'lock', '') AS LockEvent,
MainLock.Process.value('@objectname', 'sysname') AS ObjectName,
OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode,
MainLock.Process.value('@dbid', 'INTEGER') AS Database_id,
MainLock.Process.value('@associatedObjectId', 'BIGINT') AS AssociatedObjectId,
MainLock.Process.value('@WaitType', 'varchar(100)') AS WaitType,
WaiterList.Owner.value('@id', 'varchar(200)') AS WaitProcessId,
WaiterList.Owner.value('@mode', 'varchar(10)') AS WaitMode
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('//deadlock/resource-list') AS Lock (list)
CROSS APPLY Lock.list.nodes('*') AS MainLock (Process)
OUTER APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList (Owner)
CROSS APPLY MainLock.Process.nodes('waiter-list/waiter') AS WaiterList (Owner)
), Process AS
(
-- get the data from the process node
SELECT CTE.DeadlockID,
[Victim] = CONVERT(BIT, CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = ISNULL(Deadlock.Process.value('../../@victim', 'varchar(50)'), v.ID)
THEN 1
ELSE 0
END),
[LockMode] = Deadlock.Process.value('@lockMode', 'varchar(10)'), -- how is this different from in the resource-list section?
[ProcessID] = Process.ID, --Deadlock.Process.value('@id', 'varchar(50)'),
[KPID] = Deadlock.Process.value('@kpid', 'int'), -- kernel-process id / thread ID number
[SPID] = Deadlock.Process.value('@spid', 'int'), -- system process id (connection to sql)
[SBID] = Deadlock.Process.value('@sbid', 'int'), -- system batch id / request_id (a query that a SPID is running)
[ECID] = Deadlock.Process.value('@ecid', 'int'), -- execution context ID (a worker thread running part of a query)
[IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)'),
[WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)'),
[LogUsed] = Deadlock.Process.value('@logused', 'int'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
[BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime'),
[InputBuffer] = Input.Buffer.query('.'),
CTE.[DeadlockGraph],
es.ExecutionStack,
[QueryStatement] = Execution.Frame.value('.', 'varchar(max)'),
ProcessQty = SUM(1) OVER (PARTITION BY CTE.DeadlockID),
TranCount = Deadlock.Process.value('@trancount', 'int')
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('//deadlock/process-list/process') AS Deadlock (Process)
CROSS APPLY (SELECT Deadlock.Process.value('@id', 'varchar(50)') ) AS Process (ID)
LEFT JOIN Victims v ON Process.ID = v.ID
CROSS APPLY Deadlock.Process.nodes('inputbuf') AS Input (Buffer)
CROSS APPLY Deadlock.Process.nodes('executionStack') AS Execution (Frame)
-- get the data from the executionStack node as XML
CROSS APPLY (SELECT ExecutionStack = (SELECT ProcNumber = ROW_NUMBER()
OVER (PARTITION BY CTE.DeadlockID,
Deadlock.Process.value('@id', 'varchar(50)'),
Execution.Stack.value('@procname', 'sysname'),
Execution.Stack.value('@code', 'varchar(MAX)')
ORDER BY (SELECT 1)),
ProcName = Execution.Stack.value('@procname', 'sysname'),
Line = Execution.Stack.value('@line', 'int'),
SQLHandle = Execution.Stack.value('@sqlhandle', 'varchar(64)'),
Code = LTRIM(RTRIM(Execution.Stack.value('.', 'varchar(MAX)')))
FROM Execution.Frame.nodes('frame') AS Execution (Stack)
ORDER BY ProcNumber
FOR XML PATH('frame'), ROOT('executionStack'), TYPE )
) es
)
-- get the columns in the desired order
SELECT p.DeadlockID,
p.Victim,
p.ProcessQty,
ProcessNbr = DENSE_RANK()
OVER (PARTITION BY p.DeadlockId
ORDER BY p.ProcessID),
p.LockMode,
LockedObject = NULLIF(l.ObjectName, ''),
l.database_id,
l.AssociatedObjectId,
LockProcess = p.ProcessID,
p.KPID,
p.SPID,
p.SBID,
p.ECID,
p.TranCount,
l.LockEvent,
LockedMode = l.LockMode,
l.WaitProcessID,
l.WaitMode,
p.WaitResource,
l.WaitType,
p.IsolationLevel,
p.LogUsed,
p.ClientApp,
p.HostName,
p.LoginName,
p.TransactionTime,
p.BatchStarted,
p.BatchCompleted,
p.QueryStatement,
p.InputBuffer,
p.DeadlockGraph,
p.ExecutionStack
INTO [DeadlockCollection]
FROM Process p
LEFT JOIN Locks l
ON p.DeadlockID = l.DeadlockID
AND p.ProcessID = l.LockProcessID
ORDER BY p.DeadlockId,
p.Victim DESC,
p.ProcessId
DECLARE @Value varchar(500)
SET @Value='DeadlockCollection' +'-'+(CONVERT(VARCHAR(10), getdate(), 102))
EXEC sp_rename 'DeadlockCollection', @Value
Here is the snip of the output
Comments