主/明細表之間的雜湊連接產生過低的基數估計

將主表連接到詳細表時，如何鼓勵 SQL Server 2014 使用較大（詳細）表的基數估計作為連接輸出的基數估計？

例如，當將 10K 主行連接到 100K 詳細行時，我希望 SQL Server 估計 100K 行的聯接——與估計的詳細行數相同。我應該如何建構我的查詢和/或表和/或索引來幫助 SQL Server 的估算器利用每個細節行總是有一個相應的主行這一事實？（這意味著它們之間的連接永遠不應該減少基數估計。）

這裡有更多細節。我們的數據庫有一對主/從表：VisitTarget每個銷售交易VisitSale有一行，每個交易中的每個產品都有一個行。這是一對多的關係：一個 VisitTarget 行對應平均 10 個 VisitSale 行。

表格如下所示：（我正在簡化為僅與此問題相關的列）

-- "master" table
CREATE TABLE VisitTarget
(
 VisitTargetId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 SaleDate date NOT NULL,
 StoreId int NOT NULL
 -- other columns omitted for clarity  
);
-- covering index for date-scoped queries
CREATE NONCLUSTERED INDEX IX_VisitTarget_SaleDate 
   ON VisitTarget (SaleDate) INCLUDE (StoreId /*, ...more columns */);

-- "detail" table
CREATE TABLE VisitSale
(
 VisitSaleId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 VisitTargetId int NOT NULL,
 SaleDate date NOT NULL, -- denormalized; copied from VisitTarget
 StoreId int NOT NULL, -- denormalized; copied from VisitTarget
 ItemId int NOT NULL,
 SaleQty int NOT NULL,
 SalePrice decimal(9,2) NOT NULL
 -- other columns omitted for clarity  
);
-- covering index for date-scoped queries
CREATE NONCLUSTERED INDEX IX_VisitSale_SaleDate 
 ON VisitSale (SaleDate)
 INCLUDE (VisitTargetId, StoreId, ItemId, SaleQty, TotalSalePrice decimal(9,2) /*, ...more columns */
);
ALTER TABLE VisitSale 
 WITH CHECK ADD CONSTRAINT FK_VisitSale_VisitTargetId 
 FOREIGN KEY (VisitTargetId)
 REFERENCES VisitTarget (VisitTargetId);
ALTER TABLE VisitSale
 CHECK CONSTRAINT FK_VisitSale_VisitTargetId;

出於性能原因，我們通過將最常見的過濾列（例如SaleDate）從主表複製到每個明細表行來進行部分非規範化，然後我們在兩個表上添加覆蓋索引以更好地支持日期過濾查詢。這對於減少執行日期過濾查詢時的 I/O 非常有效，但我認為這種方法在將主表和詳細表連接在一起時會導致基數估計問題。

當我們連接這兩個表時，查詢如下所示：

SELECT vt.StoreId, vt.SomeOtherColumn, Sales = sum(vs.SalePrice*vs.SaleQty)
FROM VisitTarget vt 
   JOIN VisitSale vs on vt.VisitTargetId = vs.VisitTargetId
WHERE
   vs.SaleDate BETWEEN '20170101' and '20171231'
   and vt.SaleDate BETWEEN '20170101' and '20171231'
   -- more filtering goes here, e.g. by store, by product, etc. 

明細表 ( VisitSale) 上的日期過濾器是多餘的。它可以在詳細表上為按日期範圍過濾的查詢啟用順序 I/O（又名 Index Seek 運算符）。

此類查詢的計劃如下所示：

可以在此處找到具有相同問題的查詢的實際計劃。

如您所見，連接的基數估計（圖片左下角的工具提示）太低了 4 倍以上：實際為 210 萬，估計為 0.5 萬。這會導致性能問題（例如溢出到 tempdb），尤其是當此查詢是用於更複雜查詢的子查詢時。

但是連接的每個分支的行數估計值接近於實際行數。連接的上半部分實際為 100K，而估計為 164K。連接的下半部分實際為 210 萬行，而估計為 370 萬行。雜湊桶分佈看起來也不錯。這些觀察結果向我表明，每個表的統計數據都可以，問題在於連接基數的估計。

起初我認為問題在於 SQL Server 期望每個表中的 SaleDate 列是獨立的，而實際上它們是相同的。所以我嘗試將銷售日期的相等比較添加到連接條件或 WHERE 子句中，例如

ON vt.VisitTargetId = vs.VisitTargetId and vt.SaleDate = vs.SaleDate

要麼

WHERE vt.SaleDate = vs.SaleDate

這沒有用。它甚至使基數估計變得更糟！因此，要麼 SQL Server 沒有使用該相等提示，要麼其他原因是問題的根本原因。

對如何排除故障並希望解決此基數估計問題有任何想法嗎？我的目標是估計主/詳細連接的基數與連接的較大（“詳細資訊表”）輸入的估計值相同。

如果重要，我們將在 Windows Server 上執行 SQL Server 2014 Enterprise SP2 CU8 build 12.0.5557.0。沒有啟用跟踪標誌。數據庫兼容級別是 SQL Server 2014。我們在多個不同的 SQL Server 上看到相同的行為，因此這似乎不太可能是特定於伺服器的問題。

SQL Server 2014 Cardinality Estimator中有一個優化，這正是我正在尋找的行為：

然而，新的 CE 使用了一種更簡單的算法，該算法假定大表和小表之間存在一對多的連接關聯。這假設大表中的每一行都與小表中的一行完全匹配。該算法返回較大輸入的估計大小作為連接基數。

理想情況下，我可以獲得這種行為，其中連接的基數估計與大表的估計相同，即使我的“小”表仍將返回超過 100K 行！

假設通過對統計數據做某事或使用舊版 CE 無法獲得任何改進，那麼解決問題的最直接方法是將您的問題更改INNER JOIN為LEFT OUTER JOIN：

SELECT vt.StoreId, vt.SomeOtherColumn, Sales = sum(vs.SalePrice*vs.SaleQty)
FROM VisitSale vs
   LEFT OUTER JOIN VisitTarget vt on vt.VisitTargetId = vs.VisitTargetId
           AND vt.SaleDate BETWEEN '20170101' and '20171231'
WHERE vs.SaleDate BETWEEN '20170101' and '20171231'

如果表之間有外鍵，則始終在SaleDate兩個表的相同範圍內進行過濾，並且表SaleDate之間始終匹配，則查詢結果不應更改。使用這樣的外連接可能看起來很奇怪，但可以將其視為通知查詢優化器，與VisitTarget表的連接永遠不會減少查詢返回的行數。不幸的是，外鍵不會改變基數估計，所以有時你需要使用這樣的技巧。（Microsoft Connect 建議：使用元數據使優化器估計更準確）。

根據聯接後查詢中發生的其他情況，以這種形式編寫查詢可能無法正常工作。您可以嘗試使用臨時表來保存具有最重要基數估計的結果集的中間結果。

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