為什麼我的並行查詢有時會處理數十億額外的行？

April 10, 2017

我正在執行一些數據準備查詢，當時我注意到其中一個查詢的執行時間有時比預期的要長得多。查詢應該在幾秒鐘內完成，但有時需要幾分鐘才能執行。這是查詢：
CREATE TABLE dbo.X_HEAP (ID INT NOT NULL, ID2 INT NOT NULL);

INSERT INTO dbo.X_HEAP WITH (TABLOCK)
SELECT TOP (20000000) 
 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 100000 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3;
這是查詢快速執行時的實際執行計劃。我在計劃中找不到任何不尋常的地方。
但是，當查詢花費的時間比預期的要長時，查詢計劃就會出現問題。最終（從右到左讀取）嵌套循環運算符生成大約 133 億行。由於TOP. 我觀察到有一到三個執行緒失控，這似乎是導致執行時間長的直接原因。
為什麼我的並行查詢有時會處理數十億額外的行？我能做些什麼呢？
我在 SQL Server 2016 SP1 上。我無法重現兼容級別為 120 的問題。
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) 2016 年 10 月 28 日 18:17:30 版權所有 (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 6.3 (Build 14393: )

讓我們首先討論為什麼快速查詢處理的行數略多於 2000 萬行。這是一個螢幕截圖：
在我的測試中，執行的最終嵌套循環運算符MAXDOP 4總是生成 20008861 行，然後運算符將行減少到正好 2000 萬行TOP。這是因為 SQL Server 中的並行查詢使用數據包（它是常見CXPACKET等待事件中的“PACKET”）。我們可以通過執行一個僅返回 100 行並使用跟踪標誌 8649來強制執行並行計劃的類似查詢來看到這一點：
SELECT TOP (100) 
 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 100000 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (QUERYTRACEON 8649);
大多數時候，我看到總共處理了大約 9000 行：
每個執行緒處理的行數通常接近 1023 的倍數。那麼，這個查詢的數據包大小可能是 1023？如果執行緒正在處理數據包，而當我們最終獲得最後一行時，其他執行緒已經在獲取更多數據，那麼由於並行性，我們可以看到處理的行數超過了嚴格必要的行數，這似乎是合理的。
對於另一種分析數據包大小的方法，我們可以減少MAXDOP查詢。以 100000 的行目標，我始終看到嵌套循環連接處理了 108442 行。每個執行緒處理的行數可以改變，但在我的測試中總的總數是一樣的。更改MAXDOP為 3 會將處理的行數減少到 106395。更改MAXDOP為 2 會將處理的行數減少到 104348。對於這個查詢和其他一些類似的查詢，我總是觀察到每個DOP. 可疑的是，這幾乎是之前值 1023 的兩倍。我不確定為什麼它不一致，但至少我們可以得出結論，我們使用 more 處理更多額外行DOP。
這有點教育意義，但它不能解釋為什麼查詢有時會處理數十億行。要深入了解這一點，我們可以使用 SQL Server 2014 中引入的sys.dm_exec_query_profiles dmv。以下是 dmv 在一次緩慢執行期間的一些行的快照：
這是帶有標記的相關節點的查詢計劃，以便為數據提供一些上下文：
這裡有一些有趣的事情需要注意。我們已經在表中插入了 19997604 行，所以查詢非常接近完成。node_id 8 處的嵌套循環連接的執行緒 1-3 已完成所有工作。節點 4 處的TOP操作員已經達到其 20000000 行的行目標並已關閉。但是，嵌套循環連接的執行緒 4 繼續獲取行。根據該last_row_time列，這些行不會被發送到任何地方。此外，節點 3 處的並行節點仍處於打開狀態。如果我讓查詢執行，除了執行緒 4 的行數之外沒有任何變化：
最終它將用完要發送的數據包，查詢將完成。
似乎問題與並行性直接相關。查詢在計劃中的節點 3 處“卡住”，這是並行插入的分發流操作符。SQL Server 2016 支持對現有堆表的並行插入。SQL Server 2014 沒有，所以這可以解釋為什麼我無法以較低的兼容性級別重現該問題。所以也許問題與並行插入有關。
最明顯的解決方法是讓整個查詢連續執行。但是，假設出於性能原因，我仍然需要部分查詢並行執行。並行插入需要TABLOCK提示。如果我刪除TABLOCK提示，我將無法重現該問題，但我也會失去最少的日誌記錄。如果我不想失去最少的日誌記錄，我可以在全域級別設置跟踪標誌 9495 。包含NORECOMPILE提示或從記憶體中擦除計劃也很重要，因為即使啟用了跟踪標誌 9495，記憶體的計劃也會導致並行插入。跟踪標誌的替代方法是阻止並行插入的其他方法，例如向表中添加非聚集索引。任何索引都可以，包括實際上不包含任何行的過濾索引：
CREATE INDEX X_DUMMY_INDEX ON dbo.X_HEAP (ID) WHERE (ID = -1 AND ID = -2);
總而言之，我不能確定為什麼查詢有時會處理數十億行，但它絕對看起來像是意外行為。也許這是某種與並行性相關的競爭條件。對於這個查詢，禁用並行插入似乎足以防止錯誤再次出現。