Udostępnij za pośrednictwem

Statystyki w usłudze Synapse SQL

  • Artykuł

W tym artykule przedstawiono zalecenia i przykłady tworzenia i aktualizowania statystyk optymalizacji zapytań przy użyciu zasobów SQL usługi Synapse: dedykowana pula SQL i bezserwerowa pula SQL.

Statystyki w dedykowanej puli SQL

Dlaczego warto używać statystyk

Bardziej dedykowana pula SQL wie o danych, tym szybciej może wykonywać zapytania. Po załadowaniu danych do dedykowanej puli SQL zbieranie statystyk dotyczących danych jest jedną z najważniejszych rzeczy, które można wykonać w celu optymalizacji zapytań.

Dedykowany optymalizator zapytań puli SQL jest optymalizatorem opartym na kosztach. Porównuje koszt różnych planów zapytań, a następnie wybiera plan z najniższym kosztem. W większości przypadków wybiera plan, który będzie wykonywany najszybciej.

Jeśli na przykład optymalizator szacuje, że data filtrowania zapytania zwróci jeden wiersz, wybierze jeden plan. Jeśli szacuje się, że wybrana data zwróci 1 milion wierszy, zwróci inny plan.

Automatyczne tworzenie statystyk

Dedykowany aparat puli SQL przeanalizuje przychodzące zapytania użytkowników pod kątem brakujących statystyk, gdy opcja AUTO_CREATE_STATISTICS bazy danych ma wartość ON. Jeśli statystyk będzie brakować, optymalizator zapytań utworzy statystyki dla pojedynczych kolumn w predykacie zapytania lub warunku sprzężenia.

Ta funkcja służy do ulepszania oszacowań kardynalności dla planu zapytania.

Ważne

Automatyczne tworzenie statystyk jest obecnie domyślnie włączone.

Możesz sprawdzić, czy magazyn danych AUTO_CREATE_STATISTICS skonfigurowany, uruchamiając następujące polecenie:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Jeśli magazyn danych nie ma włączonej AUTO_CREATE_STATISTICS, zalecamy włączenie tej właściwości, uruchamiając następujące polecenie:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Te instrukcje wyzwalają automatyczne tworzenie statystyk:

  • SELECT
  • INSERT-SELECT
  • CTAS
  • UPDATE
  • DELETE
  • WYJAŚNIENIE w przypadku wykrycia sprzężenia lub obecności predykatu

Uwaga

Automatyczne tworzenie statystyk nie jest generowane w tabelach tymczasowych ani zewnętrznych.

Automatyczne tworzenie statystyk odbywa się synchronicznie. W związku z tym może wystąpić nieznacznie obniżona wydajność zapytań, jeśli w kolumnach brakuje statystyk. Czas tworzenia statystyk dla pojedynczej kolumny zależy od rozmiaru tabeli.

Aby uniknąć mierzalnego obniżenia wydajności, należy najpierw upewnić się, że statystyki zostały utworzone przez wykonanie obciążenia porównawczego przed profilowaniem systemu.

Uwaga

Tworzenie statystyk jest rejestrowane w sys.dm_pdw_exec_requests w innym kontekście użytkownika.

Po utworzeniu automatycznych statystyk będą miały postać: WA_Sys<8-cyfrowy identyfikator kolumny w identyfikatorze tabeli szesnastkowej szesnastkowej><>. Możesz wyświetlić już utworzone statystyki, uruchamiając polecenie DBCC SHOW_STATISTICS :

DBCC SHOW_STATISTICS (<table_name>, <target>)

Table_name to nazwa tabeli zawierającej statystyki do wyświetlenia, która nie może być tabelą zewnętrzną. Element docelowy to nazwa indeksu docelowego, statystyk lub kolumny, dla której mają być wyświetlane informacje statystyczne.

Aktualizacja statystyk

Najlepszym rozwiązaniem jest zaktualizowanie statystyk dotyczących kolumn dat każdego dnia w miarę dodawania nowych dat. Za każdym razem, gdy nowe wiersze są ładowane do magazynu danych, dodawane są nowe daty ładowania lub daty transakcji. Te dodatki zmieniają rozkład danych i są nieaktualne.

Statystyki dotyczące kolumny kraju lub regionu w tabeli klienta mogą nigdy nie być aktualizowane, ponieważ rozkład wartości zwykle się nie zmienia. Zakładając, że dystrybucja jest stała między klientami, dodanie nowych wierszy do odmiany tabeli nie spowoduje zmiany rozkładu danych.

Jeśli jednak magazyn danych zawiera tylko jeden kraj lub region i dane są dostępne z nowego kraju lub regionu, należy zaktualizować statystyki dotyczące kolumny kraj lub region.

