Czy powinienem wyłączyć „statystyki automatycznych aktualizacji” w scenariuszu hurtowni danych?

Mam 200 GB hurtowni danych w SQL Server.

Występują naprawdę powolne czasy wykonywania niektórych zapytań; na przykład 12 godzin na proste deletezapytanie z inner join.

Po przeprowadzeniu badań z planami wykonania zaktualizowałem statystyki 2 tabel biorących udział w zapytaniu, korzystając z tej WITH FULLSCANopcji.

Zapytanie jest teraz wykonywane w mniej niż sekundę, więc wygląda na to, że statystyki nie były aktualne.

Rozważam wyłączenie auto update statisticsw bazie danych i uruchomienie UPDATE STATISTICSręczne po załadowaniu hurtowni danych. Hurtownia danych jest ładowana stopniowo ze źródłowego systemu ERP codziennie, w nocy.

Czy mam rację, zakładając, że auto update statisticsscenariusze hurtowni danych nie są tak naprawdę przydatne? Czy zamiast tego sensowniej jest ręcznie aktualizować statystyki po załadowaniu danych?

sql-server data-warehouse statistics

— saso
źródło

To bardzo dobra lektura na temat statystyk: simple-talk.com/sql/performance/… prowadzimy również codzienne zadanie, używając rozwiązania ola.hallengren.com/... w celu aktualizacji statystyk na 1 TB bazy danych. Nie wyłączyłbym opcji statystyk automatycznych aktualizacji.

— Joy Walker,

Wiele zależy od tego, ile rekordów znajduje się w twoich tabelach i ile dodajesz partią. W tabeli rzędów 1b, w której dodajesz 20 milionów wierszy na noc, twoje statystyki będą aktualizowane co około 10 dni, co nie jest świetne.

— JNK,

Po prostu FYI - dostępna jest flaga śledzenia (2371), która zmienia próg aktualizacji statystyk ze stałej 20% na dynamiczną stopę procentową. Zobacz więcej tutaj: blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/…

— DaniSQL,

Bardzo pouczające linki, dzięki! @JNK: Tak, to duży DB. Stolik pomostowy ma ponad 300 mln rzędów i zależnie od dnia wstawiamy codziennie od 1 do 10 metrów. Przyjrzymy się bliżej statystykom, jak zaproponowano w odpowiedzi poniżej.

— saso,

Oto oficjalny dokument na temat automatycznej aktualizacji statystyk . Oto najważniejsze punkty w stosunku do automatycznych aktualizacji statystyk:

Rozmiar tabeli spadł z 0 do> 0 wierszy (test 1).

Liczba wierszy w tabeli, gdy zebrano statystyki, wynosiła 500 lub mniej, a colmodctr kolumny wiodącej obiektu statystycznego zmienił się od tego czasu o ponad 500 (test 2).

Tabela miała ponad 500 wierszy podczas gromadzenia statystyk, a colmodctr kolumny wiodącej obiektu statystycznego zmienił się o ponad 500 + 20% liczby wierszy w tabeli, gdy gromadzono statystyki (test 3) .

@JNK stwierdził w komentarzu, że jeśli masz 1 miliard wierszy w tabeli, musisz mieć 20 000 5 000 zapisów w pierwszej kolumnie statystyki, aby uruchomić aktualizację.

Weźmy następującą strukturę:

CREATE TABLE dbo.test_table (
    test_table_id INTEGER IDENTITY(1,1) NOT NULL, 
    test_table_value VARCHAR(50), 
    test_table_value2 BIGINT, 
    test_table_value3 NUMERIC(10,2)
);

CREATE CLUSTERED INDEX cix_test_table ON dbo.test_table (test_table_id, test_table_value);

Teraz możemy sprawdzić, co się stało w lądowaniu statystyk.

select * 
    from sys.stats
        where object_id = OBJECT_ID('dbo.test_table')

stat_container

Aby jednak sprawdzić, czy jest to znaczący obiekt statystyczny, musimy:

dbcc show_statistics('dbo.test_table',cix_test_table)

histogram

