Dziwne zachowanie przy rozmiarach próbek do aktualizacji statystyk

Bawiłem się badaniem progów próbkowania z aktualizacjami statystyk na SQL Server (2012) i zauważyłem dziwne zachowanie. Zasadniczo liczba próbkowanych wierszy wydaje się zmieniać w pewnych okolicznościach - nawet przy tym samym zestawie danych.

Uruchomię to zapytanie:

--Drop table if exists
IF (OBJECT_ID('dbo.Test')) IS NOT NULL DROP TABLE dbo.Test;

--Create Table for Testing
CREATE TABLE dbo.Test(Id INT IDENTITY(1,1) CONSTRAINT PK_Test PRIMARY KEY CLUSTERED, TextValue VARCHAR(20) NULL);

--Insert enough data so we have more than 8Mb (the threshold at which sampling kicks in)
INSERT INTO dbo.Test(TextValue) 
SELECT TOP 1000000 'blahblahblah'
FROM sys.objects a, sys.objects b, sys.objects c, sys.objects d;  

--Create Index on TextValue
CREATE INDEX IX_Test_TextValue ON dbo.Test(TextValue);

--Update Statistics without specifying how many rows to sample
UPDATE STATISTICS dbo.Test IX_Test_TextValue;

--View the Statistics
DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER;

Kiedy patrzę na wynik SHOW_STATISTICS, stwierdzam, że „Wiersze próbkowane” zmieniają się przy każdym pełnym wykonaniu (tj. Tabela jest upuszczana, odtwarzana i ponownie wypełniana).

Na przykład:

Próbki wierszy

• 318618
• 319240
• 324198
• 314154

Oczekiwałem, że ta liczba będzie taka sama za każdym razem, gdy tabela będzie identyczna. Nawiasem mówiąc, nie dostaję tego zachowania, jeśli po prostu usunę dane i włożę je ponownie.

To nie jest krytyczne pytanie, ale chciałbym zrozumieć, co się dzieje.

tło

Dane dla obiektu statystyki są gromadzone za pomocą instrukcji formularza:

SELECT 
    StatMan([SC0], [SC1], [SB0000]) 
