可以從 SQL Server 2008 中的環形緩衝區中提取的死鎖 xml 創建圖表嗎？

在更高版本的 SQL_Server 中，我們在對象瀏覽器中查看事件文件以查看死鎖圖。此特定客戶端具有 SQL 2008（兼容性 2005），並且已使用以下腳本從環形緩衝區中提取了唯一的死鎖副本：

SELECT
  xed.value('@timestamp', 'datetime2(3)') as CreationDate,
  xed.query('.') AS XEvent
FROM
(
  SELECT CAST([target_data] AS XML) AS TargetData
  FROM sys.dm_xe_session_targets AS st
  INNER JOIN sys.dm_xe_sessions AS s
     ON s.address = st.event_session_address
  WHERE s.name = N'system_health'
        AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC

該格式與我們的 xdl 查看器不兼容。有沒有辦法從下面返回的 XML 中獲取圖表？：

<event name="xml_deadlock_report" package="sqlserver" id="123" version="1" timestamp="2018-07-04T00:15:14.667Z">
 <data name="xml_report">
   <type name="unicode_string" package="package0" />
   <value><deadlock>
<victim-list>
 <victimProcess id="process3cf048"/>
</victim-list>
<process-list>
 <process id="process3cf048" taskpriority="0" logused="0" waitresource="PAGE: 11:1:45050737" waittime="3573" ownerId="19491756" transactionname="user_transaction" lasttranstarted="2018-07-04T01:14:58.003" XDES="0x31b45b400" lockMode="S" schedulerid="4" kpid="2280" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-04T01:05:29.587" lastbatchcompleted="2018-07-04T01:05:29.587" clientapp="SQLAgent - TSQL JobStep (Job 0x3404686D1D7A2F478F69577F5EEBAE41 : Step 1)" hostname="ae-es" hostpid="5340" loginname="ae\rmsSYSTEM" isolationlevel="read committed (2)" xactid="19491756" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
  <executionStack>
   <frame procname="" line="2177" stmtstart="149730" stmtend="150314" sqlhandle="0x03000b00389b050021b69200bca300000100000000000000">
   </frame>
   <frame procname="" line="92" stmtstart="8170" stmtend="9080" sqlhandle="0x03000b00dea9f7571cb80601e6a300000100000000000000">
   </frame>
   <frame procname="" line="53" stmtstart="4514" stmtend="5792" sqlhandle="0x03000b00333d1b553bc7b200d3a300000100000000000000">
   </frame>
   <frame procname="" line="2" stmtstart="6" sqlhandle="0x01000b00b5d2e138601f5208040000000000000000000000">
   </frame>
  </executionStack>
  <inputbuf>

exec EBB_PSF_ImportSales 'G:\progra~1\es\winrms\import'   </inputbuf>
 </process>
 <process id="process4483b88" taskpriority="0" logused="6076" waitresource="PAGE: 11:1:132712" waittime="11747" ownerId="19493225" transactionname="user_transaction" lasttranstarted="2018-07-04T01:15:03.157" XDES="0x1344f8cc0" lockMode="S" schedulerid="6" kpid="5948" status="suspended" spid="64" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2018-07-04T01:15:03.157" lastbatchcompleted="2018-07-04T01:15:03.153" lastattention="2018-07-03T23:45:37.360" clientapp="ECommerce WebService" hostname="ae-es" hostpid="3472" loginname="rmsSYSTEM" isolationlevel="read committed (2)" xactid="19493225" currentdb="11" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
  <executionStack>
   <frame procname="" line="359" stmtstart="36280" stmtend="39240" sqlhandle="0x03000b00bdc1b416537f2601dfa700000100000000000000">
   </frame>
   <frame procname="" line="79" stmtstart="4356" stmtend="4452" sqlhandle="0x03000b00f6e5a817d68c2601dfa700000100000000000000">
   </frame>
   <frame procname="" line="1" stmtstart="98" sqlhandle="0x01000b00c20b1a2dd036be87000000000000000000000000">
   </frame>
   <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
   </frame>
  </executionStack>
  <inputbuf>

(@p0 xml,@p1 xml output,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[EBB_PSFXML_Process] @xml = @p0, @xml_out = @p1 OUTPUT   </inputbuf>
 </process>
</process-list>
<resource-list>
 <pagelock fileid="1" pageid="45050737" dbid="11" objectname="" id="lockdb734b80" mode="IX" associatedObjectId="72057595923464192">
  <owner-list>
   <owner id="process4483b88" mode="IX"/>
  </owner-list>
  <waiter-list>
   <waiter id="process3cf048" mode="S" requestType="wait"/>
  </waiter-list>
 </pagelock>
 <pagelock fileid="1" pageid="132712" dbid="11" objectname="" id="lock80131400" mode="IX" associatedObjectId="72057594217758720">
  <owner-list>
   <owner id="process3cf048" mode="IX"/>
  </owner-list>
  <waiter-list>
   <waiter id="process4483b88" mode="S" requestType="wait"/>
  </waiter-list>
 </pagelock>
</resource-list>
</deadlock>
</value>
   <text />
 </data>
</event>

是的。那是嵌入在 XML 中的 XML，因此您需要使用如下查詢來提取和解除死鎖 XML：

select cast(@doc.value('(/event/data/value/.)[1]', 'nvarchar(max)') as xml) deadlock

然後在 SSMS 中打開 XML 結果並使用 .xdl 副檔名保存文件。然後用 SSMS 重新打開它，你會看到死鎖圖。

感謝 David Browne 的回答，以下語句返回格式正確的 XML：

SELECT
  xed.value('@timestamp', 'datetime2(3)') as CreationDate,
  cast(XEventData.xed.value('(data/value)[1]', 'nvarchar(max)') as xml) as DeadlockGraph2  
FROM
(
  SELECT CAST([target_data] AS XML) AS TargetData
  FROM sys.dm_xe_session_targets AS st
  INNER JOIN sys.dm_xe_sessions AS s
     ON s.address = st.event_session_address
  WHERE s.name = N'system_health'
        AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC

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