Oszacowanie liczności dla operatora LIKE (zmienne lokalne)

Miałem wrażenie, że podczas korzystania z LIKEoperatora we wszystkich optymalizacjach dla nieznanych scenariuszy zarówno starsze, jak i nowe CE używają szacunku 9% (zakładając, że odpowiednie statystyki są dostępne, a optymalizator zapytań nie musi uciekać się do domysłów selektywności).

Podczas wykonywania poniższego zapytania względem bazy danych kredytów otrzymuję różne szacunki w ramach różnych CE. W ramach nowego CE otrzymuję szacunkową liczbę 900 wierszy, której się spodziewałem, w ramach wcześniejszej wersji CE otrzymuję szacunkową wartość 241,416 i nie jestem w stanie ustalić, w jaki sposób otrzymano tę ocenę. Czy ktoś jest w stanie rzucić jakieś światło?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

W moim scenariuszu mam już bazę danych kredytów ustawioną na poziom zgodności 120, dlatego w drugim zapytaniu używam flag śledzenia, aby wymusić starszą wersję CE, a także podać informacje o tym, jakie statystyki są używane / uwzględniane przez optymalizator zapytania. Widzę, że używane są statystyki kolumnowe dotyczące „nazwiska”, ale nadal nie jestem w stanie ustalić, w jaki sposób szacowana jest wartość 241,416.

Nie mogłem znaleźć w Internecie niczego poza tym artykułem Itzik Ben-Gan , który stwierdza: „Gdy korzystam z predykatu LIKE we wszystkich optymalizacjach dla nieznanych scenariuszy, zarówno starsze, jak i nowe CE używają szacunków 9 procent”. Informacje w tym poście wydają się niepoprawne.

Zgadywanie LIKE w twoim przypadku opiera się na:

• G: Standardowe zgadywanie 9% ( sqllang!x_Selectivity_Like)
• M: Współczynnik 6 (liczba magiczna)
• D: Średnia długość danych w bajtach (ze statystyk), zaokrąglona w dół do liczby całkowitej

W szczególności sqllang!CCardUtilSQL7::ProbLikeGuesswykorzystuje:

Selectivity (S) = G / M * LOG(D)

Uwagi:

• LOG(D)Termin pominięty, jeśli Dwynosi od 1 do 2.
• Jeśli Djest mniejsza niż 1 (w tym w przypadku braków lub NULLstatystyk):
  D = FLOOR(0.5 * maximum column byte length)

Ten rodzaj dziwactwa i złożoności jest dość typowy dla oryginalnego CE.

W przykładowym pytaniu średnia długość wynosi 5 (5,6154 od DBCC SHOW_STATISTICSzaokrąglenia w dół):

Szacunek = 10000 * (0,09 / 6 * LOG (5)) = 241,416

Inne przykładowe wartości:

 D   = Oszacuj za pomocą wzoru na S.
 15 = 406,208
 14 = 395,859
 13 = 384,742
 12 = 372,736
 11 = 359,684
 10 = 345,388
 09 = 329,584
 08 = 311,916
 07 = 291,887
 06 = 268,764
 05 = 241,416
 04 = 207,944
 03 = 164,792
 02 = 150 000 (LOG nieużywany)
 01 = 150 000 (LOG nieużywany)
 00 = 291,887 (LOG 7) / * FLOOR (0,5 * 15) [15, ponieważ nazwisko to varchar (15)] * /

Stanowisko testowe

DECLARE
    @CharLength integer = 5, -- Set length here
    @Counter integer = 1;

CREATE TABLE #T (c1 varchar(15) NULL);

-- Add 10,000 rows
SET NOCOUNT ON;
SET STATISTICS XML OFF;

BEGIN TRANSACTION;
WHILE @Counter <= 10000
BEGIN
    INSERT #T (c1) VALUES (REPLICATE('X', @CharLength));
    SET @Counter = @Counter + 1;
END;
COMMIT TRANSACTION;

SET NOCOUNT OFF;
SET STATISTICS XML ON;

-- Test query
DECLARE @Like varchar(15);
SELECT * FROM #T AS T 
WHERE T.c1 LIKE @Like;

DROP TABLE #T;

Testowałem na SQL Server 2014 ze starszą wersją CE i nie otrzymałem 9% jako oszacowanie liczności. Nie mogłem znaleźć niczego dokładnego w Internecie, więc wykonałem testy i znalazłem model, który pasuje do wszystkich testowanych przypadków, ale nie jestem pewien, czy jest kompletny.

