慢速並行 SQL Server 查詢，幾乎即時串列

我有一個 SQL Server 查詢如下（混淆）：

UPDATE  [TABLE1]
SET     [COLUMN1] = CAST('N' AS CHAR(1))
FROM    [TABLE1]
WHERE   (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR 
       (COLUMN1 <> 'N' AND (
                                   SELECT  COUNT(*)
                                   FROM    TABLE2 wle
                                           JOIN TABLE3 wl 
                                                   ON wl.COLUMN3 = wle.COLUMN3
                                   WHERE   TABLE1.COLUMN4 = wle.COLUMN4 AND 
                                           (wl.COLUMN5 = '1' OR wl.COLUMN6 = '1') AND 
                                           wle.COLUMN7 = (
                                                           SELECT  MIN(alias.COLUMN7)
                                                           FROM    TABLE2 AS alias
                                                           WHERE   TABLE1.COLUMN4 = alias.COLUMN4
                                                           )
                               ) > 0
                                           
       )

我們剛剛將我們的（測試）伺服器從 SQL Server 2014 SP3 升級到 SQL Server 2016 SP2。

因此，上述查詢的性能似乎已經跌落懸崖。

伺服器在SQL Server 2014的時候，數據庫兼容級別是120，現在是SQL Server 2016，數據庫的兼容級別還是120，但是我試過110120和130的查詢，結果都一樣.

當我執行時sp_whoisactive，我可以看到 wait_info(48847425ms)CXCONSUMER提示查詢已等待CXCONSUMER最後48847425ms（814 分鐘）查詢目前已執行 13:34:07.587。

等待資訊表明查詢一直在等待CXCONSUMER大多數（如果不是全部）執行時間。

這對我來說暗示了並行性的一些問題，所以我使用提示執行查詢OPTION (MAXDOP 1)並在可接受的時間內完成（大約 30 秒）

計劃形狀如下。

查詢的計劃形狀MAXDOP 1如下：

（沒有並行運算符的相同計劃）

當我在啟用實時執行計劃的情況下執行並行查詢時，以綠色突出顯示的運算符（Clustered Index scan on TABLE2）顯示 100%，但它的執行時間繼續計時。

以紅色突出顯示的運算符（Clustered Index Scan on TABLE1）在 180,215 的 4 行中“卡住”。

什麼可能導致這個問題？在我的腦海中，我是否認為這是並行性傾斜（工作量不均勻），但鑑於串列查詢在不到一分鐘的時間內完成，我會認為即使查詢並行但只使用一個執行緒，它仍然會在接近的時間內完成串列查詢。此外，鑑於實時計劃似乎顯示紅色聚集索引掃描根本沒有進行，我不確定發生了什麼。

處理器和 I/O 關聯設置為自動

我發現這篇文章描述了類似的行為，儘管文章的目的似乎表明 CXCONSUMER 不一定是良性等待，也沒有說明如何/是否可以修復。

在修復它方面，我知道可以以更有效的方式重寫程式碼（可以將子查詢COUNT和MIN子查詢都選擇到變數中），但不幸的是，更改查詢不是我的選擇。我可以強制MAXDOP提示，但同樣，這意味著更改程式碼，也許我可以通過計劃指南強制它，儘管這種強制優化器的做法通常是不可取的？

是什麼導致了這個問題？

為什麼查詢在 SQL Server 2016 中執行緩慢？

為什麼 TABLE1 上的聚集索引掃描“卡”在 180,215 的 4 行上？

有沒有辦法在不更改程式碼的情況下解決這個問題？

有沒有辦法在不更改程式碼的情況下解決這個問題？

創建以下索引以消除急切的索引假離線：

-- Give this index a better name
CREATE INDEX i ON dbo.TABLE2 (COLUMN4, COLUMN7) INCLUDE (COLUMN3);

有了這個，你應該得到一個類似的計劃：

一旦您為優化器提供良好的索引，問題中描述的其他問題幾乎肯定會消失。

您的查詢可能（儘管不太可能）遭受未檢測到的查詢內並行死鎖 (IQPD)。需要進行非常詳細的調查來確認這一點。檢測到絕大多數 IQPD ，然後可以通過將交換緩衝區溢出到tempdb來解決。未檢測到的 IQPD 將導致查詢永遠卡住。

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