Statystyki w usłudze Synapse SQL

W tym artykule przedstawiono zalecenia i przykłady dotyczące 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.

Na przykład, jeśli optymalizator szacuje, że data, według której filtrowane jest zapytanie, zwróci jeden wiersz, wybierze jeden plan. Jeśli system oszacuje, że wybrana data zwróci 1 milion wierszy, zwróci inny plan wykonania.

Automatyczne tworzenie statystyk

Dedykowany aparat puli SQL będzie analizować przychodzące zapytania użytkowników pod kątem brakujących statystyk, gdy opcja bazy danych AUTO_CREATE_STATISTICS jest ustawiona na 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.

Możesz sprawdzić, czy magazyn danych ma skonfigurowaną opcję AUTO_CREATE_STATISTICS, 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 wyzwolą automatyczne tworzenie statystyk:

• Wybierz
• INSERT-SELECT (przykład użycia w zapytaniach SQL)
• CTAS
• AKTUALIZACJA
• USUŃ
• Wyjaśniaj, gdy wykryto sprzężenie lub obecność predykatu

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 testu porównawczego przed profilowaniem systemu.

Po utworzeniu automatycznych statystyk będą miały postać: WA_Sys<8-cyfrowy identyfikator kolumny w zapisie szesnastkowym>_<8-cyfrowy identyfikator tabeli w zapisie szesnastkowym>. 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ą. Elementem docelowym jest nazwa docelowego indeksu, statystyk lub kolumny, dla której mają być wyświetlane informacje statystyczne.

Aktualizowanie statystyk

Najlepszym rozwiązaniem jest aktualizowanie 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 sprawiają, że statystyki 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.

Jednak, gdy magazyn danych zawiera tylko jeden kraj lub region i wprowadzasz dane z nowego kraju lub regionu, musisz zaktualizować statystyki dotyczące kolumny kraju lub regionu.

Poniżej przedstawiono zalecenia dotyczące aktualizowania statystyk:

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 nastąpiła żadna materialna zmiana danych bazowych. Gdy liczba wierszy uległa znacznej zmianie lub nastąpi zmiana rozkładu wartości dla kolumny, nadszedł czas na zaktualizowanie statystyk.

Od czasu ostatniej aktualizacji statystyk nie jest dostępny dynamiczny widok zarządzania umożliwiający określenie, czy dane w tabeli uległy zmianie. Znajomość wieku statystyk może dać Ci częściowy obraz.

Poniższe zapytanie umożliwia określenie czasu ostatniej aktualizacji statystyk w każdej tabeli.

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;

Na przykład kolumny dat w magazynie danych 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 sprawiają, że statystyki są nieaktualne.

Statystyki dotyczące kolumny płci w tabeli klienta mogą nigdy nie wymagać aktualizacji. 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ć, a 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 pod koniec ładowania. Ładowanie danych najczęściej występuje, gdy zmienia się rozmiar tabel, rozkład wartości w tabelach lub obie te cechy. W związku logiczne wydaje się implementowanie procesów zarządzania na etapie ładowania.

Następujące zasady podstawowe dotyczą aktualizowania statystyk podczas procesu ładowania:

• Upewnij się, że każda załadowana tabela ma zaktualizowany co najmniej jeden obiekt statystyk. Ten proces aktualizuje informacje o rozmiarze tabeli (liczba wierszy i liczba stron) w ramach aktualizacji statystyk.
• Skup się na kolumnach objętych klauzulami JOIN, GROUP BY, ORDER BY i DISTINCT.
• Rozważ częstsze aktualizowanie kolumn typu „klucz rosnący”, takich jak daty transakcji, ponieważ te wartości nie zostaną uwzględnione w histogramie statystyk.
• Rozważ rzadsze aktualizowanie kolumn o rozkładzie statycznym.
• Pamiętaj, że każdy obiekt statystyk jest aktualizowany po kolei. Proste implementowanie UPDATE STATISTICS <TABLE_NAME> nie zawsze jest najlepszym rozwiązaniem, szczególnie w przypadku szerokich tabel z wieloma obiektami statystycznymi.

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 właściwości danych i sposobu użycia kolumny w zapytaniach.

Tworzenie statystyk z jedną kolumną przy użyciu opcji domyślnych

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]);

Na przykład:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Twórz statystyki jednokolumnowe poprzez sprawdzanie każdego wiersza

Domyślna częstotliwość próbkowania wynosząca 20 procent jest wystarczająca dla 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;

Na 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;

Utwórz statystyki w układzie jednokolumnowym 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żesz użyć przefiltrowanych statystyk podczas planowania wykonywania zapytań dotyczących określonej partycji dużej tabeli podzielonej na partycje. 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';

Tworzenie statystyk z jedną kolumną ze wszystkimi opcjami

Możesz również połączyć opcje razem. 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łne informacje, zobacz CREATE STATISTICS.

Utwórz statystyki wielokolumnowe

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

W tym przykładzie histogram znajduje się na product_category. Statystyki między kolumnami są obliczane dla 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 wielokolumnowe poprawią szacowanie kardynalności sprzężeń, agregacje GROUP BY, liczby unikalne i filtry WHERE (o ile kolumna podstawowych statystyk jest częścią filtra).

