SQL Server 選擇與維度表的嵌套循環連接並對每一行進行查找

我面臨 SQL Server 生成非最佳執行計劃的問題：嵌套循環連接並查找維度表並對其執行 2M 讀取。

排序操作估計是 100 行而不是 450 K 行，並且可能會影響計劃選擇：

NestedLoop：https ://www.brentozar.com/pastetheplan/?id=B110MZ2Pm或 NestedLoop 計劃

這是在測試數據庫中。我們有一個具有相同架構和幾乎相同數據的附加數據庫。

執行完全相同的查詢（都來自 SSMS）使用 Hash Join 和維度表掃描（32K 讀取）生成不同的計劃：

HashJoin：https ://www.brentozar.com/pastetheplan/?id=r1Jm7b2D7或 雜湊計劃

我需要幫助來理解和解決問題。

我可以通過提示 Hash Joint 來解決它，但是同一實例上的 2 個相似的 DB 生成不同的計劃沒有任何意義。

更新 #1：我發現估計的成本是不同的所以當 SQL Server 並行執行時，它會選擇一個散列連接。

用單執行緒會嵌套循環。

更新 #2：在從同一個表中進行 SELECT 期間發生了同樣的問題。取決於列數（估計成本）。當我減少列數時，執行計劃陷入嵌套循環並尋找維度表。

為什麼您在一個環境中獲得串列嵌套循環連接計劃而在另一個環境中獲得雜湊連接似乎有三個原因。根據您提供的資訊，最佳修復涉及查詢提示或將查詢拆分為兩部分。

1. 環境之間的差異

一個環境在您的 CCI 中有 480662 行，而另一個環境有 686053 行。我不會稱之為幾乎相同的。您的環境之間似乎也存在硬體或配置差異，或者至少您變得非常不走運。251 MB 估計數據的串列排序的 IO 成本為 0.0037538 個單位。351 MB 估計數據的並行排序具有 23.1377 個單位的 IO 成本，即使它被並行度打折。引擎希望為並行計劃溢出相對大量的數據。這樣的差異會導致環境之間的不同計劃。 2. 優化器誤用了行目標成本降低，這可能有利於嵌套循環連接計劃

嵌套循環計劃的成本好像只需要從排序中輸出 100 行：

SELECT但是，查詢在子句中包含以下內容：COUNT(*) OVER ()

引擎必須讀取所有行才能為聚合生成正確的結果。這確實是實際計劃中發生的情況，索引查找執行了 450k 次而不是 100 次。這種成本降低似乎發生在各種版本（我測試到 2016 SP1 基礎）、兩個 CE 上，具有許多不同的視窗功能，以及批處理模式和行模式。這是產品中的一個限制，導致此處的查詢計劃不理想。 3. 由於批處理模式排序的限制，嵌套循環連接計劃是串列的

您的串列嵌套循環連接可能符合併行性（取決於您的 CTFP），您可能想知道為什麼優化器沒有找到成本較低的並行計劃。優化器具有啟發式方法，可防止並行批處理模式排序成為嵌套循環連接（必須以行模式執行）的第一個子項。問題是並行批處理模式排序會將所有行放在一個執行緒上，這與並行嵌套循環連接不起作用。將排序移動為循環連接的父級不會導致索引查找的估計執行次數減少（由於優化器問題）。因此，即使 CTFP 設置為預設值 5，您也很可能最終得到一個串列計劃。

---

這是您的問題的複製品，我無法將其上傳到 PasteThePlan，因為它不支持我的 SQL Server 版本：

drop table if exists cci_216665;

create table cci_216665 (
   SORT_ID BIGINT,
   JOIN_ID BIGINT,
   COL1 BIGINT,
   COL2 BIGINT,
   COL3 BIGINT,
   INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO cci_216665 WITH (TABLOCK)
SELECT TOP (500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 50
, 0, 0, 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

drop table if exists YEAH_NAH;

CREATE TABLE dbo.YEAH_NAH (ID INT, FILLER VARCHAR(20), PRIMARY KEY (ID));

INSERT INTO dbo.YEAH_NAH WITH (TABLOCK)
SELECT TOP (50) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 'CHILLY BIN'
FROM master..spt_values t1;

GO

-- takes 780 ms of CPU with nested loops
SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID;

-- takes 111 ms of CPU with hash join
SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID
OPTION (HASH JOIN);

解決問題的最直接方法是將查詢分成兩部分。這是一種方法：

SELECT COUNT(*)
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID;

SELECT TOP (100) *
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID;

在我的機器上，這實際上比散列連接計劃更快，但您可能看不到相同的結果。一般來說，我對像您這樣的查詢的第一次嘗試是OVER在只需要前 100 行時避免沒有子句的視窗聚合。

一個合理的替代方法是使用DISABLE_OPTIMIZER_ROWGOALSQL Server 2016 SP1 中引入的使用提示。對於這種類型的查詢，行目標存在問題，因此此提示直接解決了該問題，而不依賴於統計資訊或類似的任何東西。我認為這是一個相對安全的提示。

SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

這會在我的機器上產生一個雜湊連接計劃。

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