SQL Server 2014：自聯接基數估計不一致的任何解釋？

考慮 SQL Server 2014 中的以下查詢計劃：

在查詢計劃中，自聯接ar.fId = ar.fId產生 1 行的估計值。然而，這是一個邏輯上不一致的估計：ar有20,608行和只有一個不同的值fId（準確地反映在統計數據中）。因此，此連接產生行（~424MM行）的全叉積，導致查詢執行數小時。

我很難理解為什麼 SQL Server 會提出一個很容易證明與統計數據不一致的估計值。有任何想法嗎？

初步調查和其他細節

根據 Paul 在此處的回答，用於估計連接基數的 SQL 2012 和 SQL 2014 啟發式方法似乎都可以輕鬆處理需要比較兩個相同直方圖的情況。

我從跟踪標誌 2363 的輸出開始，但無法輕易理解。下面的程式碼片段是否意味著 SQL Server 正在比較直方圖，fId以便bId估計僅使用 的連接的選擇性fId？如果是這樣，那顯然是不正確的。還是我誤讀了跟踪標誌輸出？

Plan for computation:
 CSelCalcExpressionComparedToExpression( QCOL: [ar].fId x_cmpEq QCOL: [ar].fId )
Loaded histogram for column QCOL: [ar].bId from stats with id 3
Loaded histogram for column QCOL: [ar].fId from stats with id 1
Selectivity: 0

請注意，我提出了幾種解決方法，它們包含在完整的重現腳本中，並將此查詢縮短到毫秒。這個問題的重點是了解行為，如何在以後的查詢中避免它，以及確定它是否是應該向 Microsoft 送出的錯誤。

這是一個完整的複制腳本，這是跟踪標誌 2363 的完整輸出，這裡是查詢和表定義，以防您想在不打開完整腳本的情況下快速查看它們：

WITH cte AS (
   SELECT ar.fId, 
       ar.bId,
       MIN(CONVERT(INT, ar.isT)) AS isT,
       MAX(CONVERT(INT, tcr.isS)) AS isS
   FROM  #SQL2014MinMaxAggregateCardinalityBug_ar ar 
   LEFT OUTER JOIN #SQL2014MinMaxAggregateCardinalityBug_tcr tcr
       ON tcr.rId = 508
       AND tcr.fId = ar.fId
       AND tcr.bId = ar.bId
   GROUP BY ar.fId, ar.bId
)
SELECT s.fId, s.bId, s.isS, t.isS
FROM cte s 
JOIN cte t 
   ON t.fId = s.fId 
   AND t.isT = 1

---

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_ar (
   fId INT NOT NULL,
   bId INT NOT NULL,
   isT BIT NOT NULL
   PRIMARY KEY (fId, bId)
)

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_tcr (
   rId INT NOT NULL,
   fId INT NOT NULL,
   bId INT NOT NULL,
   isS BIT NOT NULL
   PRIMARY KEY (rId, fId, bId, isS)
)

我很難理解為什麼 SQL Server 會提出一個很容易證明與統計數據不一致的估計值。

一致性

沒有一致性的一般保證。可以使用不同的統計方法在不同的時間對不同的（但邏輯上等價的）子樹計算估計值。

說連接這兩個相同的子樹應該產生叉積的邏輯沒有錯，但同樣沒有什麼可以說推理的選擇比其他任何方法都更合理。

初步估計

在您的特定情況下，連接的初始基數估計不會在兩個相同的 subtrees 上執行。當時的樹形是：

LogOp_Join
LogOp_GbAgg
LogOp_LeftOuterJoin
LogOp_Get TBL: ar
LogOp_Select
LogOp_Get TBL: tcr
ScaOp_Comp x_cmpEq
ScaOp_Identifier [tcr].rId
ScaOp_Const 值=508
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier [ar] .fId
ScaOp_Identifier [tcr] .fId
ScaOp_Comp x_cmpEq
ScaOp_Identifier [ar] .bId
ScaOp_Identifier [tcr].bId
AncOp_PrjList
AncOp_PrjEl Expr1003
ScaOp_AggFunc stopMax
ScaOp_Convert int
ScaOp_Identifier [tcr].isS
LogOp_Select
LogOp_GbAgg
LogOp_LeftOuterJoin
LogOp_Get TBL: ar
LogOp_Select
LogOp_Get TBL: tcr
ScaOp_Comp x_cmpEq
ScaOp_Identifier [tcr].rId
ScaOp_Const 值=508
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier [ar] .fId
ScaOp_Identifier [tcr] .fId
ScaOp_Comp x_cmpEq
ScaOp_Identifier [ar] .bId
ScaOp_Identifier [tcr].bId
AncOp_PrjList
AncOp_PrjEl Expr1006
ScaOp_AggFunc stopMin
ScaOp_Convert int
ScaOp_Identifier[ar].isT
AncOp_PrjEl Expr1007
ScaOp_AggFunc stopMax
ScaOp_Convert int
ScaOp_Identifier [tcr].isS
ScaOp_Comp x_cmpEq
ScaOp_Identifier Expr1006
ScaOp_Const 值=1
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL：[ar] .fId
ScaOp_Identifier QCOL：[ar] .fId

