none
Decipher waitresource RRS feed

  • Question

  • I am trying to analyze a dead lock trace.

    I see this in the waitresource

    waitresource=KEY: 9:72057594065256448 (ef008dce9cbe)

    How can I find the exact key thats causing the problem? Thanks!

    Friday, February 16, 2007 3:06 PM

All replies

  • Look at the Trace Flag 1222 Example in SQL Server 2005 Books Online topic:
     
    Detecting and Ending Deadlocks
    http://msdn2.microsoft.com/en-us/library/ms178104.aspx

    For example:

    waitresource=KEY: 6:72057594057457664
    dbid=6 objectname=AdventureWorks.dbo.T2
    associatedObjectId=72057594057457664
    keylock hobtid=72057594057457664

    Saturday, February 17, 2007 7:55 AM
  • I think that is a hobt_id. You find it through sys.partitions:

    SELECT o.name, i.name
    FROM sys.partitions p
    JOIN sys.objects o ON p.object_id = o.object_id
    JOIN sys.indexes i ON p.object_id = i.object_id
    AND p.index_id = i.index_id
    WHERE p.hobt_id = 72057594065256448

     

    Monday, December 1, 2008 11:50 PM
  • I was looking for the same thing. I had to roll my own from the output of few different sites.

    declare @databaseName varchar(100) = 'Your Database'--DatabaseName
    declare @keyValue varchar(100) = 'KEY: 10:72057600953614336 (0d0120c75d83)'--Output from deadlock diagram
    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

    Friday, August 16, 2013 3:29 PM