快速 XML，慢速 XML

我們的數據庫更新 Windows 應用程序需要在兩個數據庫之間傳輸一些數據，作為某個一次性更新過程的一部分。我選擇 XML 作為媒介來移動數據。

該過程通過從源中選擇一大塊行作為 XML 來工作，這些行通過應用程序傳遞到目標伺服器，在那裡它被分解成一個全域臨時表。（源數據庫和目標數據庫可以在 2 個不同的實例上。）重複該過程，直到所需的所有數據都在目標實例的臨時表中。最後，將臨時表記錄合併到實際的目標數據庫表中。

我們遇到的問題是，在某些情況下，第二個塊非常慢，CPU 使用率非常高，而且它無處可去。我們能夠在我們的託管環境中重現該問題，但不能在開發或 QA 中重現。我們的一些客戶也遇到了這個問題——其中一個客戶讓它執行了一夜，最後在執行了 18（！）小時後在第二天早上將其殺死。在那種情況下，我不確定它走了多遠。等待約 2 小時後，我無法通過託管中的第二塊。

這是第一個塊的語句批處理：

SET NOCOUNT ON;

DECLARE @src xml;
SET @src = CAST(@P1 AS xml);

SELECT
   n.x.value(N'field1[1]', 'uniqueidentifier') AS field1,
   n.x.value(N'field2[1]', 'smallint') AS field2,
   ... (8 more fields of various types) ...
   INTO [##target_2994] /*******/
   FROM @src.nodes('Rows[1]/Row') n(x);

這是第二個和後續塊的批次，這就是問題所在：

SET NOCOUNT ON;

DECLARE @src xml;
SET @src = CAST(@P1 AS xml);

INSERT INTO [dbo].[##target_2994] /*******/
   SELECT
       n.x.value(N'field1[1]', 'uniqueidentifier') AS field1,
       n.x.value(N'field2[1]', 'smallint') AS field2,
       ... (8 more fields of various types) ...
       FROM @src.nodes('Rows[1]/Row') n(x);

這是我到目前為止所看到的：

• 這不是一個阻塞問題：等待統計資訊是聲明中的 99 SOS_SCHEDULER_YIELD% INSERT。
• sys.dm_io_virtual_file_stats在目標上tempdb顯示它基本上是空閒的，所以這不是 I/O 問題。
• 所有數據都只有固定寬度的列，因此沒有大量長文本欄位。
• 數據塊大小目前為 25,000 行，我們可能會降低這個值，但這並不能解釋差異，因為我們已經用一些相同的數據集進行了測試，效果很好。需要傳輸的最大表約為 725,000 行，測試結果很好。
• 查詢計劃在問題與沒有問題之間是相同的*（我對 XML 進行了文件比較）。
• 問題與無問題之間的會話SET選項相同。
• 版本似乎不是一個因素：託管是 2008 R2 SP1 Enterprise x64；我們已經在 2005 SP4+ Standard x64 一直到 2008 R2 SP1+ Developer x86 進行了測試，沒有出現任何問題。有問題的客戶是 2008 RTM/SP1 Standard/Enterprise x64（到目前為止）。
• 虛擬化似乎不是一個因素：託管和 QA 是虛擬化的；dev 是部分虛擬化的；有問題的客戶是身體上的。MAXDOP沒有為我們的任何伺服器設置（最大 = 4 個邏輯處理器）；我不確定客戶的設置。
• 兩個數據庫在同一台伺服器上與不同的伺服器上沒有區別。
• 在 TS 盒上執行更新應用程序與在本地執行沒有區別。
• Tempdb 數據庫設置是相同的。
• 實例和數據庫排序規則是相同的。
• 將目標伺服器上 tempdb 的兼容級別更改為 90 並沒有幫助。（根據馬克的回答）
• 沒有顯著的實例配置差異。（根據金博的回答）

任何人都可以建議其他的東西看嗎？

• 計算表達式的名稱不同，其中一個估計行大小的差異 < 1%，但其他一切都相同，包括總成本估計。

對於您遇到問題的版本，在連接上記錄了一個類似的問題 -使用 XML.nodes() 的 INSERT 語句在 SQL2008 SP1 中非常非常慢。

使用SQL2008，當使用節點查詢粉碎XML時，僅SELECTing時性能很好。但是，當您想將數據插入表/臨時表/表變數時，性能會變得很差。

使用 RedGate 中的 SQL 比較之類的東西比較數據庫

使用 Redgate 的 SQL 數據比較之類的方法比較表中的數據

如果架構相同且數據差異不顯著，則比較 SQL 實例屬性。

如果差異不顯著，請查看您的臨時文件使用情況。

您正在插入全域表 - 嘗試插入到您創建的實際表中。

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