Ustawiać

Mam problem ze zrozumieniem oszacowania liczności. Oto moja konfiguracja testowa:

wersja 2010 bazy danych przepełnienia stosu
SQL Server 2017 CU15 + GDR (KB4505225) - 14.0.3192.2
nowy CE (poziom zgodności 140)

Mam ten proc:

USE StackOverflow2010;
GO

CREATE OR ALTER PROCEDURE #sp_PostsByCommentCount
    @CommentCount int
AS
BEGIN
    SELECT * 
    FROM dbo.Posts p
    WHERE 
        p.CommentCount = @CommentCount
    OPTION (RECOMPILE); 
END;
GO

W dbo.Poststabeli nie ma indeksów ani statystyk nieklastrowanych (włączony jest indeks klastrowany Id).

Pytając o szacunkowy plan tego, „szacowane wiersze” wychodzące dbo.Poststo 1 934,99:

EXEC #sp_PostsByCommentCount @CommentCount = 51;

Następujący obiekt statystyczny został automatycznie utworzony, gdy poprosiłem o plan szacunkowy:

DBCC SHOW_STATISTICS('dbo.Posts', [_WA_Sys_00000006_0519C6AF]);

Najważniejsze z tego są:

Statystyki mają dość niski współczynnik próbkowania wynoszący 1,81% (67 796/3 744 192)
Zastosowano tylko 31 kroków histogramu
Wartość „Cała gęstość” wynosi 0.03030303(próbkowano 33 różne wartości)
Ostatni RANGE_HI_KEYna histogramie to 50, z EQ_ROWS1

Pytanie

Przekazanie dowolnej wartości większej niż 50 (do 2 147 483 647 włącznie) powoduje oszacowanie wiersza 1 934,99. Jakie obliczenia lub wartości stosuje się do sporządzenia tego oszacowania? Nawiasem mówiąc, starszy estymator liczności tworzy oszacowanie 1 rzędu.

Co próbowałem

Oto niektóre teorie, które miałem, rzeczy, które wypróbowałem, lub dodatkowe informacje, które udało mi się odkryć, patrząc na to.

Wektor gęstości

Początkowo myślałem, że będzie to wektor gęstości, taki sam, jak gdybym użył OPTION (OPTIMIZE FOR UNKNOWN). Ale wektor gęstości dla tego obiektu statystyk wynosi 3,744,192 * 0,03030303 = 113,460, więc to nie to.

Rozszerzone wydarzenia

Próbowałem uruchomić sesję Extended Event, która zgromadziła to query_optimizer_estimate_cardinalitywydarzenie (o czym dowiedziałem się z posta na blogu Paula White'a Oszacowanie liczności: łączenie statystyk zagęszczenia ), i otrzymałem takie ciekawe ciekawostki:

<CalculatorList>
  <FilterCalculator CalculatorName="CSelCalcColumnInInterval" Selectivity="-1.000" 
                    CalculatorFailed="true" TableName="[p]" ColumnName="CommentCount" />

  <FilterCalculator CalculatorName="CSelCalcAscendingKeyFilter" Selectivity="0.001" 
                    TableName="[p]" ColumnName="CommentCount" UseAverageFrequency="true" 
                    StatId="4" />
</CalculatorList>

Wygląda więc na to, że CSelCalcAscendingKeyFilterużyto kalkulatora (drugi mówi, że się nie udało, cokolwiek to znaczy). Ta kolumna nie jest kluczem ani unikalna, ani niekoniecznie wstępująca, ale cokolwiek.

Wykonanie googlingu tego terminu doprowadziło mnie do niektórych postów na blogu:

Te posty wskazują, że nowe CE opierają te szacunki poza histogramem na kombinacji wektora gęstości i licznika modyfikacji statystyki. Niestety, wykluczyłem już wektor gęstości (chyba ?!), a licznik modyfikacji wynosi zero (nasys.dm_db_stats_properties każdym razie).

Flagi śledzenia

Forrest zasugerował włączenie TF 2363, aby uzyskać więcej informacji na temat procesu szacowania. Myślę, że najbardziej istotną rzeczą z tego wyniku jest:

Plan for computation:

  CSelCalcAscendingKeyFilter(avg. freq., QCOL: [p].CommentCount)

Selectivity: 0.000516798

To przełom (dzięki, Forrest!): Ta 0.000516798liczba (która wydaje się nieprzydatnie zaokrąglona w XESelectivity="0.001" powyższym atrybucie ) pomnożona przez liczbę wierszy w tabeli jest szacunkiem, którego szukałem (1 934,99).

Prawdopodobnie brakuje mi czegoś oczywistego, ale nie byłem w stanie odtworzyć, jak ta wartość selektywności jest wytwarzana w CSelCalcAscendingKeyFilterkalkulatorze.

— Josh Darnell
źródło

Na podstawie moich testów oszacowanie liczności poza granicami jest po prostu pierwiastkiem kwadratowym liczby wierszy, ograniczonym poniżej przez liczbę dodanych wierszy od ostatniej aktualizacji statystyk i ograniczoną powyżej przez średnią liczbę wierszy na wartość.

W twoim przypadku 1 934,99 = SQRT (3744192)

Konfiguracja testowa poniżej:

--setup
USE TestDB
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS OFF
GO

DROP TABLE IF EXISTS dbo.Hist

CREATE TABLE dbo.Hist (
ID int identity primary key,
Num int
)

INSERT dbo.Hist
SELECT TOP 300
(ROW_NUMBER() OVER(ORDER BY(SELECT 1/0)))%3
FROM master..spt_values a
CROSS JOIN master..spt_values b

--Get estimated plan
--don't forget to run right after setup to auto-create stats
SELECT *
FROM dbo.Hist
WHERE Num = 1000

--gradually add rows, then rerun estimate above
INSERT dbo.Hist
SELECT TOP 100
-1
FROM master..spt_values a

--I sure hope you weren't testing this in prod (cleanup)
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
GO

Zaskakująco nawet szacunki wierszy zostały wygenerowane z tego podejścia: 20 przy 400 całkowitych rzędach, 30 przy 900, 40 przy 1600 itd.

Jednak po 10 000 szacowana wartość wiersza wynosi maksymalnie 100, czyli liczba wierszy na wartość w istniejących statystykach. Dodanie tylko 10 wierszy ustawi wartość szacunkową na 10, ponieważ sqrt (300)> 10.

Tak więc szacunki można wyrazić za pomocą tego wzoru:

Estimate = MIN(SQRT(AC), MIN(AR, MC))

Zauważ, że jeśli próbkuje się statystyki, MC nie jest brane pod uwagę. Tak więc formuła staje się:

Estimate = MIN(SQRT(AC), AR))

Gdzie

MC to „liczba modyfikacji” (liczba modyfikacji od czasu utworzenia statystyk)
AC to „skorygowana liczność” (liczba wierszy ze statystyk plus MC),
AR to średnia liczba wierszy na wartość (liczba wierszy ze statystyki podzielona przez różne wartości w kolumnie)

Formuły tych szacunków i inne szczegóły dotyczące kalkulatora można znaleźć w tym poście na blogu: Analiza szacunków z kalkulatora CSelCalcAscendingKeyFilter

— Forrest
źródło

Oszacowanie liczności poza histogramem