FROM 
(
    SELECT TOP 100 PERCENT 
        [SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
    FROM 
    (
        SELECT 
            [TextValue] AS [SC0], 
            [Id] AS [SC1] 
        FROM [dbo].[Test] 
            TABLESAMPLE SYSTEM (2.223684e+001 PERCENT) 
            WITH (READUNCOMMITTED) 
    ) AS _MS_UPDSTATS_TBL_HELPER 
    ORDER BY 
        [SC0], 
        [SC1], 
        [SB0000] 
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

Możesz zebrać to oświadczenie za pomocą Rozszerzonych zdarzeń lub Profilera (SP:StmtCompleted ).

Kwerendy generowania statystyk często uzyskują dostęp do tabeli podstawowej (zamiast indeksu nieklastrowanego), aby uniknąć grupowania wartości, które naturalnie występują na nieklastrowanych stronach indeksu.

Liczba próbkowanych wierszy zależy od liczby całych stron wybranych do próbkowania. Każda strona tabeli jest albo wybrana, albo nie. Wszystkie wiersze na wybranych stronach przyczyniają się do statystyk.

Losowe liczby

SQL Server używa generatora liczb losowych, aby zdecydować, czy strona się kwalifikuje, czy nie. Generatorem zastosowanym w tym przypadku jest generator liczb losowych Lehmera z wartościami parametrów przedstawionymi poniżej:

X dalej = X ziarno * 7 5 mod (2 31 )

Wartość jest obliczana jako suma:Xseed

• Niska liczba całkowita części biginttabeli podstawowej ( ) partition_idnp

  SELECT
      P.[partition_id] & 0xFFFFFFFF
  FROM sys.partitions AS P
  WHERE
      P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
      AND P.index_id = 1;

• Wartość określona w REPEATABLEklauzuli

  • Dla próbki UPDATE STATISTICS,REPEATABLE wartość 1.
  • Ta wartość jest ujawniana w m_randomSeedelemencie informacji wewnętrznych debugowania metody dostępu wyświetlanych w planach wykonania, gdy flaga śledzenia 8666 jest włączona, na przykład<Field FieldName="m_randomSeed" FieldValue="1" />

W przypadku programu SQL Server 2012 obliczenia te występują w sqlmin!UnOrderPageScanner::StartScan:

mov     edx,dword ptr [rcx+30h]
add     edx,dword ptr [rcx+2Ch]

gdzie pamięć at [rcx+30h]zawiera 32 niskie bity identyfikatora partycji, a pamięć at [rcx+2Ch]zawieraREPEATABLE używaną wartość.

Generator liczb losowych jest inicjowany później tą samą metodą, wywołując sqlmin!RandomNumGenerator::Init, gdzie instrukcja:

imul    r9d,r9d,41A7h

... rozmnaża się ziarno o 41A7sześciokątnych (16807 dziesiętny = 7 ⁵ ), jak pokazano w równaniu powyżej.

Później losowe liczby (dla poszczególnych stron) są generowane przy użyciu tego samego podstawowego kodu wstawionego do sqlmin!UnOrderPageScanner::SetupSubScanner .

StatMan

Dla przykładowego StatManzapytania pokazanego powyżej zostaną zebrane te same strony, co w przypadku instrukcji T-SQL:

SELECT 
    COUNT_BIG(*) 
FROM dbo.Test AS T 
    TABLESAMPLE SYSTEM (2.223684e+001 PERCENT)  -- Same sample %
    REPEATABLE (1)                              -- Always 1 for statman
    WITH (INDEX(0));                            -- Scan base object

To dopasuje wynik:

SELECT 
    DDSP.rows_sampled
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.[object_id], S.stats_id) AS DDSP
WHERE 
    S.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND S.[name] = N'IX_Test_TextValue';

Obudowa krawędzi

Jedną konsekwencją użycia generatora liczb losowych MINSTD Lehmera jest to, że nie należy stosować wartości początkowych zero i int.max, ponieważ spowoduje to, że algorytm wygeneruje sekwencję zer (wybieranie każdej strony).

Kod wykrywa zero i używa wartości z systemowego „zegara” jako źródła w tym przypadku. Nie robi to samo, jeśli ziarno ma wartość int.max ( 0x7FFFFFFF= 2 ³¹ - 1).

Możemy zaprojektować ten scenariusz, ponieważ początkowe ziarno jest obliczane jako suma niskich 32 bitów identyfikatora partycji i REPEATABLEwartości. REPEATABLEWartość, która spowoduje, że materiał siewny jest int.max i dlatego każda strona jest wybrana próbka:

SELECT
    0x7FFFFFFF - (P.[partition_id] & 0xFFFFFFFF)
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND P.index_id = 1;

Przekształcenie tego w pełny przykład:

DECLARE @SQL nvarchar(4000) = 
    N'
    SELECT
        COUNT_BIG(*) 
    FROM dbo.Test AS T 
        TABLESAMPLE (0 PERCENT) 
        REPEATABLE (' +
        (
            SELECT TOP (1)
                CONVERT(nvarchar(11), 0x7FFFFFFF - P.[partition_id] & 0xFFFFFFFF)
            FROM sys.partitions AS P
            WHERE
                P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
                AND P.index_id = 1
        ) + ')
        WITH (INDEX(0));';

PRINT @SQL;
--EXECUTE (@SQL);

Spowoduje to zaznaczenie każdego wiersza na każdej stronie, niezależnie od TABLESAMPLEklauzuli (nawet zero procent).

To doskonałe pytanie! Zacznę od tego, co wiem na pewno, a następnie przejdę do spekulacji. Wiele szczegółów na ten temat w moim blogu tutaj .

Próbkowane aktualizacje statystyk wykorzystują TABLESAMPLEza kulisami. Dokumentacja na ten temat online jest dość łatwa. Uważam jednak, że nie jest dobrze znane, że wiersze zwrócone przez TABLESAMPLEczęściowo zależą hobt_idod obiektu. Po upuszczeniu i odtworzeniu obiektu otrzymujesz nowyhobt_id więc wiersze zwracane przez losowe próbkowanie są różne.

Jeśli usuniesz i ponownie wstawisz dane, hobt_id pozostaną takie same. Tak długo, jak dane są ułożone w taki sam sposób na dysku (skanowanie kolejności przydziału zwraca te same wyniki w tej samej kolejności), próbkowane dane nie powinny się zmienić.

Możesz także zmienić liczbę próbkowanych wierszy, odbudowując indeks klastrowany w tabeli. Na przykład:

UPDATE STATISTICS dbo.Test IX_Test_TextValue;

DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER; -- 273862 rows

ALTER INDEX PK_Test on Test REBUILD;

UPDATE STATISTICS dbo.Test IX_Test_TextValue;

DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER; -- 273320 rows

Co do tego, dlaczego tak się dzieje, uważam, że dzieje się tak, ponieważ SQL Server skanuje indeks klastrowany zamiast indeksu nieklastrowanego podczas gromadzenia próbkowanych statystyk w indeksie. Myślę również, że istnieje ukryta (dla tych z nas, którzy śledzą ukryte zapytania aktualizacji statystyk) wartość REPEATABLEużywana z TABLESAMPLE. Nie udowodniłem tego, ale wyjaśnia, dlaczego próbka histogramu i wierszy zmienia się wraz z przebudową indeksu klastrowego.

Zapomniałem, jak działał TABLESAMPLE pod względem przypisywania losowego prawdopodobieństwa na stronę. - Martin Smith

Widziałem to w Inside Microsoft SQL Server 2008: T-SQL Querying autorstwa Itzika Ben-Gana i nie mogę dodać go jako komentarza, więc zamieszczam go tutaj, myślę, że jest interesujący również dla innych:

Zobacz także Próbkowanie przy użyciu TABLESAMPLE autorstwa Roji. P. Thomas.