為什麼死鎖圖中沒有填充對象名和索引名？

我收到了檢查 2008 sp3 伺服器（10.0.5500.0 - 企業版（64 位））上的一些錯誤的請求，看看我是否能找到之前發生的死鎖。

Traceflag 1222 未啟用，伺服器所有者也不想啟用它。我使用了擴展事件並使用以下查詢提取了一些資訊：

   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 )

我收到了很多類似這樣的資訊：

<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>

如果 objectname="" indexname="" ，我如何找出死鎖是什麼？

我無法回答為什麼缺少資訊，但我可以指出如何提取它。

首先，移動到該數據庫：

SELECT DB_NAME(25);

拿那個結果，然後：

USE <db_name>;

現在找出這些 hobt_id 代表什麼：

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

您還可以使用以下方法找到所涉及的批次（如果它們仍然存在）：

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

偏移量在那裡，ExecutionStack因此您可以根據需要更深入地研究該輸出（許多線上使用偏移量的範例）。

編輯Sandwiches 先生指出了以下 Connect 項（不再可用），Microsoft 已聲明此錯誤已在 SQL Server 2012 中修復。它不會在 2008/R2 中修復。

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

引用自：https://dba.stackexchange.com/questions/28996