Poniżej przedstawiono zalecenia dotyczące aktualizowania statystyk:

Typ Zalecenie
Częstotliwość aktualizacji statystyk Konserwatywny: codziennie
po załadowaniu lub przekształceniu danych
Próbkowanie Mniej niż 1 miliard wierszy użyj domyślnego próbkowania (20 procent).
W przypadku ponad 1 miliardów wierszy użyj próbkowania o dwóch procentach.

Określanie ostatniej aktualizacji statystyk

Jednym z pierwszych pytań, które należy zadać podczas rozwiązywania problemów z zapytaniem, jest "Czy statystyki są aktualne?"

To pytanie nie jest takie, na które można odpowiedzieć według wieku danych. Aktualny obiekt statystyk może być stary, jeśli nie wprowadzono żadnych zmian w danych bazowych. Gdy liczba wierszy uległa znacznej zmianie lub nastąpi znaczna zmiana rozkładu wartości dla kolumny, nadszedł czas na zaktualizowanie statystyk.

Nie jest dostępny dynamiczny widok zarządzania, aby określić, czy dane w tabeli uległy zmianie od czasu ostatniej aktualizacji statystyk. Znajomość wieku statystyk może zapewnić ci część obrazu.

Poniższe zapytanie służy do określania czasu ostatniej aktualizacji statystyk w każdej tabeli.

Uwaga

Jeśli nastąpiła zmiana materialna w rozkładie wartości dla kolumny, należy zaktualizować statystyki niezależnie od czasu ich ostatniej aktualizacji.

SELECT
    sm.[name] AS [schema_name],
    tb.[name] AS [table_name],
    co.[name] AS [stats_column_name],
    st.[name] AS [stats_name],
    STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
    sys.objects ob
    JOIN sys.stats st
        ON  ob.[object_id] = st.[object_id]
    JOIN sys.stats_columns sc
        ON  st.[stats_id] = sc.[stats_id]
        AND st.[object_id] = sc.[object_id]
    JOIN sys.columns co
        ON  sc.[column_id] = co.[column_id]
        AND sc.[object_id] = co.[object_id]
    JOIN sys.types  ty
        ON  co.[user_type_id] = ty.[user_type_id]
    JOIN sys.tables tb
        ON  co.[object_id] = tb.[object_id]
    JOIN sys.schemas sm
        ON  tb.[schema_id] = sm.[schema_id]
WHERE
    st.[user_created] = 1;

Kolumny dat w magazynie danych, na przykład, zwykle wymagają częstych aktualizacji statystyk. Za każdym razem, gdy nowe wiersze są ładowane do magazynu danych, dodawane są nowe daty ładowania lub daty transakcji. Te dodatki zmieniają rozkład danych i są nieaktualne.

Statystyki dotyczące kolumny płci w tabeli klienta mogą nigdy nie być konieczne. Zakładając, że dystrybucja jest stała między klientami, dodanie nowych wierszy do odmiany tabeli nie spowoduje zmiany rozkładu danych.

Jeśli jednak magazyn danych zawiera tylko jedną płeć i nowe wymaganie powoduje wiele płci, musisz zaktualizować statystyki dotyczące kolumny płci.

Aby uzyskać więcej informacji, zapoznaj się z artykułem Statystyki .

Implementowanie zarządzania statystykami

Często dobrym pomysłem jest rozszerzenie procesu ładowania danych w celu zapewnienia, że statystyki są aktualizowane na końcu obciążenia. Ładowanie danych polega na tym, że tabele najczęściej zmieniają ich rozmiar, rozkład wartości lub obie te wartości. W związku z tym proces ładowania jest logicznym miejscem do implementowania niektórych procesów zarządzania.

W procesie ładowania podano następujące wytyczne dotyczące aktualizowania statystyk:

  • Upewnij się, że każda załadowana tabela ma co najmniej jeden obiekt statystyk zaktualizowany. Ten proces aktualizuje informacje o rozmiarze tabeli (liczba wierszy i liczba stron) w ramach aktualizacji statystyk.
  • Skup się na kolumnach uczestniczących w klauzulach JOIN, GROUP BY, ORDER BY i DISTINCT.
  • Rozważ zaktualizowanie kolumn "klucza rosnącego", takich jak daty transakcji częściej, ponieważ te wartości nie zostaną uwzględnione w histogramie statystyk.
  • Rozważ rzadziej aktualizowanie kolumn rozkładu statycznego.
  • Pamiętaj, że każdy obiekt statystyk jest aktualizowany w sekwencji. Po prostu implementacja UPDATE STATISTICS <TABLE_NAME> nie zawsze jest idealna, zwłaszcza w przypadku szerokich tabel z dużą częścią obiektów statystyk.

