在 SQL Server 中應用基數估計問題

現在，在一個看似相當簡單的情況下，我面臨著對我來說不太清楚的基數估計邏輯問題。我在工作中遇到了這種情況，因此，出於隱私考慮，我將在下面僅提供問題的一般描述，但是為了更詳細的分析，我在 AdventureWorksDW 培訓基地模擬了這個問題。

有如下形式的查詢：

SELECT <some columns>
FROM <some dates table>
CROSS APPLY(

   SELECT
       <some p columns>
   FROM <some table> p
   WHERE p.StartDate <= Dates.d
     AND p.EndDate >= Dates.d
) t

從上面給出的執行計劃中可以看出，基數估計器估計 Index Seek 操作中的估計行數為 17,884,200（對應 NL 外部每行 2,980,700 行），這與實際數量非常接近.

現在我將修改查詢並添加到 CROSS APPLY LEFT OUTER JOIN：

SELECT <some columns t>
FROM <some dates table>
CROSS APPLY(

   SELECT
       <some p columns>
   <some columns f>
   FROM <some table> p
   LEFT JOIN <some table> f ON p.key = f.key
       AND f.date = Dates.d
   WHERE p.StartDate <= Dates.d
     AND p.EndDate >= Dates.d
) t

此查詢給出以下計劃：

看到查詢的邏輯形式，假設Index Seek操作的預期行數保持不變是合乎邏輯的，雖然我知道尋找計劃的路線不同，但是，似乎部分紅色突出顯示的沒有改變，相同的謂詞等，但是Index Seek現在的估計是664,506（對應來自NL外部的每行110,751），這是一個嚴重的錯誤，在生產環境中會導致嚴重的tempdb溢出數據。

上述查詢是在 Sql Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (x64) 的實例上執行的。

為了獲得更多細節和簡化分析，我在 AdventureWorksDW2017 數據庫中的 SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 實例上模擬了這個問題，但我在打開 9481 跟踪標誌的情況下執行查詢以模擬系統使用基數估計器版本 70。

下面是一個帶有左外連接的查詢。

DECLARE @db DATE = '20130720'
DECLARE @de DATE = '20130802'

;WITH Dates AS(

   SELECT [FullDateAlternateKey] AS d
   FROM [AdventureWorksDW2017].[dbo].[DimDate]
   WHERE [FullDateAlternateKey] BETWEEN @db AND @de
)
SELECT *
FROM Dates
CROSS APPLY(

   SELECT
       p.[ProductAlternateKey]
      ,f.[OrderQuantity]
   FROM [AdventureWorksDW2017].[dbo].[DimProduct] p
   LEFT JOIN [AdventureWorksDW2017].[dbo].[FactInternetSales] f ON f.ProductKey = p.ProductKey
      AND f.[OrderDate] = Dates.d
   WHERE p.StartDate <= Dates.d
     AND ISNULL(p.EndDate, '99991231') >= Dates.d

) t
OPTION(QUERYTRACEON 9481 /*force legacy CE*/)

還值得注意的是，在 DimProduct 表上創建了以下索引：

CREATE NONCLUSTERED INDEX [Date_Indx] ON [dbo].[DimProduct]
(
   [StartDate] ASC,
   [EndDate] ASC
)
INCLUDE([ProductAlternateKey])

該查詢給出以下查詢計劃：（1）

如您所見，以紅色突出顯示的查詢部分估計為 59,754（每行約 182）。現在我將展示一個沒有左外連接的查詢計劃。(2)

如您所見，以紅色突出顯示的查詢部分的得分為 97 565（每行約 297），差異不是很大，但是過濾器（3）運算符的基數得分明顯不同〜每行 244與左外連接查詢中的 ~ 54 相比。

(3) – 過濾謂詞：

isnull([AdventureWorksDW2017].[dbo].[DimProduct].[EndDate] as [p].[EndDate],'9999-12-31 00:00:00.000')>=[AdventureWorksDW2017].[dbo].[DimDate].[FullDateAlternateKey]

為了更深入地研究，我查看了上述計劃中的物理操作員樹。

以下是未記錄標誌 8607 和 8612 的踪跡中最重要的部分。

對於計劃 (2)：

PhyOp_Apply lookup TBL: AdventureWorksDW2017.dbo.DimProduct
…
PhyOp_Range TBL: AdventureWorksDW2017.dbo.DimProduct(alias TBL: p)(6) ASC  Bmk ( QCOL: [p].ProductKey) IsRow: COL: IsBaseRow1002  [ Card=296.839 Cost(RowGoal 0,ReW 0,ReB 327.68,Dist 328.68,Total 328.68)= 0.174387 ](Distance = 2)
             ScaOp_Comp x_cmpLe
                ScaOp_Identifier QCOL: [p].StartDate
                ScaOp_Identifier QCOL: [AdventureWorksDW2017].[dbo].[DimDate].FullDateAlternateKey

對於計劃 (1)：

PhyOp_Apply (x_jtInner)
…
PhyOp_Range TBL: AdventureWorksDW2017.dbo.DimProduct(alias TBL: p)(6) ASC  Bmk ( QCOL: [p].ProductKey) IsRow: COL: IsBaseRow1002  [ Card=181.8 Cost(RowGoal 0,ReW 0,ReB 327.68,Dist 328.68,Total 328.68)= 0.132795 ](Distance = 2)


                ScaOp_Comp x_cmpLe

                   ScaOp_Identifier QCOL: [p].StartDate

                   ScaOp_Identifier QCOL: [AdventureWorksDW2017].[dbo].[DimDate].FullDateAlternateKey

