Zmiany w szacunkach dotyczących predykatów zawierających SUBSTRING () w SQL Server 2016?

Czy istnieje dokumentacja lub badanie dotyczące zmian w SQL Server 2016 dotyczących szacowania liczności dla predykatów zawierających SUBSTRING () lub inne funkcje łańcuchowe?

Powodem, dla którego pytam, jest to, że szukałem zapytania, którego wydajność uległa pogorszeniu w trybie zgodności 130, a przyczyną była zmiana szacunku liczby wierszy pasujących do klauzuli WHERE, która zawierała wywołanie SUBSTRING (). Rozwiązałem problem z przepisaniem zapytania, ale zastanawiam się, czy ktoś zna jakąkolwiek dokumentację dotyczącą zmian w tym obszarze w SQL Server 2016.

Kod demonstracyjny znajduje się poniżej. Szacunki są bardzo bliskie w tym przypadku testowym, ale dokładność różni się w zależności od danych.

W przypadku testowym na poziomie zgodności 120 SQL Server wydaje się używać histogramu do oszacowania, podczas gdy na poziomie zgodności 130 SQL Server wydaje się przyjmować stałe 10% zgodności tabeli.

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);
*/

Nie znam żadnej dokumentacji. Przyglądałem się temu i poczyniłem kilka spostrzeżeń, które są jednak zbyt długie, aby można je było wypowiedzieć.

Szacunek 10% nie zawsze oznacza degradację. Weź następujący przykład.

TRUNCATE TABLE dbo.StringTest

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

i WHEREklauzula w twoim pytaniu.

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

Tabela zawiera milion wierszy. Wszystkie pasują do predykatu. Przy poziomie zgodności 130, 10% trafności daje szacunkową wartość 100 000. Poniżej 120 szacuje się, że wiersze wynoszą 1,03913.

120 zachowań używa histogramu, ale tylko w celu uzyskania liczby różnych wierszy. Wektor gęstości w moim przypadku pokazuje 1.039131E-06 i jest on mnożony przez liczność tabeli, aby uzyskać szacowaną liczbę wierszy. Wszystkie wartości są w rzeczywistości różne, ale wszystkie pasują do predykatu.

Śledzenie query_optimizer_estimate_cardinalityzdarzenia rozszerzonego pokazuje, że poniżej 130 lat istnieją dwa różne <StatsCollection Name="CStCollFilter"zdarzenia. Pierwszy szacuje 100 000. Drugi ładuje histogram i używa CSelCalcPointPredsFreqBased / DistinctCountCalculator, aby uzyskać oszacowanie 1,04. Ten drugi wynik wydaje się nieużywany.

Zachowanie, które zaobserwowałeś, nie jest konsekwentnie stosowane w 130. Dodałem ORDER BY TheStringoczekiwanie, że będzie to wyraźna wygrana dla estymatora 130, ponieważ 120 walczy z przyznaniem pamięci dla jednego rzędu, ale ta niewielka zmiana była wystarczająca, aby sprowadzić szacowane wiersze do 1.03913 również w przypadku 130.

Dodanie OPTION (QUERYRULEOFF SelectToFilter)powoduje odwrócenie oszacowania przechodzącego do sortowania do 100 000, ale przydział pamięci nie wzrasta, a oszacowania wychodzące z sortowania są nadal oparte na odrębnych wartościach w tabeli.

Podobnie dostosowanie progu kosztów dla równoległości, aby zapytanie otrzymało plan równoległy, było wystarczające w przypadku 130, aby powrócić do niższych wartości szacunkowych. Dodanie QUERYTRACEON 8757powoduje również niższe oszacowanie. Wygląda na to, że szacunek 10% jest zachowany tylko dla trywialnych planów.

Proponowane przepisanie z

WHERE TheString LIKE 'ZZ[_]%'

Pokazuje znacznie lepsze oceny niż oba. Wynikiem tego jest

  CSelCalcTrieBased

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

Pokazuje, że używał prób . Więcej informacji na ten temat znajduje się w sekcji Statystka ciąg tuż nad tutaj .

Nie jest to jednak to samo, co oryginalne zapytanie. Jako pierwszy przypadek _zakłada się, że zawsze będzie to trzecia postać, a nie zostanie odnaleziona dynamicznie.

Jeśli to założenie jest zapisane w oryginalnym zapytaniu

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

Metoda szacowania zmienia się, CSelCalcHistogramComparison(INTERVAL)a szacowane wiersze stają się dokładne.

Jest w stanie przekonwertować to na zakres

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

i użyj histogramu, aby oszacować liczbę wierszy z wartościami w tym zakresie.

Dotyczy to jednak tylko oszacowania liczności. LIKEjest preferowane, ponieważ może korzystać z wyszukiwania zasięgu w czasie wykonywania. SUBSTRING(TheString, 1, 3)albo LEFT(TheString, 3)nie może.