Aby uzyskać więcej informacji, zobacz Szacowanie kardynalności.

Przykłady: Tworzenie statystyk

W tych przykładach pokazano, jak używać różnych opcji tworzenia statystyk. Opcje używane dla każdej kolumny zależą od cech danych i sposobu użycia kolumny w zapytaniach.

Tworzenie statystyk jednokolumnach z opcjami domyślnymi

Aby utworzyć statystyki dla kolumny, podaj nazwę obiektu statystyk i nazwę kolumny. Ta składnia używa wszystkich opcji domyślnych. Domyślnie dedykowana pula SQL próbkuje 20 procent tabeli podczas tworzenia statystyk.

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name]);

Przykład:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Tworzenie statystyk z jedną kolumną przez zbadanie każdego wiersza

Domyślna częstotliwość próbkowania wynosząca 20 procent jest wystarczająca w przypadku większości sytuacji. Można jednak dostosować częstotliwość próbkowania. Aby próbkować pełną tabelę, użyj następującej składni:

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name])
    WITH FULLSCAN;

Przykład:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH FULLSCAN;

Tworzenie statystyk z jedną kolumną przez określenie rozmiaru próbki

Inną opcją jest określenie rozmiaru próbki jako procent:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH SAMPLE 50 PERCENT;

Tworzenie statystyk z jedną kolumną tylko dla niektórych wierszy

Możesz również utworzyć statystyki dotyczące części wierszy w tabeli, która jest nazywana przefiltrowaną statystyką.

Na przykład można użyć filtrowanych statystyk podczas planowania wykonywania zapytań dotyczących określonej partycji dużej tabeli partycjonowanej. Tworząc statystyki dotyczące tylko wartości partycji, dokładność statystyk zostanie poprawiona. Uzyskasz również poprawę wydajności zapytań.

W tym przykładzie są tworzone statystyki dotyczące zakresu wartości. Wartości można łatwo zdefiniować tak, aby odpowiadały zakresowi wartości w partycji.

CREATE STATISTICS stats_col1
    ON table1(col1)
    WHERE col1 > '2000101' AND col1 < '20001231';

Uwaga

Aby optymalizator zapytań rozważał użycie filtrowanych statystyk podczas wybierania planu zapytania rozproszonego, zapytanie musi mieścić się wewnątrz definicji obiektu statystyk. W poprzednim przykładzie klauzula WHERE zapytania musi określać wartości kolumny col1 między 2000101 a 20001231.

Tworzenie statystyk z jedną kolumną ze wszystkimi opcjami

Możesz również połączyć te opcje. Poniższy przykład tworzy filtrowany obiekt statystyk z niestandardowym rozmiarem próbki:

CREATE STATISTICS stats_col1
    ON table1 (col1)
    WHERE col1 > '2000101' AND col1 < '20001231'
    WITH SAMPLE 50 PERCENT;

Aby uzyskać pełną dokumentację, zobacz CREATE STATISTICS (TWORZENIE STATYSTYK).

Tworzenie statystyk wielokolumnach

Aby utworzyć obiekt statystyk z wieloma kolumnami, użyj poprzednich przykładów, ale określ więcej kolumn.

Uwaga

Histogram, który służy do szacowania liczby wierszy w wyniku zapytania, jest dostępny tylko dla pierwszej kolumny wymienionej w definicji obiektu statystyk.

W tym przykładzie histogram znajduje się na product_category. Statystyki międzykolumna są obliczane na podstawie product_category i product_sub_category:

CREATE STATISTICS stats_2cols
    ON table1 (product_category, product_sub_category)
    WHERE product_category > '2000101' AND product_category < '20001231'
    WITH SAMPLE 50 PERCENT;

Ponieważ istnieje korelacja między product_category a product_sub_category, obiekt statystyk z wieloma kolumnami może być przydatny, jeśli te kolumny są dostępne w tym samym czasie. Podczas wykonywania zapytań dotyczących tej tabeli statystyki wielokolumna poprawią szacowanie kardynalności dla sprzężeń, agregacji GROUP BY, unikatowych liczb i filtrów WHERE (o ile podstawowa kolumna statystyk jest częścią filtru).

Tworzenie statystyk dla wszystkich kolumn w tabeli

Jednym ze sposobów tworzenia statystyk jest wydawanie poleceń CREATE STATISTICS po utworzeniu tabeli:

CREATE TABLE dbo.table1
(
   col1 int
,  col2 int
,  col3 int
)
WITH
  (
    CLUSTERED COLUMNSTORE INDEX
  )
;

CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);

Tworzenie statystyk dotyczących wszystkich kolumn w bazie danych przy użyciu procedury składowanej