Ta statystyka nie została zaktualizowana. To dlatego, że wygląda na to, że statystyki nie są aktualizowane aż do SELECTwystąpienia, a nawet wtedy SELECTmusi wykraczać poza to, co SQL Server ma na swoim histogramie. Oto skrypt testowy, który uruchomiłem, aby to przetestować:

    CREATE TABLE test_table (
        test_table_id INTEGER IDENTITY(1,1) NOT NULL, 
        test_table_value VARCHAR(50), 
        test_table_value2 BIGINT, 
        test_table_value3 NUMERIC(10,2)
    );

    CREATE CLUSTERED INDEX cix_test_table ON test_table (test_table_id, test_table_value);

    ALTER TABLE test_table ADD CONSTRAINT pk_test_table PRIMARY KEY  (test_table_id)

    SELECT * 
        FROM sys.stats
            WHERE object_id = OBJECT_ID('dbo.test_table')

    --DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table)
    DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

declare @test int = 0

WHILE @test < 1
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT 'one row|select < 1', * FROM test_table WHERE test_table_id < 1;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

SET @test = 1

WHILE @test < 500
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '100 rows(add 99)|select < 100',* FROM test_table WHERE test_table_id < 100;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--get the table up to 500 rows/changes
WHILE @test < 500
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END
SELECT '500 rows(add 400)|select < 100',* FROM test_table WHERE test_table_id < 100;
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
SELECT '500 rows(add 400)|select < 500',* FROM test_table WHERE test_table_id < 500;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--bump it to 501
SET @test = 500;
WHILE @test < 501
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END


SELECT '501 rows(add 1)|select < 501',* FROM test_table WHERE test_table_id < 501;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 600
SET @test = 501;
WHILE @test < 600
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '600 rows (add 100)|select < 600',* FROM test_table WHERE test_table_id < 600;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 700
SET @test = 600;
WHILE @test < 700
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '700 rows (add 100)|select < 700', * FROM test_table WHERE test_table_id < 700;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 1200
SET @test = 700;
WHILE @test < 1200
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '1200 rows (add 500)|select < 1200',* FROM test_table WHERE test_table_id < 1200;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--DROP TABLE test_table

Zamiast ślepo wyłączać statystyki auto_update, spróbowałbym sprawdzić twój zestaw danych pod kątem przekrzywienia. Jeśli Twoje dane wykazują znaczące zniekształcenie, musisz rozważyć utworzenie filtrowanych statystyk, a następnie zdecydować, czy ręczne zarządzanie aktualizacjami statystyk jest właściwym działaniem.

Aby przeanalizować pochylenie, musisz uruchomić DBCC SHOW_STATISTICS(<stat_object>, <index_name>);(w powyższym skrypcie bez WITH STAT_HEADER) na określonej kombinacji statystyki / indeksu, którą chcesz sprawdzić. Szybkim sposobem na zerknięcie gałki ocznej byłoby spojrzenie na histogram (trzeci zestaw wyników) i sprawdzenie wariancji EQ_ROWS. Jeśli jest dość spójny, to pochylenie jest minimalne. Aby go zwiększyć, spójrz na RANGE_ROWSkolumnę i spójrz na wariancję, ponieważ mierzy to, ile wierszy istnieje między każdym krokiem. Na koniec możesz pobrać [All density]wynik z DENSITY_VECTOR(drugiego zestawu wyników) i pomnożyć go przez [Rows Sampled]wartość w STAT_HEADER(pierwszym zestawie wyników) i zobaczyć, jakie byłoby średnie oczekiwanie na zapytanie w tej kolumnie. Porównujesz tę średnią do swojejEQ_ROWS a jeśli jest wiele miejsc, w których znacznie się różni, to masz przekrzywienie.

Jeśli okaże się, że masz pochylenie, musisz rozważyć utworzenie filtrowanych statystyk dla zakresów, które mają wysokie bardzo wysokie RANGE_ROWS, abyś mógł podać dodatkowe kroki w celu uzyskania lepszych oszacowań tych wartości.

Po zainstalowaniu filtrowanych statystyk możesz sprawdzić możliwość ręcznej aktualizacji statystyk.

— swasheck
źródło

Dziękuję za wyczerpującą odpowiedź. Nie mam zbyt dużego doświadczenia ze statystykami i wydaje się to trudniejsze, jak myślałem. Na pewno przyjrzy się bliżej i zastosuje się do twoich wskazówek.

— saso,