如您所見，優化器選擇了 Apply 運算符的各種實現，(2) 中的 PhyOp_Apply 查找和 (1) 中的 PhyOp_Apply (x_jtInner)，但我仍然不明白我可以從中提取什麼。

通過重寫沒有左外連接的原始查詢，我可以得到與計劃 (1) 中相同的估計值，如下所示：

DECLARE @db DATE = '20130720'
DECLARE @de DATE = '20130802'

;WITH Dates AS(

   SELECT [FullDateAlternateKey] AS d
   FROM [AdventureWorksDW2017].[dbo].[DimDate]
   WHERE [FullDateAlternateKey] BETWEEN @db AND @de
)
SELECT *
FROM Dates
CROSS APPLY(

   SELECT TOP(1000000000)
       p.[ProductAlternateKey]
   FROM [AdventureWorksDW2017].[dbo].[DimProduct] p
   WHERE p.StartDate <= Dates.d
     AND ISNULL(p.EndDate, '99991231') >= Dates.d

) t
OPTION(QUERYTRACEON 9481 /*force legacy CE*/)

這給出了以下計劃：（4）

如您所見，以紅色突出顯示的區域的估計與計劃 (1) 和物理運算符樹中的 PhyOp_Apply (x_jtInner) 運算符一致。

請幫我回答這個問題，有沒有辦法影響這樣的基數估計，可能通過提示或更改查詢形式等，並幫助理解為什麼優化器在這種情況下給出這樣的估計。

通常有幾種方法可以得出基數估計，每種方法給出不同（但同樣有效）的答案。這就是統計和估計的本質。

您基本上問為什麼一種方法會產生296.839行的估計值，而另一種方法會產生181.8行的估計值。

---

讓我們看一下問題中給出的相同 AdventureWorksDW2017 連接的更簡單範例：

範例 1 - 加入

DECLARE @db date = '20130720';
DECLARE @de date = '20130802';

SELECT DD.FullDateAlternateKey, DP.ProductAlternateKey
FROM dbo.DimDate AS DD
JOIN dbo.DimProduct AS DP
   ON DP.StartDate <= CONVERT(datetime, DD.FullDateAlternateKey)
WHERE
   DD.FullDateAlternateKey BETWEEN @db AND @de
OPTION (FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

這是一個連接：

• DimDate（過濾FullDateAlternateKey BETWEEN @db AND @de）；和
• DimProduct

連接謂詞為：

• DP.StartDate <= CONVERT(datetime, DD.FullDateAlternateKey)

計算連接選擇性的一種方法是考慮值將如何與使用直方圖資訊FullDateAlternateKey的值重疊。StartDate

的直方圖步驟FullDateAlternateKey將根據 的選擇性進行縮放BETWEEN @db AND @de，然後再進行比較DP.StartDate以查看它們是如何連接的。

使用原始 CE，連接估計將在“連接”之前使用線性插值逐步對齊兩個直方圖。

一旦我們使用這種方法計算了連接的選擇性，連接是散列、合併、嵌套循環還是應用都無關緊要（除了顯示目的）。

基於直方圖的計算步驟並不是特別困難，但它們太冗長，無法在此處顯示。所以我會切入正題，簡單地展示結果：

請注意在查找時估計有296.839行DimProduct。

這是連接基數估計被計算為 97,565.2 行（使用直方圖）的結果。過濾器DimDate通過 328.68 行，因此內側必須平均每次迭代產生 296.839 行才能使數學計算出來。

如果此查詢可以使用散列或合併連接（由於不等式，它不是），DimProduct則將掃描該表，生成其所有 606 行。連接的結果仍然是 97,565.2 行。

此估計是作為連接估計的結果。

範例 2 - 應用

我們還可以將此查詢估計為apply。用 T-SQL 編寫的邏輯等價形式是：

DECLARE @db date = '20130720';
DECLARE @de date = '20130802';

SELECT DD.FullDateAlternateKey, DP.ProductAlternateKey
FROM dbo.DimDate AS DD
CROSS APPLY
(
   SELECT DP.ProductAlternateKey
   FROM dbo.DimProduct AS DP
   WHERE
       DP.StartDate <= CONVERT(datetime, DD.FullDateAlternateKey)
) AS DP
WHERE
   DD.FullDateAlternateKey BETWEEN @db AND @de
OPTION (FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'), QUERYTRACEON 9114);

（跟踪標誌 9114 阻止優化器將應用重寫為連接）

這次的估計方法是DimProduct從DimDate（每次迭代）評估每行將匹配多少行：

和以前一樣，我們有 328.68 行，但現在這些行中的每一行都DimDate應該匹配.DimProduct

這只是對 選擇性的猜測StartDate <= FullDateAlternateKey。

猜測是 606 行中的 30% DimProduct：0.3 * 606 = 181.8行。

該估計是作為應用估計的結果。

最後的筆記

您的範例引入了外部聯接，以使查詢過於復雜，以至於優化器無法從應用轉換為聯接形式。在 apply 內部使用TOP是另一種說服優化器不要將 apply 轉換為 join 的方法（即使可以）。

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