Dlaczego funkcja LEN () źle nie docenia liczności w SQL Server 2014?

Mam tabelę z kolumną ciągów i predykatem, który sprawdza wiersze o określonej długości. W SQL Server 2014 widzę szacunkową wartość 1 wiersza bez względu na sprawdzaną długość. Daje to bardzo kiepskie plany, ponieważ w rzeczywistości są tysiące, a nawet miliony wierszy, a SQL Server decyduje się umieścić tę tabelę na zewnętrznej stronie zagnieżdżonej pętli.

Czy istnieje wyjaśnienie szacunku liczności 1.0003 dla SQL Server 2014, podczas gdy SQL Server 2012 szacuje 31 622 wierszy? Czy istnieje dobre obejście?

Oto krótkie odtworzenie problemu:

-- 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

Oto bardziej kompletny skrypt pokazujący dodatkowe testy

Przeczytałem również oficjalny dokument na temat programu SQL Server 2014 Cardinality Estimator , ale nie znalazłem tam niczego, co wyjaśniłoby sytuację.

Widzę, że w przypadku starszej wersji CE oszacowanie dotyczy 3,166228% wierszy - i jest to heurystyka „magiczna liczba” używana w predykatach kolumna = dosłownie (istnieją inne heurystyki oparte na konstrukcji predykatu - ale są LENowinięte wokół kolumny dla starsze wyniki CE odpowiadają tej strukturze zgadywania). Przykłady tego można zobaczyć w poście na temat domysłów selektywności w przypadku braku statystyk Joe Sack i oceny porównawczej Constant-Constant autorstwa Iana Jose.

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

Jeśli chodzi o nowe zachowanie CE, wygląda na to, że jest to teraz widoczne dla optymalizatora (co oznacza, że ​​możemy korzystać ze statystyk). Przeszedłem ćwiczenie patrzenia na dane wyjściowe kalkulatora poniżej i możesz spojrzeć na powiązane automatyczne generowanie statystyk jako wskaźnik:

-- 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

Niestety logika opiera się na oszacowaniu liczby różnych wartości, które nie są korygowane o efekt LENfunkcji.

Możliwe obejście

Możesz uzyskać oszacowanie oparte na trie w ramach obu modeli CE, przepisując LENjako LIKE:

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

Informacje na temat używanych flag śledzenia:

• 2363: pokazuje wiele informacji, w tym ładowane statystyki.
• 3604: drukuje dane wyjściowe poleceń DBCC na karcie komunikatów.

Czy istnieje wyjaśnienie szacunku liczności 1.0003 dla SQL 2014, podczas gdy SQL 2012 szacuje 31 622 wierszy?

Myślę @ Zane za odpowiedź obejmuje tę część całkiem dobrze.

Czy istnieje dobre obejście?

Możesz spróbować utworzyć nietrwałą kolumnę obliczaną LEN(cust_nbr)i (opcjonalnie) utworzyć indeks nieklastrowany na tej kolumnie obliczonej. To powinno zapewnić ci dokładne statystyki.

Zrobiłem kilka testów i oto, co znalazłem:

• Statystyki zostały utworzone automatycznie w nieobciążonej kolumnie obliczeniowej, gdy nie zdefiniowano w niej żadnego indeksu.
• Dodanie indeksu nieklastrowego do kolumny obliczeniowej nie tylko nie pomogło, ale trochę pogorszyło wydajność. Nieco wyższy czas pracy procesora i czasu, który upłynął. Nieco wyższy szacunkowy koszt (cokolwiek to jest warte).
• Utworzenie kolumny obliczanej jako PERSISTED(bez indeksu) było lepsze niż pozostałe dwie odmiany. Szacowane rzędy były dokładniejsze. Procesor i upływający czas były lepsze (zgodnie z oczekiwaniami, ponieważ nie musiał obliczać niczego na wiersz).
• Nie mogłem utworzyć indeksu filtrowanego ani statystyk filtrowanych w kolumnie obliczanej (ponieważ jest obliczany), nawet jeśli był PERSISTED:-(