Pula SQL nie ma systemowej procedury składowanej równoważnej sp_create_stats w SQL Server. Ta procedura składowana tworzy obiekt statystyki pojedynczej kolumny dla każdej kolumny bazy danych, która nie ma jeszcze statystyk.

Poniższy przykład pomoże Ci rozpocząć projektowanie bazy danych. Możesz dostosować go do swoich potrzeb:

CREATE PROCEDURE    [dbo].[prc_sqldw_create_stats]
(   @create_type    tinyint -- 1 default, 2 Fullscan, 3 Sample
,   @sample_pct     tinyint
)
AS

IF @create_type IS NULL
BEGIN
    SET @create_type = 1;
END;

IF @create_type NOT IN (1,2,3)
BEGIN
    THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
    DROP TABLE #stats_ddl;
END;

CREATE TABLE #stats_ddl
WITH    (   DISTRIBUTION    = HASH([seq_nmbr])
        ,   LOCATION        = USER_DB
        )
AS
WITH T
AS
(
SELECT      t.[name]                        AS [table_name]
,           s.[name]                        AS [table_schema_name]
,           c.[name]                        AS [column_name]
,           c.[column_id]                   AS [column_id]
,           t.[object_id]                   AS [object_id]
,           ROW_NUMBER()
            OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
FROM        sys.[tables] t
JOIN        sys.[schemas] s         ON  t.[schema_id]       = s.[schema_id]
JOIN        sys.[columns] c         ON  t.[object_id]       = c.[object_id]
LEFT JOIN   sys.[stats_columns] l   ON  l.[object_id]       = c.[object_id]
                                    AND l.[column_id]       = c.[column_id]
                                    AND l.[stats_column_id] = 1
LEFT JOIN    sys.[external_tables] e    ON    e.[object_id]        = t.[object_id]
WHERE       l.[object_id] IS NULL
AND            e.[object_id] IS NULL -- not an external table
)
SELECT  [table_schema_name]
,       [table_name]
,       [column_name]
,       [column_id]
,       [object_id]
,       [seq_nmbr]
,       CASE @create_type
        WHEN 1
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
        WHEN 2
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
        WHEN 3
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
        END AS create_stat_ddl
FROM T
;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''
;

WHILE @i <= @t
BEGIN
    SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

Aby utworzyć statystyki dotyczące wszystkich kolumn w tabeli przy użyciu wartości domyślnych, wykonaj procedurę składowaną.

EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;

Aby utworzyć statystyki dotyczące wszystkich kolumn w tabeli przy użyciu funkcji fullscan, wywołaj tę procedurę:

EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;

Aby utworzyć przykładowe statystyki dla wszystkich kolumn w tabeli, wprowadź wartość 3 i procent próbki. Poniższa procedura używa 20-procentowego współczynnika próbkowania.

EXEC [dbo].[prc_sqldw_create_stats] 3, 20;

Przykłady: Aktualizowanie statystyk

Aby zaktualizować statystyki, możesz:

  • Zaktualizuj jeden obiekt statystyk. Określ nazwę obiektu statystyk, który chcesz zaktualizować.
  • Zaktualizuj wszystkie obiekty statystyczne w tabeli. Określ nazwę tabeli zamiast jednego określonego obiektu statystyk.

Aktualizowanie jednego określonego obiektu statystyk

Użyj następującej składni, aby zaktualizować określony obiekt statystyk:

UPDATE STATISTICS [schema_name].[table_name]([stat_name]);

Przykład:

UPDATE STATISTICS [dbo].[table1] ([stats_col1]);

Aktualizując określone obiekty statystyk, można zminimalizować czas i zasoby wymagane do zarządzania statystykami. Ta akcja wymaga przemyślenia wyboru najlepszych obiektów statystyk do zaktualizowania.

Aktualizowanie wszystkich statystyk w tabeli

Prostą metodą aktualizowania wszystkich obiektów statystyk w tabeli jest:

UPDATE STATISTICS [schema_name].[table_name];

Przykład:

UPDATE STATISTICS dbo.table1;

Instrukcja UPDATE STATISTICS jest łatwa w użyciu. Pamiętaj tylko, że aktualizuje wszystkie statystyki w tabeli, co powoduje wyświetlenie większej ilości pracy niż jest to konieczne.

Jeśli wydajność nie jest problemem, ta metoda jest najprostszym i najbardziej kompletnym sposobem zagwarantowania aktualności statystyk.

Uwaga

Podczas aktualizowania wszystkich statystyk w tabeli dedykowana pula SQL przeprowadza skanowanie w celu próbkowania tabeli dla każdego obiektu statystyk. Jeśli tabela jest duża i zawiera wiele kolumn i wiele statystyk, może być bardziej wydajne aktualizowanie poszczególnych statystyk w zależności od potrzeb.

