Sql-Server

使用 XML 閱讀器優化計劃

  • November 8, 2019

從此處執行查詢以將死鎖事件拉出預設擴展事件會話

SELECT CAST (
   REPLACE (
       REPLACE (
           XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
           '<victim-list>', '<deadlock><victim-list>'),
       '<process-list>', '</victim-list><process-list>')
   AS XML) AS DeadlockGraph
FROM (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') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
   WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

在我的機器上完成大約需要 20 分鐘。報告的統計數據是

Table 'Worktable'. Scan count 0, logical reads 68121, physical reads 0, read-ahead reads 0, 
        lob logical reads 25674576, lob physical reads 0, lob read-ahead reads 4332386.

SQL Server Execution Times:
  CPU time = 1241269 ms,  elapsed time = 1244082 ms.

慢計劃 XML

平行線

如果我刪除該WHERE子句，它將在不到一秒的時間內完成，返回 3,782 行。

同樣，如果我添加OPTION (MAXDOP 1)到原始查詢中也可以加快速度，現在統計資訊顯示大量更少的 lob 讀取。

Table 'Worktable'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0,
               lob logical reads 6767, lob physical reads 0, lob read-ahead reads 6076.

SQL Server Execution Times:
  CPU time = 639 ms,  elapsed time = 693 ms.

更快的計劃 XML

串列

所以我的問題是

誰能解釋發生了什麼？為什麼最初的計劃如此災難性地更糟，有沒有可靠的方法來避免這個問題？

添加：

我還發現更改查詢以INNER HASH JOIN在一定程度上改善事情（但仍然需要> 3分鐘），因為 DMV 結果是如此之小，我懷疑 Join 類型本身是負責的，並假設其他東西必須改變。統計數據

Table 'Worktable'. Scan count 0, logical reads 30294, physical reads 0, read-ahead reads 0, 
         lob logical reads 10741863, lob physical reads 0, lob read-ahead reads 4361042.

SQL Server Execution Times:
  CPU time = 200914 ms,  elapsed time = 203614 ms.

（和計劃）

在填充了擴展事件環形緩衝區（DATALENGTH其中XML4,880,045 字節，其中包含 1,448 個事件。）並測試了原始查詢的縮減版本（有和沒有MAXDOP提示）。

SELECT COUNT(*)
FROM   (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') AS Data
       CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE  XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

SELECT*
FROM   sys.dm_db_task_space_usage
WHERE  session_id = @@SPID 

給出了以下結果

+-------------------------------------+------+----------+
|                                     | Fast |   Slow   |
+-------------------------------------+------+----------+
| internal_objects_alloc_page_count   |  616 |  1761272 |
| internal_objects_dealloc_page_count |  616 |  1761272 |
| elapsed time (ms)                   |  428 |   398481 |
| lob logical reads                   | 8390 | 12784196 |
+-------------------------------------+------+----------+

616tempdb 分配與顯示頁面已分配和釋放的更快的分配有明顯差異。這與將 XML 放入變數時使用的頁面數量相同。

對於慢速計劃，這些頁面分配計數達到數百萬。在查詢執行時進行輪詢dm_db_task_space_usage顯示，它似乎在不斷地分配和釋放頁面，tempdb任何時候都分配了 1,800 到 3,000 個頁面。

性能差異的原因在於標量表達式在執行引擎中的處理方式。在這種情況下，感興趣的表達是：

[Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0)

此表達式標籤由計算標量運算符定義（串列計劃中的節點 11，並行計劃中的節點 13）。計算標量運算符與其他運算符（SQL Server 2005 及更高版本）的不同之處在於它們定義的表達式不一定在它們出現在可見執行計劃中的位置進行計算；評估可以推遲到後面的運算符需要計算結果為止。

在目前的查詢中，target_data字元串通常很大，使得從字元串到XML昂貴的轉換。XML在慢速計劃中，每次需要結果的後續運算符Expr1000反彈時，都會執行要轉換的字元串。

當相關參數（外部引用）更改時，重新綁定發生在嵌套循環連接的內側。Expr1000是此執行計劃中大多數嵌套循環連接的外部引用。該表達式被多個 XML 讀取器（包括 Stream Aggregates 和啟動過濾器）多次引用。根據 的大小，XML字元串轉換為的次數XML很容易達到數百萬。

下面的呼叫堆棧顯示了target_data字元串被轉換為XML（ConvertStringToXMLForES- 其中 ES 是表達式服務）的範例：

啟動過濾器

啟動過濾器呼叫堆棧

XML 閱讀器（內部 TVF 流）

TVF 流呼叫堆棧

流聚合

流聚合呼叫堆棧

將字元串轉換為XML每次這些運算符重新綁定時都解釋了使用嵌套循環計劃觀察到的性能差異。這與是否使用並行性無關。恰巧優化器在MAXDOP 1指定提示時選擇了雜湊連接。如果MAXDOP 1, LOOP JOIN指定，則性能很差，就像預設的並行計劃（優化器選擇嵌套循環）一樣。

雜湊連接能提高多少性能取決於是Expr1000出現在操作符的建構端還是探測端。以下查詢在探測端定位表達式：

SELECT CAST (
   REPLACE (
       REPLACE (
           XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
           '<victim-list>', '<deadlock><victim-list>'),
       '<process-list>', '</victim-list><process-list>')
   AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
   FROM sys.dm_xe_sessions s
   INNER HASH JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
   WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

我已經從問題中顯示的版本中顛倒了連接的書面順序，因為連接提示（INNER HASH JOIN上面）也強制整個查詢的順序，就像FORCE ORDER已經指定一樣。反轉是必要的，以確保Expr1000出現在探頭側。執行計劃的有趣部分是：

提示1

使用在探測端定義的表達式，值被記憶體：

雜湊記憶體

的評估Expr1000仍然推遲到第一個運算符需要該值（上面的堆棧跟踪中的啟動過濾器），但計算的值被記憶體（CValHashCachedSwitch）並重用於 XML 讀取器和流聚合的後續呼叫。下面的堆棧跟踪顯示了 XML 閱讀器重用記憶體值的範例。

記憶體重用

當強制連接順序使得定義Expr1000發生在雜湊連接的建構端時，情況就不同了：

SELECT CAST (
   REPLACE (
       REPLACE (
           XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
           '<victim-list>', '<deadlock><victim-list>'),
       '<process-list>', '</victim-list><process-list>')
   AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
   FROM sys.dm_xe_session_targets st 
   INNER HASH JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
   WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

雜湊 2

散列連接在開始探測匹配之前完全讀取其建構輸入以構造散列表。因此，我們必須儲存所有值，而不僅僅是從計劃的探測端處理的每個執行緒的值。因此，散列連接使用tempdb工作表來儲存XML數據，並且以後的操作員每次訪問結果都Expr1000需要昂貴的訪問tempdb：

訪問速度慢

下面顯示了慢速訪問路徑的更多細節：

慢細節

如果強制合併連接，則輸入行被排序（阻塞操作，就像雜湊連接的建構輸入）導致類似的安排，tempdb由於數據的大小，需要通過排序優化的工作表進行慢速訪問。

由於執行計劃中不明顯的各種原因，操作大型數據項的計劃可能會出現問題。使用散列連接（使用正確輸入上的表達式）不是一個好的解決方案。它依賴於未記錄的內部行為，不能保證下週它會以同樣的方式工作，或者在稍微不同的查詢上工作。

資訊是，XML今天的操縱可能是難以優化的事情。在粉碎之前將其寫入XML變數或臨時表是比上面顯示的任何東西更可靠的解決方法。一種方法是：

DECLARE @data xml =
       CONVERT
       (
           xml,
           (
           SELECT TOP (1)
               dxst.target_data
           FROM sys.dm_xe_sessions AS dxs 
           JOIN sys.dm_xe_session_targets AS dxst ON
               dxst.event_session_address = dxs.[address]
           WHERE 
               dxs.name = N'system_health'
               AND dxst.target_name = N'ring_buffer'
           )
       )

SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM @data.nodes ('./RingBufferTarget/event[@name eq "xml_deadlock_report"]') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

最後，我只想從下面的評論中添加馬丁非常漂亮的圖形：

馬丁的圖形

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

相關問答

Sql-Server

裝配中功能的執行時間/成本

  • November 29, 2021
檢視問答
Sql-Server

當 where 子句過濾 value() 時，為什麼不使用二級選擇性索引？

  • October 23, 2019
檢視問答
Sql-Server

使用探測殘差辨識執行計劃

  • August 15, 2019
檢視問答
Sql-Server

這個索引掃描中這個 Uniq1002 列的目的是什麼？

  • June 1, 2019
檢視問答
Sql-Server

執行計劃中的點陣圖創建導致聚集索引掃描的錯誤估計

  • January 28, 2022
檢視問答
Sql-Server

如何提示遠端掃描操作員估計超過 10000 行？

  • June 4, 2021
檢視問答