主页 / dba / 问题 / 120064
Accepted
Geoff Patterson
Asked: 2015-11-05 06:24:23 +0800 CST

为什么 LEN() 函数严重低估了 SQL Server 2014 中的基数？

  • 772

我有一个带有字符串列和一个谓词的表，用于检查具有一定长度的行。在 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 的白皮书，但没有找到任何可以澄清情况的内容。

sql-server sql-server-2014

2 个回答

  1. Best Answer

    对于旧版 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);

    LIKE计划

    有关使用的跟踪标志的信息：

    • 2363：显示很多信息，包括正在加载的统计信息。
    • 3604：将 DBCC 命令的输出打印到消息选项卡。
    • 20

  2. SQL 2014 的基数估计为 1.0003 而 SQL 2012 估计为 31,622 行，是否有解释？

    我认为@Zane 的回答很好地涵盖了这一部分。

    有没有好的解决方法？

    您可以尝试为该计算列创建一个非持久性计算列LEN(cust_nbr)并（可选）创建一个非聚集索引。这应该可以为您提供准确的统计数据。

    我做了一些测试，这是我发现的：

    • 当未在其上定义索引时，会在非持久计算列上自动创建统计信息。
    • 在计算列上添加非聚集索引不仅没有帮助，实际上还稍微损害了性能。CPU 和运行时间稍高。估计成本略高（无论值多少）。
    • 将计算列设置为PERSISTED（无索引）比其他两个变体更好。估计行数更准确。CPU 和运行时间更好（正如预期的那样，因为它不必每行计算任何东西）。
    • 我无法在计算列上创建过滤索引或过滤统计信息（由于它正在计算），即使它是PERSISTED:-(
    • 13

相关问题