為什麼 LEN() 函式嚴重低估了 SQL Server 2014 中的基數？

我有一個帶有字元串列和一個謂詞的表，用於檢查具有一定長度的行。在 SQL Server 2014 中，無論我檢查的長度如何，我都看到了 1 行的估計值。這會產生非常糟糕的計劃，因為實際上有數千甚至數百萬行，並且 SQL Server 選擇將此表放在嵌套循環的外側。

SQL Server 2014 的基數估計為 1.0003 而 SQL Server 2012 估計為 31,622 行，是否有解釋？有沒有好的解決方法？

以下是該問題的簡短再現：

-- Create a table with 1MM rows of dummy data
CREATE TABLE #customers (cust_nbr VARCHAR(10) NOT NULL)
GO

INSERT INTO #customers WITH (TABLOCK) (cust_nbr)
   SELECT TOP 1000000 
       CONVERT(VARCHAR(10),
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
   FROM master..spt_values v1
   CROSS JOIN master..spt_values v2
GO

-- Looking for string of a certain length.
-- While both CEs yield fairly poor estimates, the 2012 CE is much
-- more conservative (higher estimate) and therefore much more likely
-- to yield an okay plan rather than a drastically understimated loop join.
-- 2012: 31,622 rows estimated, 900K rows actual
-- 2014: 1 row estimated, 900K rows actual
SELECT COUNT(*)
FROM #customers
WHERE LEN(cust_nbr) = 6
OPTION (QUERYTRACEON 9481) -- Optionally, use 2012 CE
GO

這是一個更完整的腳本，顯示了其他測試

我還閱讀了有關 SQL Server 2014 Cardinality Estimator 的白皮書，但沒有找到任何可以澄清情況的內容。

對於舊版 CE，我看到估計是 3.16228% 的行——這是用於列 = 文字謂詞的“幻數”啟發式方法（還有其他基於謂詞構造的啟發式方法——但LEN環繞列的舊版 CE 結果與此猜測框架匹配）。您可以在Joe Sack的關於在沒有統計資訊的情況下的選擇性猜測和Ian Jose的常數-常數比較估計的文章中看到這方面的範例。

-- Legacy CE: 31622.8 rows
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  ( QUERYTRACEON 9481); -- Legacy CE
GO

現在，對於新的 CE 行為，優化器現在可以看到這一點（這意味著我們可以使用統計資訊）。我完成了查看下面計算器輸出的練習，您可以將相關的自動生成統計資訊視為指針：

-- New CE: 1.00007 rows
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  ( QUERYTRACEON 2312 ); -- New CE
GO

-- View New CE behavior with 2363 (for supported option use XEvents)
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  (QUERYTRACEON 2312, QUERYTRACEON 2363, QUERYTRACEON 3604, RECOMPILE); -- New CE
GO

/*
Loaded histogram for column QCOL:
[tempdb].[dbo].[#customers].cust_nbr from stats with id 2
Using ambient cardinality 1e+006 to combine distinct counts:
 999927

Combined distinct count: 999927
Selectivity: 1.00007e-006
Stats collection generated:
 CStCollFilter(ID=2, CARD=1.00007)
     CStCollBaseTable(ID=1, CARD=1e+006 TBL: #customers)

End selectivity computation
*/

EXEC tempdb..sp_helpstats '#customers';


--Check out AVG_RANGE_ROWS values (for example - plenty of ~ 1)
DBCC SHOW_STATISTICS('tempdb..#customers', '_WA_Sys_00000001_B0368087');
--That's my Stats name yours is subject to change

不幸的是，該邏輯依賴於對不同值數量的估計，該估計沒有針對LEN函式的效果進行調整。

可能的解決方法

LEN您可以通過重寫as a在兩種 CE 模型下獲得基於 trie 的估計LIKE：

SELECT COUNT_BIG(*)
FROM #customers AS C
WHERE C.cust_nbr LIKE REPLICATE('_', 6);

---

有關使用的跟踪標誌的資訊：

• 2363：顯示很多資訊，包括正在載入的統計資訊。
• 3604：將 DBCC 命令的輸出列印到消息選項卡。

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