SQL Server – Why ObjectName and IndexName Aren’t Populated in Deadlock Graph

deadlocksql serversql-server-2008

I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 – Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier.

Traceflag 1222 wasn't enabled and the server owners did not want to enable it either. I used extended events and pulled out some info using the following query:

    SELECT  CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML 
                      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 target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes 
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                                     event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS tab ( event_data )

I received a lot of info back that looked like this:

<deadlock>
  <victim-list>
    <victimProcess id="process14846ccbc8" />
  </victim-list>
  <process-list>
    <process id="process14846ccbc8" taskpriority="0" logused="0" waitresource="KEY: 25:72057594099728384 (0800dfc12261)" waittime="2842" ownerId="7035766745" transactionname="UPDATE" lasttranstarted="2012-11-20T07:50:25.023" XDES="0x14d9ade3b0" lockMode="U" schedulerid="8" kpid="17268" status="background" spid="19" sbid="0" ecid="0" priority="0" trancount="2">
      <executionStack>
        <frame procname="" line="53" stmtstart="2674" stmtend="2980" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
    <process id="process3d9bdc8" taskpriority="0" logused="224" waitresource="KEY: 25:72057594099793920 (2f00faaf5d8e)" waittime="2315" ownerId="7035765940" transactionname="user_transaction" lasttranstarted="2012-11-20T07:50:24.913" XDES="0xdad541970" lockMode="U" schedulerid="6" kpid="15448" status="background" spid="23" sbid="0" ecid="0" priority="0" trancount="2">
      <executionStack>
        <frame procname="" line="549" stmtstart="32736" stmtend="33274" sqlhandle="0x03001900b9667f2bc41ade003ea000000100000000000000" />
        <frame procname="" line="549" stmtstart="34038" stmtend="35374" sqlhandle="0x03001900f28a732c051bde003ea000000100000000000000" />
        <frame procname="" line="81" stmtstart="4284" stmtend="4392" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594099728384" dbid="25" objectname="" indexname="" id="lock6b6a4ba00" mode="X" associatedObjectId="72057594099728384">
      <owner-list>
        <owner id="process3d9bdc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process14846ccbc8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594099793920" dbid="25" objectname="" indexname="" id="lock74edcf180" mode="U" associatedObjectId="72057594099793920">
      <owner-list>
        <owner id="process14846ccbc8" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process3d9bdc8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

How can I found out what the deadlocks are if objectname="" indexname="" ?

Best Answer

I can't answer as to why there is information missing, but I can point out how to extract it.

First, move to that database:

SELECT DB_NAME(25);

Take that result and:

USE <db_name>;

Now find out what these hobt_ids represent:

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) 
FROM sys.partitions 
WHERE hobt_id IN (72057594099728384, 72057594099793920);

You can also find the batches involved (if they're still around) using:

SELECT * FROM sys.dm_exec_sql_text(0x03001900b9667f2bc41ade003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x03001900f28a732c051bde003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x030019002cf5fc35c2449200d2a000000100000000000000);

The offsets are there in the ExecutionStack so you can drill deeper into that output if you like (many examples of using offsets online).

EDIT Mr. Sandwiches pointed out the following Connect item (no longer available), where Microsoft has stated that this bug is fixed in SQL Server 2012. It won't be fixed in 2008/R2.

http://connect.microsoft.com/SQLServer/feedback/details/635391/objectname-indexname-not-populated-in-deadlock-graph-in-sql2008-sp1-and-sql2008-r2

Related Question