SQL Server 如何知道謂詞是相關的？

December 21, 2021

在診斷 SQL Server 2008 R2 查詢時基數估計較差（儘管有簡單的索引、最新的統計資訊等）以及因此較差的查詢計劃，我發現了一篇可能相關的知識庫文章： FIX：執行查詢時性能不佳包含 SQL Server 2008 或 SQL Server 2008 R2 或 SQL Server 2012 中的相關 AND 謂詞
我可以猜測知識庫文章中“相關”的含義，例如謂詞#2 和謂詞#1 主要針對相同的行。
但我不知道 SQL Server 是如何知道這些相關性的。表是否需要包含來自兩個謂詞的列的多列索引？SQL 是否使用統計資訊來檢查一列中的值是否與另一列相關？還是使用了其他方法？
我問這個有兩個原因：
確定可以使用此修補程序改進我的哪些表和查詢
知道我應該在索引、統計等方面做些什麼來影響#1

考慮下面顯示的簡單AdventureWorks查詢和執行計劃。查詢包含與連接的謂詞AND。優化器的基數估計為41,211行：
-- Estimate 41,211 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
   TH.TransactionID BETWEEN 100000 AND 168336
   AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';
使用預設統計資訊
僅給定單列統計資訊，優化器通過分別估計每個謂詞的基數並將得到的選擇性相乘來產生此估計。這種啟發式假設謂詞是完全獨立的。
將查詢分成兩部分使計算更容易查看：
-- Estimate 68,336.4 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
   TH.TransactionID BETWEEN 100000 AND 168336;
Transaction History 表總共包含 113,443 行，因此 68,336.4 估計表示此謂詞的選擇性為 68336.4 / 113443 = 0.60238533。該估計值是使用TransactionID列的直方圖資訊和查詢中指定的常數值獲得的。
-- Estimate 68,413 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
   TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';
該謂詞的估計選擇性為 68413.0 / 113443 = 0.60306056。TransactionDate同樣，它是根據謂詞的常量值和統計對象的直方圖計算得出的。
假設謂詞是完全獨立的，我們可以通過將它們相乘來估計兩個謂詞的選擇性。最終的基數估計是通過將結果選擇性乘以基表中的 113,443 行獲得的：
0.60238533 * 0.60306056 * 113443 = 41210.987
四捨五入後，這是在原始查詢中看到的 41,211 估計值（優化器也在內部使用浮點數學）。
不是一個很好的估計
和列在 AdventureWorks 數據集中具有密切的相關性（就像單調遞增的鍵和日期列通常一樣）TransactionID。TransactionDate這種相關性意味著違反了獨立性假設。因此，執行後查詢計劃顯示 68,095 行，而不是估計的 41,211：
跟踪標誌 4137
啟用此跟踪標誌會更改用於組合謂詞的啟發式方法。優化器沒有假設完全獨立，而是認為兩個謂詞的選擇性足夠接近，以至於它們很可能是相關的：
-- Estimate 68,336.4
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
   TH.TransactionID BETWEEN 100000 AND 168336
   AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13'
OPTION (QUERYTRACEON 4137);
回想一下，TransactionID單獨的謂詞估計 68,336.4 行，TransactionDate單獨的謂詞估計 68,413 行。優化器選擇了這兩個估計值中較低的一個，而不是乘以選擇性。
當然，這只是一種不同的啟發式方法，但它可以幫助改進對具有相關AND謂詞的查詢的估計。AND每個謂詞都考慮了可能的相關性，並且當涉及許多子句時還會進行其他調整，但該範例用於顯示它的基礎知識。
目前基數估計器中的跟踪標誌 4137 的等效項是ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES 查詢提示。
使 SQL Server 在估計過濾器的 AND 謂詞以考慮完全相關時使用最小選擇性生成計劃。當與 SQL Server 2012 (11.x) 及更早版本的基數估計模型一起使用時，此提示名稱等效於跟踪標誌 4137，並且當跟踪標誌 9471 與 SQL Server 2014 (12.x) 的基數估計模型一起使用時，具有類似的效果）或更高。
多列統計
這些可以幫助查詢相關性，但直方圖資訊仍然僅基於統計資訊的前導列。因此，以下候選多列統計數據在一個重要方面有所不同：
CREATE STATISTICS
   [stats Production.TransactionHistory TransactionID TransactionDate]
ON Production.TransactionHistory
   (TransactionID, TransactionDate);

CREATE STATISTICS
   [stats Production.TransactionHistory TransactionDate TransactionID]
ON Production.TransactionHistory
   (TransactionDate, TransactionID);
僅取其中之一，我們可以看到唯一的額外資訊是“全部”密度的額外級別。直方圖仍然只包含有關該TransactionDate列的詳細資訊。
DBCC SHOW_STATISTICS
   (
       'Production.TransactionHistory', 
       'stats Production.TransactionHistory TransactionDate TransactionID'
   );
有了這些多列統計數據……
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
   TH.TransactionID BETWEEN 100000 AND 168336
   AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';
…執行計劃顯示的估計值與只有單列統計資訊可用時完全相同：