第一個連接輸入已經簡化了一個未投影的聚合，第二個連接輸入將謂詞t.isT = 1推到它下面，其中t.isTis MIN(CONVERT(INT, ar.isT))。儘管如此，isT謂詞的選擇性計算仍可用於CSelCalcColumnInInterval直方圖：

CSelCalcColumnInInterval
列：COL：Expr1006

已載入 QCOL 列的直方圖：[ar].isT 來自 id 為 3 的統計資訊

選擇性：4.85248e-005

生成的統計資訊集合：
CStCollFilter(ID=11, CARD=1)
CStCollGroupBy(ID=10, CARD=20608)
CStCollOuterJoin(ID=9, CARD=20608 x_jtLeftOuter)
CStCollBaseTable(ID=3, CARD=20608 TBL: ar)
CStCollFilter(ID=8, CARD=1)
CStCollBaseTable(ID=4, CARD=28 TBL: tcr)

（正確的）期望是通過此謂詞將 20,608 行減少到 1 行。

連接估計

現在的問題變成了來自另一個連接輸入的 20,608 行如何與這一行匹配：

LogOp_Join
CStCollGroupBy(ID=7, CARD=20608)
CStCollOuterJoin(ID=6, CARD=20608 x_jtLeftOuter)
...

CStCollFilter(ID=11, CARD=1)
CStCollGroupBy(ID=10, CARD=20608)
...

ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL：[ar] .fId
ScaOp_Identifier QCOL：[ar] .fId

一般來說，有幾種不同的方法來估計連接。例如，我們可以：

• 在每個子樹中的每個計劃運算符處派生新的直方圖，在連接處對齊它們（根據需要插入步長值），並查看它們如何匹配；要麼
• 對直方圖進行更簡單的“粗略”對齊（使用最小值和最大值，而不是逐步進行）；要麼
• 單獨計算連接列的單獨選擇性（來自基表，並且沒有任何過濾），然後添加非連接謂詞的選擇性效果。
• …

根據使用的基數估計器和一些啟發式方法，可以使用其中任何一個（或變體）。有關更多資訊，請參閱 Microsoft 白皮書使用 SQL Server 2014 基數估計器優化您的查詢計劃。

漏洞？

現在，如問題中所述，在這種情況下，“簡單”單列連接（on fId）使用CSelCalcExpressionComparedToExpression計算器：

計算計劃：

CSelCalcExpressionComparedToExpression [ar] .fId x_cmpEq [ar] .fId

已載入 QCOL 列的直方圖：[ar].bId 來自 id 為 2 的統計資訊
QCOL 列的載入直方圖：[ar].fId 來自 id 為 1 的統計資訊

選擇性：0

此計算評估將 20,608 行與 1 個過濾行連接起來的選擇性為零：沒有行會匹配（在最終計劃中報告為一行）。這是錯的嗎？是的，這裡的新 CE 中可能存在錯誤。有人可能會爭辯說 1 行將匹配所有行或不匹配，因此結果可能是合理的，但有理由不相信。

細節實際上相當棘手，但期望基於未過濾fId直方圖的估計，由過濾器的選擇性修改，給20608 * 20608 * 4.85248e-005 = 20608出行是非常合理的。

遵循這個計算意味著使用計算器CSelCalcSimpleJoinWithDistinctCounts而不是CSelCalcExpressionComparedToExpression。沒有記錄的方法可以做到這一點，但如果您好奇，可以啟用未記錄的跟踪標誌 9479：

請注意，最終連接從兩個單行輸入生成 20,608 行，但這不足為奇。它與原 CE 在 TF 9481 下製作的計劃相同。

我提到細節很棘手（調查起來也很耗時），但據我所知，問題的根本原因與 predicate 相關rId = 508，選擇性為零。這個零估計值以正常方式提升到一行，當它考慮輸入樹中的較低謂詞時，這似乎有助於相關連接處的零選擇性估計值（因此載入 的統計資訊bId）。

允許外部聯接保持零行內側估計（而不是提高到一行）（因此所有外部行都符合條件）使用任一計算器給出“無錯誤”聯接估計。如果您有興趣探索這一點，未記錄的跟踪標誌是 9473（單獨）：

連接基數估計的行為CSelCalcExpressionComparedToExpression也可以修改為不考慮bId另一個未記錄的變化標誌 (9494)。我提到所有這些是因為我知道你對這些事情很感興趣；不是因為他們提供了解決方案。在您將問題報告給 Microsoft 並且他們解決（或不解決）之前，以不同方式表達查詢可能是最好的前進方式。不管這種行為是否是有意的，他們都應該有興趣了解回歸。

最後，整理一下複製腳本中提到的另一件事：問題計劃中過濾器的最終位置是基於成本的探索GbAggAfterJoinSel將聚合和過濾器移動到連接上方的結果，因為連接輸出有這麼小的行數。如您所料，過濾器最初位於連接下方。

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