Aby zapoznać się z implementacją UPDATE STATISTICS procedury, zobacz Tabele tymczasowe. Metoda implementacji różni się nieco od powyższej CREATE STATISTICS procedury, ale wynik jest taki sam. Aby uzyskać pełną składnię, zobacz Aktualizowanie statystyk.

Metadane statystyk

Istnieje kilka widoków i funkcji systemowych, których można użyć do znajdowania informacji o statystykach. Możesz na przykład sprawdzić, czy obiekt statystyk może być nieaktualny, używając funkcji STATS_DATE(). STATS_DATE() umożliwia sprawdzenie, kiedy statystyki zostały ostatnio utworzone lub zaktualizowane.

Widoki wykazu dla statystyk

Te widoki systemowe zawierają informacje o statystykach:

Widok wykazu Opis
Sys.columns Jeden wiersz dla każdej kolumny.
Sys.objects Jeden wiersz dla każdego obiektu w bazie danych.
sys.schemas Jeden wiersz dla każdego schematu w bazie danych.
Sys.stats Jeden wiersz dla każdego obiektu statystyk.
sys.stats_columns Jeden wiersz dla każdej kolumny w obiekcie statystyk. Tworzy łącza z powrotem do pliku sys.columns.
Sys.tables Jeden wiersz dla każdej tabeli (łącznie z tabelami zewnętrznymi).
sys.table_types Jeden wiersz dla każdego typu danych.

Funkcje systemowe statystyk

Te funkcje systemowe są przydatne do pracy ze statystykami:

Funkcja systemowa Opis
STATS_DATE Data ostatniej aktualizacji obiektu statystyk.
DBCC SHOW_STATISTICS Poziom podsumowania i szczegółowe informacje na temat rozkładu wartości zrozumiałych dla obiektu statystyk.

Łączenie kolumn i funkcji statystyk w jednym widoku

Ten widok zawiera kolumny powiązane ze statystykami i wynikami funkcji STATS_DATE().

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   1=1
AND     st.[user_created] = 1
;

Przykłady SHOW_STATISTICS DBCC()

DbCC SHOW_STATISTICS() pokazuje dane przechowywane w obiekcie statystyk. Te dane są dostarczane w trzech częściach:

  • Nagłówek
  • Wektor gęstości
  • Histogram

Nagłówek to metadane dotyczące statystyk. Histogram wyświetla rozkład wartości w pierwszej kolumnie klucza obiektu statystyk.

Wektor gęstości mierzy korelację między kolumnami. Dedykowana pula SQL oblicza szacowanie kardynalności z dowolnymi danymi w obiekcie statystyk.

Pokaż nagłówek, gęstość i histogram

W tym prostym przykładzie pokazano wszystkie trzy części obiektu statystyk:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)

Przykład:

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');

Pokaż co najmniej jedną część SHOW_STATISTICS DBCC()

Jeśli interesuje Cię tylko wyświetlanie określonych części, użyj klauzuli WITH i określ, które części chcesz zobaczyć:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
    WITH stat_header, histogram, density_vector

Przykład:

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
    WITH histogram, density_vector

Różnice w SHOW_STATISTICS DBCC()

DBCC SHOW_STATISTICS()program jest bardziej ściśle zaimplementowany w dedykowanej puli SQL w porównaniu z SQL Server:

  • Funkcje nieudokumentowane nie są obsługiwane.
  • Nie można użyć Stats_stream.
  • Nie można połączyć wyników dla określonych podzestawów danych statystycznych. Na przykład STAT_HEADER JOIN DENSITY_VECTOR.
  • NO_INFOMSGS nie można ustawić dla pomijania komunikatów.
  • Nawiasy kwadratowe wokół nazw statystyk nie mogą być używane.
  • Nie można używać nazw kolumn do identyfikowania obiektów statystyk.
  • Błąd niestandardowy 2767 nie jest obsługiwany.

Statystyki w bezserwerowej puli SQL

Statystyki są tworzone dla określonej kolumny dla określonego zestawu danych (ścieżka magazynu).

Uwaga

Nie można utworzyć statystyk dla kolumn biznesowych.

Dlaczego warto używać statystyk

Większa bezserwerowa pula SQL wie o Twoich danych, tym szybciej może wykonywać względem niej zapytania. Zbieranie statystyk dotyczących danych jest jedną z najważniejszych czynności, które można wykonać w celu zoptymalizowania zapytań.

