SQL Server 2016 中包含 SUBSTRING() 的謂詞估計值的變化？

是否有關於 SQL Server 2016 中關於如何估計包含 SUBSTRING() 或其他字元串函式的謂詞的基數的文件或研究？

我要問的原因是我正在查看一個查詢，它在兼容模式 130 下性能下降，原因與對包含對 SUBSTRING() 呼叫的 WHERE 子句匹配的行數的估計值的更改有關。我糾正了查詢重寫的問題，但我想知道是否有人知道有關 SQL Server 2016 中此區域更改的任何文件。

展示程式碼如下。在此測試案例中，估計值非常接近，但準確度因數據而異。

在測試案例中，在兼容級別 120 中，SQL Server 似乎使用直方圖進行估計，而在兼容級別 130 中，SQL Server 似乎假設固定 10% 的表匹配。

CREATE DATABASE MyStringTestDB;
GO
USE MyStringTestDB;
GO
DROP TABLE IF EXISTS dbo.StringTest;
CREATE TABLE dbo.StringTest ( [TheString] varchar(15) );
GO
INSERT INTO dbo.StringTest
VALUES
( 'Y5_CLV' );
INSERT INTO dbo.StringTest
VALUES
( 'Y5_EG3' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_NE' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_PQT' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_T2V' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_TT4' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_ZKK' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_LW6' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_QO3' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_TZ7' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_UZZ' );

CREATE CLUSTERED INDEX IX_Clustered ON dbo.StringTest (TheString);

/* 
Uses fixed % for estimate; 1.1 rows estimated in this case.
   Plan for computation:
       CSelCalcFixedFilter (0.1) <----
           Selectivity: 0.1
*/
ALTER DATABASE MyStringTestDB SET compatibility_level = 130;
GO
SELECT * 
FROM dbo.StringTest 
WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);

/* 
Uses histogram to get estimate of 1
CSelCalcPointPredsFreqBased <----
     Distinct value calculation:
         CDVCPlanLeaf
             0 Multi-Column Stats, 1 Single-Column Stats, 0 Guesses
     Individual selectivity calculations:
         (none)
   Loaded histogram for column QCOL: [DBA].[dbo].[StringTest].TheString from stats with id 1
*/
ALTER DATABASE MyStringTestDB SET compatibility_level = 120;
GO
SELECT * 
FROM dbo.StringTest 
WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);

/*
-- Simpler rewrite; works fine in both compat levels and gets better estimate.
SELECT * 
FROM dbo.StringTest 
WHERE TheString LIKE 'ZZ[_]%'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);
*/

我不知道任何文件。我確實對此進行了調查並提出了一些意見，但是對於評論來說太長了。

10% 的估計並不總是降級。舉個例子。

TRUNCATE TABLE dbo.StringTest

INSERT INTO dbo.StringTest
SELECT TOP (1000000) 'ZZ_' + LEFT(NEWID(), 12)
FROM   master..spt_values v1,
      master..spt_values v2;

以及WHERE您問題中的條款。

WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'

該表包含一百萬行。它們都與謂詞匹配。在兼容級別 130 下，10% 的猜測產生 100,000 的估計值。在 120 行以下，估計行數為 1.03913。

120 行為使用直方圖，但僅用於獲取不同行的數量。在我的例子中，密度向量顯示為 1.039131E-06，它乘以表基數來獲得估計的行數。所有的值實際上都是不同的，但都與謂詞匹配。

跟踪query_optimizer_estimate_cardinality擴展事件顯示在 130 下有兩個不同的<StatsCollection Name="CStCollFilter"事件。第一個估計100,000。第二個載入直方圖並使用 CSelCalcPointPredsFreqBased/DistinctCountCalculator 來獲得 1.04 的估計值。第二個結果似乎未使用。

您觀察到的行為在 130 中並未始終如一地應用。我補充說，ORDER BY TheString希望這對於 130 估計器來說是一個明顯的勝利，因為 120 正在努力為一行提供記憶體授權，但這個微小的變化足以將估計的行數降低到1.03913 在 130 的情況下也是如此。

添加OPTION (QUERYRULEOFF SelectToFilter)會將進入排序的估計值恢復為 100,000，但記憶體授予不會增加，並且排序後的估計值仍然基於表不同的值。

類似地調整併行性的成本門檻值以便查詢獲得併行計劃在 130 的情況下足以恢復到較低的估計值。添加QUERYTRACEON 8757也會導致較低的估計值。看起來 10% 的估計只保留在瑣碎的計劃中。

您建議的重寫

WHERE TheString LIKE 'ZZ[_]%'

顯示出優於兩者的估計。這個的輸出是

 CSelCalcTrieBased

     Column: QCOL: [MyStringTestDB].[dbo].[StringTest].TheString

表明它使用了嘗試。有關這方面的更多資訊，請參見此處上方的字元串摘要統計部分。

但是，它與您的原始查詢不同。因為現在假定第一個實例_始終是第三個字元，而不是動態找到。

如果這個假設被硬編碼到您的原始查詢中

WHERE SUBSTRING(TheString, 1, 3) = 'ZZ_'

估計方法更改為CSelCalcHistogramComparison(INTERVAL)，估計的行數變得準確。

它能夠將其轉換為範圍

WHERE TheString >=  'ZZ_' AND TheString < ???

並使用直方圖估計值在該範圍內的行數。

然而，這僅適用於基數估計。LIKE更可取，因為它可以在執行時使用範圍搜尋。SUBSTRING(TheString, 1, 3)或LEFT(TheString, 3)不能。

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