主页 / dba / 问题 / 151995
Accepted
James Lupolt
Asked: 2016-10-12 09:10:46 +0800 CST

对 SQL Server 2016 中包含 SUBSTRING() 的谓词的估计值的更改？

  • 772

是否有关于 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);
*/
sql-server optimization

1 个回答

  1. Best Answer

    我不知道有任何文件。我确实对此进行了调查并进行了一些观察，但是对于评论来说太长了。

    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 为一行的内存授予而苦苦挣扎，但这个微小的变化足以将估计的行数降低到130 的情况下也是 1.03913。

    添加OPTION (QUERYRULEOFF SelectToFilter)会将进入排序的估计值恢复为 100,000，但内存授予不会增加，并且排序后的估计值仍然基于表的不同值。

    在此处输入图像描述

    类似地调整并行度的成本阈值，以便查询获得并行计划足以在 130 的情况下恢复到较低的估计。添加QUERYTRACEON 8757也会导致较低的估计。看起来 10% 的估计只保留用于琐碎的计划。

    您建议的重写

    WHERE TheString LIKE 'ZZ[_]%'

    显示出比两者都高得多的估计。这个的输出是

      CSelCalcTrieBased

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

    表明它使用了try。有关此的更多信息，请参见此处上方的字符串摘要统计部分。

    但是，它与您的原始查询不同。由于_现在假定第一个实例始终是第三个字符，而不是动态找到的。

    如果此假设被硬编码到您的原始查询中

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

    估计方法更改为CSelCalcHistogramComparison(INTERVAL)，估计的行变得准确。

    它能够将其转换为一个范围

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

    并使用直方图估计值在该范围内的行数。

    然而，这仅适用于基数估计。LIKE更可取，因为它可以在运行时使用范围搜索。SUBSTRING(TheString, 1, 3)或者LEFT(TheString, 3)不能。

    • 8

相关问题