Optymalizator zapytań bezserwerowej puli SQL jest optymalizatorem opartym na kosztach. Porównuje koszt różnych planów zapytań, a następnie wybiera plan z najniższym kosztem. W większości przypadków wybiera plan, który będzie wykonywany najszybciej.

Jeśli na przykład optymalizator szacuje, że data filtrowania zapytania zwróci jeden wiersz, wybierze jeden plan. Jeśli szacuje się, że wybrana data zwróci 1 milion wierszy, wybierze inny plan.

Automatyczne tworzenie statystyk

Bezserwerowa pula SQL analizuje przychodzące zapytania użytkowników pod kątem brakujących statystyk. Jeśli brakuje statystyk, optymalizator zapytań tworzy statystyki dotyczące poszczególnych kolumn w predykacie zapytania lub warunku sprzężenia, aby poprawić szacowanie kardynalności dla planu zapytania.

Instrukcja SELECT wyzwoli automatyczne tworzenie statystyk.

Uwaga

W przypadku automatycznego tworzenia próbkowania statystyk jest używane, a w większości przypadków wartość procentowa próbkowania będzie mniejsza niż 100%. Ten przepływ jest taki sam dla każdego formatu pliku. Należy pamiętać, że podczas odczytywania pliku CSV z próbkowaniem analizatora w wersji 1.0 nie jest obsługiwane, a automatyczne tworzenie statystyk nie będzie miało miejsce z procentem próbkowania mniejszym niż 100%. W przypadku małych tabel z szacowaną niską kardynalnością (liczba wierszy) automatyczne tworzenie statystyk zostanie wyzwolone z procentem próbkowania 100%. Oznacza to w zasadzie, że funkcja fullscan jest wyzwalana, a automatyczne statystyki są tworzone nawet dla woluminów CSV z analizatorem w wersji 1.0.

Automatyczne tworzenie statystyk jest wykonywane synchronicznie, więc może wystąpić nieznacznie obniżona wydajność zapytań, jeśli w kolumnach brakuje statystyk. Czas tworzenia statystyk dla pojedynczej kolumny zależy od rozmiaru plików docelowych.

Ręczne tworzenie statystyk

Bezserwerowa pula SQL umożliwia ręczne tworzenie statystyk. W przypadku korzystania z analizatora w wersji 1.0 z woluminem CSV prawdopodobnie trzeba będzie ręcznie utworzyć statystyki, ponieważ ta wersja analizatora nie obsługuje próbkowania. Automatyczne tworzenie statystyk w przypadku analizatora w wersji 1.0 nie nastąpi, chyba że procent próbkowania wynosi 100%.

Zapoznaj się z poniższymi przykładami, aby uzyskać instrukcje dotyczące ręcznego tworzenia statystyk.

Aktualizacja statystyk

Zmiany w danych w plikach, usuwaniu i dodawaniu plików powodują zmiany w dystrybucji danych i powodują, że statystyki są nieaktualne. W takim przypadku należy zaktualizować statystyki.

Bezserwerowa pula SQL automatycznie odtwarza statystyki w przypadku znacznej zmiany danych. Za każdym razem, gdy statystyki są tworzone automatycznie, bieżący stan zestawu danych jest również zapisywany: ścieżki plików, rozmiary, daty ostatniej modyfikacji.

Gdy statystyki są nieaktualne, zostaną utworzone nowe. Algorytm przechodzi przez dane i porównuje je z bieżącym stanem zestawu danych. Jeśli rozmiar zmian jest większy niż określony próg, stare statystyki zostaną usunięte i zostaną ponownie utworzone w nowym zestawie danych.

Statystyki ręczne nigdy nie są deklarowane jako nieaktualne.

Uwaga

W przypadku automatycznego odtworzenia próbkowania statystyk jest używane, a w większości przypadków wartość procentowa próbkowania będzie mniejsza niż 100%. Ten przepływ jest taki sam dla każdego formatu pliku. Należy pamiętać, że podczas odczytywania pliku CSV z próbkowaniem analizatora w wersji 1.0 nie jest obsługiwane, a automatyczne odtworzenie statystyk nie będzie miało miejsce z procentem próbkowania mniejszym niż 100%. W takim przypadku należy ręcznie porzucić i ponownie utworzyć statystyki. Zapoznaj się z poniższymi przykładami, aby dowiedzieć się, jak usunąć i utworzyć statystyki. W przypadku małych tabel z szacowaną niską kardynalnością (liczba wierszy) automatyczne odtwarzanie statystyk zostanie wyzwolone z procentem próbkowania 100%. Oznacza to w zasadzie, że funkcja fullscan jest wyzwalana, a automatyczne statystyki są tworzone nawet dla woluminów CSV z analizatorem w wersji 1.0.