W modelu, który znalazłem, oszacowanie pochodzi z liczby wierszy w tabeli, średniej długości klucza statystyki dla filtrowanej kolumny, a czasem długości typu danych filtrowanej kolumny. Istnieją dwa różne wzory używane do oszacowania.

Jeśli FLOOR (średnia długość klucza) = 0, wówczas formuła szacowania ignoruje statystyki kolumny i tworzy oszacowanie na podstawie długości typu danych. Testowałem tylko z VARCHAR (N), więc możliwe jest, że istnieje inna formuła dla NVARCHAR (N). Oto wzór na VARCHAR (N):

(oszacowanie wiersza) = (wiersze w tabeli) * (-0,004869 + 0,032649 * log10 (długość typu danych))

To ma bardzo ładne dopasowanie, ale nie jest idealnie dokładne:

Oś x to długość typu danych, a oś y to liczba szacowanych wierszy dla tabeli z 1 milionem wierszy.

Optymalizator zapytań użyłby tej formuły, jeśli nie masz statystyk dotyczących kolumny lub jeśli kolumna ma wystarczającą liczbę wartości NULL, aby doprowadzić średnią długość klucza do wartości poniżej 1.

Załóżmy na przykład, że masz tabelę z 150 tys. Wierszy z filtrowaniem na VARCHAR (50) i bez statystyk kolumn. Prognozowane oszacowanie wiersza to:

150000 * (-0,004869 + 0,032649 * log10 (50)) = 7590,1 wierszy

SQL, aby to przetestować:

CREATE TABLE X_CE_LIKE_TEST_1 (
STRING VARCHAR(50)
);

CREATE STATISTICS X_STAT_CE_LIKE_TEST_1 ON X_CE_LIKE_TEST_1 (STRING) WITH NORECOMPUTE;

WITH
    L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
    L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
    L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
    L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B CROSS JOIN L2 C),
    NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L4)  
    INSERT INTO X_CE_LIKE_TEST_1 WITH (TABLOCK) (STRING)
    SELECT TOP (150000) 'ZZZZZ'
    FROM NUMS
    ORDER BY NUM;

DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM X_CE_LIKE_TEST_1
WHERE STRING LIKE @LastName;

SQL Server podaje szacunkową liczbę wierszy 7242.47, co jest rodzajem zamknięcia.

Jeśli FLOOR (średnia długość klucza)> = 1, wówczas używana jest inna formuła oparta na wartości FLOOR (średnia długość klucza). Oto tabela niektórych wartości, które próbowałem:

1    1.5%
2    1.5%
3    1.64792%
4    2.07944%
5    2.41416%
6    2.68744%
7    2.91887%
8    3.11916%
9    3.29584%
10   3.45388%

Jeśli FLOOR (średnia długość klucza) <6, skorzystaj z powyższej tabeli. W przeciwnym razie użyj następującego równania:

(oszacowanie wiersza) = (wiersze w tabeli) * (-0,003381 + 0,034539 * log10 (FLOOR (średnia długość klucza)))

Ten ma lepsze dopasowanie niż drugi, ale wciąż nie jest idealnie dokładny.

Oś X to średnia długość klucza, a oś Y to liczba szacowanych wierszy dla tabeli z 1 milionem wierszy.

Aby podać inny przykład, załóżmy, że masz tabelę z 10 tys. Wierszy o średniej długości klucza 5,5 dla statystyk w filtrowanej kolumnie. Szacunkowa wartość rzędu to:

10000 * 0,241416 = 241,416 wierszy.

SQL, aby to przetestować:

CREATE TABLE X_CE_LIKE_TEST_2 (
STRING VARCHAR(50)
);

WITH
    L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
    L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
    L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
    L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B CROSS JOIN L2 C),
    NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L4)  
    INSERT INTO X_CE_LIKE_TEST_2 WITH (TABLOCK) (STRING)
    SELECT TOP (10000) 
    CASE 
      WHEN NUM % 2 = 1 THEN REPLICATE('Z', 5) 
      ELSE REPLICATE('Z', 6)
    END
    FROM NUMS
    ORDER BY NUM;

CREATE STATISTICS X_STAT_CE_LIKE_TEST_2 ON X_CE_LIKE_TEST_2 (STRING) 
WITH NORECOMPUTE, FULLSCAN;

DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM X_CE_LIKE_TEST_2
WHERE STRING LIKE @LastName;

Szacunkowy wiersz to 241,416, który pasuje do tego, co masz w pytaniu. Wystąpiłby błąd, gdybym użył wartości spoza tabeli.

Modele tutaj nie są idealne, ale myślę, że całkiem dobrze ilustrują ogólne zachowanie.