Friday, January 31, 2014

Historical Deadlock Detection

create table #DeadlockEvents(AlertTime datetime2,DeadlockGraph  xml)

SET QUOTED_IDENTIFIER ON ;
;WITH ring_buffer ( xmlcol )
AS (
SELECT CONVERT(XML, st.target_data) as ring_buffer
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets st ON st.event_session_address = s.[address]
)
INSERT INTO dbo.#DeadlockEvents ( AlertTime, DeadlockGraph )
SELECT CAST(T2.evntdata.value('(@timestamp)[1]','varchar(24)') AS datetime2) AS [AlertTime]
,CAST(T2.evntdata.value('(data[@name="xml_report"]/value)[1]','varchar(max)') AS XML) AS deadlock_report
FROM ring_buffer
CROSS APPLY xmlcol.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') as T2(evntdata) ;



;with cte as (
select
T2.deadlockData.value('(../victim-list/victimProcess/@id)[1]','varchar(100)')  as ProcessID,
CAST(T2.deadlockData.value('(process/@lasttranstarted)[1]','varchar(24)') AS datetime2) as lasttranstarted ,
T2.deadlockData.value('(process/inputbuf)[1]','varchar(max)') as inputbuffer1,
T2.deadlockData.value('(process/inputbuf)[2]','varchar(max)') as inputbuffer2,
T2.deadlockData.value('(process/inputbuf)[3]','varchar(max)') as inputbuffer3,
x.DeadlockGraph
from #DeadlockEvents  x
cross apply x.DeadlockGraph.nodes('/deadlock/process-list') as T2(deadlockData)
)
select * From cte
order by lasttranstarted desc

/*
find the wait resource
*/
declare @databaseName varchar(100) = db_name(12)
declare @keyValue varchar(100) = 'KEY: 12:72057594344112128 (bb6543c4f003)'--Output from deadlock graph
declare @lockres varchar(100)
declare @hobbitID bigint
select @hobbitID = convert(bigint,RTRIM(SUBSTRING(@keyValue,CHARINDEX(':',@keyValue,CHARINDEX(':',@keyValue)+1)+1,
CHARINDEX('(',@keyValue)-CHARINDEX(':',@keyValue,CHARINDEX(':',@keyValue)+1)-1)))
select @lockRes = RTRIM(SUBSTRING(@keyValue,CHARINDEX('(',@keyValue)+1,CHARINDEX(')',@keyValue)-CHARINDEX('(',@keyValue)-1))


declare @objectName sysname
declare @ObjectLookupSQL as nvarchar(max) = '
SELECT @objectName = o.name
FROM '+@databaseName+'.sys.partitions p
JOIN '+@databaseName+'.sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id]
join '+@databaseName+'.sys.objects o on o.object_id = i.object_id
WHERE hobt_id = '+convert(nvarchar(50),@hobbitID)+'
'
exec sp_executesql @ObjectLookupSQL, N'@objectName sysname OUTPUT',@objectName=@objectName OUTPUT
select @objectName
declare @finalResult nvarchar(max) = N'select %%lockres%% ,*
from '+@databaseName+'.dbo.' + @objectName + '
where %%lockres%% = ''('+@lockRes+')''
'
exec sp_executesql @finalResult

select object_name(11424)

No comments:

Post a Comment