Jednym z pierwszych pytań, które należy zadać podczas rozwiązywania problemów z zapytaniem, jest "Czy statystyki są aktualne?"

Gdy liczba wierszy uległa znacznej zmianie lub nastąpiła istotna zmiana rozkładu wartości dla kolumny , nadszedł czas na zaktualizowanie statystyk.

Uwaga

W przypadku istotnej zmiany rozkładu wartości dla kolumny należy zaktualizować statystyki niezależnie od czasu ich ostatniej aktualizacji.

Implementowanie zarządzania statystykami

Możesz rozszerzyć potok danych, aby upewnić się, że statystyki są aktualizowane, gdy dane są znacznie zmieniane przez dodawanie, usuwanie lub zmienianie plików.

Dostępne są następujące wytyczne dotyczące aktualizowania statystyk:

  • Upewnij się, że zestaw danych ma zaktualizowany co najmniej jeden obiekt statystyk. Informacje o rozmiarze aktualizacji (liczba wierszy i liczba stron) w ramach aktualizacji statystyk.
  • Skup się na kolumnach uczestniczących w klauzulach WHERE, JOIN, GROUP BY, ORDER BY i DISTINCT.
  • Aktualizuj kolumny "rosnąco klucza", takie jak daty transakcji częściej, ponieważ te wartości nie będą uwzględniane w histogramie statystyk.
  • Rzadziej aktualizuj kolumny dystrybucji statycznej.

Aby uzyskać więcej informacji, zobacz Szacowanie kardynalności.

Przykłady: tworzenie statystyk dla kolumny w ścieżce OPENROWSET

W poniższych przykładach pokazano, jak używać różnych opcji tworzenia statystyk w Azure Synapse bezserwerowych pul SQL. Opcje używane dla każdej kolumny zależą od właściwości danych i sposobu użycia kolumny w zapytaniach. Aby uzyskać więcej informacji na temat procedur składowanych używanych w tych przykładach, zapoznaj się z sys.sp_create_openrowset_statistics i sys.sp_drop_openrowset_statistics, które dotyczą tylko bezserwerowych pul SQL.

Uwaga

W tej chwili można tworzyć statystyki z jedną kolumną tylko w tej chwili.

Do wykonywania sp_create_openrowset_statistics operacji zbiorczych i sp_drop_openrowset_statistics: ADMINISTROWANIE OPERACJAMI ZBIORCZYM LUB ADMINISTROWANIE OPERACJAMI ZBIORCZYM BAZY DANYCH są wymagane następujące uprawnienia.

Poniższa procedura składowana służy do tworzenia statystyk:

sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'

Argumenty: [ @stmt = ] N'statement_text' — określa instrukcję języka Transact-SQL, która zwróci wartości kolumn do użycia na potrzeby statystyk. Aby określić próbki danych do użycia, można użyć metody TABLESAMPLE. Jeśli parametr TABLESAMPLE nie zostanie określony, zostanie użyty tryb FULLSCAN.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Uwaga

Próbkowanie woluminów CSV nie działa, jeśli używasz analizatora w wersji 1.0, tylko funkcja FULLSCAN jest obsługiwana w przypadku woluminów CSV z analizatorem w wersji 1.0.

Tworzenie statystyk z jedną kolumną przez sprawdzenie każdego wiersza

Aby utworzyć statystyki dla kolumny, podaj zapytanie zwracające kolumnę, dla której potrzebujesz statystyk.

Jeśli domyślnie nie określisz inaczej podczas ręcznego tworzenia statystyk, bezserwerowa pula SQL używa 100% danych podanych w zestawie danych podczas tworzenia statystyk.

Aby na przykład utworzyć statystyki z opcjami domyślnymi (FULLSCAN) dla kolumny populacji zestawu danych na podstawie pliku us_population.csv:


EXEC sys.sp_create_openrowset_statistics N'SELECT 
    population
FROM OPENROWSET(
    BULK ''Https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
    FORMAT = ''CSV'',
    PARSER_VERSION = ''2.0'',
    HEADER_ROW = TRUE)
AS [r]'

Tworzenie statystyk z jedną kolumną przez określenie rozmiaru próbki

Rozmiar próbki można określić jako procent:

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

Przykłady: Aktualizowanie statystyk

Aby zaktualizować statystyki, należy usunąć i utworzyć statystyki. Aby uzyskać więcej informacji, zapoznaj się z sys.sp_create_openrowset_statistics i sys.sp_drop_openrowset_statistics.

Procedura sys.sp_drop_openrowset_statistics składowana służy do porzucania statystyk:

sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'

Uwaga

