Tworzenie statystyk w celu zwiększenia wydajności zapytań

Ukończone

Gdy zapytania są przesyłane, dedykowany optymalizator zapytań puli SQL próbuje określić, które ścieżki dostępu do danych spowodują najmniejsze nakłady pracy w celu pobrania danych wymaganych do rozwiązania zapytania. Jest to optymalizator oparty na kosztach i porównuje koszt różnych planów zapytań, a następnie wybiera plan z najniższym kosztem.

Statystyki w dedykowanych pulach SQL

Aby ułatwić ten proces, wymagane są statystyki opisujące ilość danych znajdujących się w zakresach wartości oraz zakres wierszy, które mogą zostać zwrócone w celu spełnienia filtru zapytania lub sprzężenia. W związku z tym 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ń.

Podczas tworzenia bazy danych w dedykowanej puli SQL w usłudze Azure Synapse Analytics automatyczne tworzenie statystyk jest domyślnie włączone. Oznacza to, że statystyki są tworzone podczas uruchamiania następującego typu instrukcji języka Transact-SQL:

• Wybierz
• INSERT-SELECT (przykład użycia w zapytaniach SQL)
• CTAS
• Aktualizacja
• USUŃ
• WYJAŚNIENIE w przypadku wykrycia sprzężenia lub obecności predykatu

Podczas wykonywania powyższych instrukcji języka Transact-SQL tworzenie statystyk jest wykonywane na bieżąco, a w rezultacie może wystąpić niewielkie obniżenie wydajności zapytań.

Aby tego uniknąć, statystyki są również tworzone dla każdego utworzonego indeksu, który pomaga w procesie optymalizacji zapytań. Ponieważ jest to akcja wykonywana przed wykonaniem zapytania względem tabeli, na której opiera się indeks, oznacza to, że statystyki są tworzone z wyprzedzeniem. Należy jednak wziąć pod uwagę, że w miarę ładowania nowych danych do tabeli statystyki mogą stać się nieaktualne.

W związku z tym ważne jest zaktualizowanie statystyk po załadowaniu danych lub zaktualizowaniu dużych zakresów danych, dzięki czemu zapytania mogą korzystać ze zaktualizowanych informacji statystycznych.

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łączonego AUTO_CREATE_STATISTICS, zaleca się włączenie tej właściwości, uruchamiając następujące polecenie:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Statystyki w bezserwerowych pulach SQL

Statystyki w bezserwerowej puli SQL mają ten sam cel użycia optymalizatora opartego na kosztach w celu wybrania planu wykonania, który będzie wykonywany najszybciej. Sposób tworzenia statystyk różni się.

Bezserwerowa pula SQL analizuje przychodzące zapytania użytkowników dotyczące 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. Możesz również ręcznie utworzyć statystyki. Jest to ważne podczas pracy z plikami CSV, ponieważ automatyczne tworzenie statystyk nie jest dla nich włączone.

W poniższym przykładzie procedura składowana systemu służy do określania tworzenia statystyk dla określonej instrukcji języka Transact-SQL

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

Aby utworzyć statystyki dla określonej kolumny w pliku CSV, możesz uruchomić następujący kod:

/* make sure you have the credentials to access the storage account 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
*/

/*
The following code will create statistics on a column named year, from a file named population.csv
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT year
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/csv/population/population.csv'',
        FORMAT = ''CSV'',
        FIELDTERMINATOR ='','',
        ROWTERMINATOR = ''\n''
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
'

Należy również zaktualizować statystyki po zmianie danych w plikach. W rzeczywistości bezserwerowa pula SQL automatycznie ponownie utworzy statystyki, 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.

Aby zaktualizować statystyki dla kolumny roku w zestawie danych, która jest oparta na pliku population.csv, należy je usunąć, a następnie utworzyć je, oto instrukcja drop:

EXEC sys.sp_drop_openrowset_statistics N'SELECT year
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/csv/population/population.csv'',
        FORMAT = ''CSV'',
        FIELDTERMINATOR ='','',
        ROWTERMINATOR = ''\n''
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
'

Aby zaktualizować statystyki dla instrukcji, należy usunąć i utworzyć statystyki. Poniższa procedura składowana służy do porzucania statystyk względem określonego tekstu języka Transact-SQL:

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