Tworzenie statystyk dotyczących 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

Zbiornik SQL nie ma systemowej procedury składowanej równoważnej sp_create_stats w SQL Server. Ta procedura składowana tworzy obiekt statystyki dla 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 następującą 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 współczynnika próbkowania 20 procent.

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]);

Na przykład:

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

Aktualizując określone obiekty statystyczne, 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];

Na 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 więcej pracy niż jest to konieczne.

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

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. Na przykład można 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 katalogu dla statystyk

Te widoki systemowe zawierają informacje o statystykach:

Funkcje systemowe statystyk

Te funkcje systemowe są przydatne do pracy ze statystykami:

Łą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 DBCC SHOW_STATISTICS()

Funkcja DBCC SHOW_STATISTICS() pokazuje dane przechowywane w obiekcie statystyk. Te dane są dostępne 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ę krzyżową. Dedykowana pula SQL oblicza oszacowania kardynalności na podstawie dowolnych danych z obiektu statystyk.

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

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

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

Na przykład:

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

Pokaż jedną lub więcej części DBCC SHOW_STATISTICS()

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

Na przykład:

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

Różnice w DBCC SHOW_STATISTICS()

DBCC SHOW_STATISTICS() jest bardziej rygorystycznie egzekwowany w dedykowanej puli SQL w porównaniu do 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 używać do 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 konkretnego zbioru danych (ścieżka przechowywania).

Dlaczego warto używać statystyk

Większa bezserwerowa pula SQL wie o danych, tym szybciej może wykonywać względem niej zapytania. Zbieranie statystyk dotyczących danych jest jedną z najważniejszych rzeczy, które można wykonać, aby zoptymalizować zapytania.

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 warunku predykatu zapytania lub sprzężenia w celu zwiększenia oszacowań kardynalności dla planu zapytania.

Instrukcja SELECT wyzwoli automatyczne tworzenie statystyk.

Automatyczne tworzenie statystyk odbywa się 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. Jeśli używasz analizatora w wersji 1.0 z formatem CSV, prawdopodobnie będziesz musiał 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.

Aktualizowanie statystyk

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

Bezserwerowa pula SQL automatycznie ponownie utworzy statystyki dla kolumn OPENROWSET, jeśli dane zostaną znacząco zmienione. 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 nieaktualne.

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 znacznemu zmianie lub nastąpiła zmiana rozkładu wartości dla kolumny, nadszedł czas na zaktualizowanie statystyk.

Implementowanie zarządzania statystykami

Możesz rozszerzyć potok danych, aby upewnić się, że statystyki są aktualizowane, gdy dane są znacznie zmieniane poprzez 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. To aktualizuje informacje o rozmiarze (liczbie wierszy i liczbie stron) w ramach aktualizacji statystyk.
• Skoncentruj się na kolumnach uczestniczących w klauzulach WHERE, JOIN, GROUP BY, ORDER BY i DISTINCT.
• Aktualizuj częściej kolumny z "kluczem rosnącym", takie jak daty transakcji, ponieważ wartości te 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 bezserwerowych pulach SQL usługi Azure Synapse. 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, przejrzyj sys.sp_create_openrowset_statistics i sys.sp_drop_openrowset_statistics, które mają zastosowanie tylko do bezserwerowych pul SQL.

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ę 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 funkcja TABLESAMPLE nie jest określona, zostanie użyta funkcja FULLSCAN.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Twórz statystyki jednokolumnowe poprzez sprawdzanie 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 usuwania statystyk.

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

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

Aby zaktualizować statystyki dla kolumny "year" w zestawie danych, opartego na pliku population.csv, 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 właściwości danych i sposobu użycia kolumny w zapytaniach.

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ą, dla której należy utworzyć statystyki.

Oblicz statystyki za pomocą opcji FULLSCAN, skanując wszystkie wiersze. FULLSCAN i SAMPLE 100 PERCENT dają takie same rezultaty. Nie można użyć opcji FULLSCAN z opcją SAMPLE.

SAMPLE number PERCENT Określa przybliżony procent lub liczbę wierszy w tabeli lub widoku indeksowanego, które optymalizator zapytań ma użyć przy tworzeniu statystyk. Liczba może być z zakresu od 0 do 100.

SAMPLE nie może być używany z opcją FULLSCAN.

Twórz statystyki jednokolumnowe poprzez sprawdzanie 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 pomiń statystyki:

DROP STATISTICS census_external_table.sState

I utwórz statystyki:

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

Metadane statystyk

Istnieje kilka widoków i funkcji systemowych, których można użyć do znajdowania informacji o statystykach. Na przykład można 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 katalogu dla statystyk

Te widoki systemowe zawierają informacje o statystykach:

Funkcje systemowe statystyk

Te funkcje systemowe są przydatne do pracy ze statystykami:

Łą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   st.[user_created] = 1
;

Następne kroki

Aby jeszcze bardziej zwiększyć wydajność zapytań dla dedykowanej puli SQL, zobacz Monitorowanie obciążenia i najlepsze rozwiązania dotyczące dedykowanej puli SQL.

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