Do wykonywania sp_create_openrowset_statistics operacji zbiorczych i sp_drop_openrowset_statistics: ADMINISTROWANIE OPERACJAMI ZBIORCZYM LUB ADMINISTROWANIE OPERACJAMI ZBIORCZYM BAZY DANYCH są wymagane następujące uprawnienia.

Argumenty: [ @stmt = ] N'statement_text' — określa tę samą instrukcję języka Transact-SQL używaną podczas tworzenia statystyk.

Aby zaktualizować statystyki dla kolumny year w zestawie danych, który jest oparty na population.csv pliku, należy usunąć i utworzyć statystyki:

EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'
GO

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

Przykłady: Tworzenie statystyk dla kolumny tabeli zewnętrznej

W poniższych przykładach pokazano, jak używać różnych opcji tworzenia statystyk. Opcje używane dla każdej kolumny zależą od cech danych i sposobu użycia kolumny w zapytaniach.

Uwaga

W tej chwili możesz utworzyć statystyki jednokolumne.

Aby utworzyć statystyki dla kolumny, podaj nazwę obiektu statystyk i nazwę kolumny.

CREATE STATISTICS statistics_name
ON { external_table } ( column )
    WITH
        { FULLSCAN
          | [ SAMPLE number PERCENT ] }
        , { NORECOMPUTE }

Argumenty: external_table Określa tabelę zewnętrzną, którą należy utworzyć.

Statystyki obliczeń FULLSCAN przez skanowanie wszystkich wierszy. FULLSCAN i SAMPLE 100 PROCENT mają te same wyniki. Nie można używać funkcji FULLSCAN z opcją SAMPLE.

PRÓBKA liczba PERCENT Określa przybliżoną wartość procentową lub liczbę wierszy w tabeli lub w widoku indeksowanym dla optymalizatora zapytań do użycia podczas tworzenia statystyk. Liczba może być z zakresu od 0 do 100.

Przykład nie może być używany z opcją FULLSCAN.

Uwaga

Próbkowanie CSV nie działa, jeśli używasz analizatora w wersji 1.0, tylko FUNKCJA FULLSCAN jest obsługiwana dla woluminów CSV z analizatorem w wersji 1.0.

Tworzenie statystyk z jedną kolumną przez zbadanie każdego wiersza

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Tworzenie statystyk z jedną kolumną przez określenie rozmiaru próbki

-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH SAMPLE 5 percent, NORECOMPUTE

Przykłady: Aktualizowanie statystyk

Aby zaktualizować statystyki, należy usunąć i utworzyć statystyki. Najpierw upuść statystyki:

DROP STATISTICS census_external_table.sState

Utwórz statystyki:

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Metadane statystyk

Istnieje kilka widoków systemowych i funkcji, których można użyć do znajdowania informacji o statystykach. Na przykład możesz sprawdzić, czy obiekt statystyk może być nieaktualny przy użyciu funkcji STATS_DATE(). STATS_DATE() umożliwia sprawdzenie, kiedy statystyki zostały ostatnio utworzone lub zaktualizowane.

Uwaga

Metadane statystyk są dostępne tylko dla kolumn tabeli zewnętrznej. Metadane statystyk nie są dostępne dla kolumn OPENROWSET.

Widoki wykazu dla statystyk

Te widoki systemowe zawierają informacje o statystykach:

Widok wykazu Opis
Sys.columns Jeden wiersz dla każdej kolumny.
Sys.objects Jeden wiersz dla każdego obiektu w bazie danych.
sys.schemas Jeden wiersz dla każdego schematu w bazie danych.
Sys.stats Jeden wiersz dla każdego obiektu statystyk.
sys.stats_columns Jeden wiersz dla każdej kolumny w obiekcie statystyk. Łączy się z powrotem do pliku sys.columns.
Sys.tables Jeden wiersz dla każdej tabeli (łącznie z tabelami zewnętrznymi).
sys.table_types Jeden wiersz dla każdego typu danych.

Funkcje systemowe dla statystyk

Te funkcje systemowe są przydatne do pracy ze statystykami:

Funkcja systemowa Opis
STATS_DATE Data ostatniej aktualizacji obiektu statystyk.

Łączenie kolumn i funkcji statystyk w jednym widoku

Ten widok zawiera kolumny, które odnoszą się do statystyk i wyników funkcji STATS_DATE().

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   st.[user_created] = 1
;

Następne kroki

Aby dodatkowo poprawić wydajność zapytań dla dedykowanej puli SQL, zobacz Monitorowanie obciążenia i najlepsze rozwiązania dotyczące dedykowanej puli SQL.

Aby dodatkowo zwiększyć wydajność zapytań dla bezserwerowej puli SQL, zobacz Najlepsze rozwiązania dotyczące bezserwerowej puli SQL.