Optymalizowanie transakcji w dedykowanej puli SQL w usłudze Azure Synapse Analytics

Dowiedz się, jak zoptymalizować wydajność kodu transakcyjnego w dedykowanej puli SQL, jednocześnie minimalizując ryzyko długich wycofań transakcji.

Transakcje i rejestrowanie

Transakcje są ważnym składnikiem relacyjnego silnika puli SQL. Transakcje są używane podczas modyfikowania danych. Te transakcje mogą być jawne lub niejawne. Pojedyncze instrukcje INSERT, UPDATE i DELETE to wszystkie przykłady niejawnych transakcji. Jawne transakcje używają FUNKCJI BEGIN TRAN, COMMIT TRAN lub ROLLBACK TRAN. Jawne transakcje są zwykle używane, gdy wiele instrukcji modyfikacji musi być powiązanych ze sobą w jednej jednostce atomowej.

Zmiany w puli SQL są śledzone przy użyciu dzienników transakcji. Każda dystrybucja ma własny dziennik transakcji. Zapisy dziennika transakcji są automatyczne. Nie jest wymagana żadna konfiguracja. Mimo że ten proces zapewnia zapis danych, wprowadza to obciążenie w systemie. Ten wpływ można zminimalizować, pisząc transakcyjnie wydajny kod. Kod wydajny transakcyjnie zasadniczo należy do dwóch kategorii.

• Używaj minimalistycznych podejść do logowania, jeśli jest to możliwe
• Przetwarzanie danych przy użyciu partii o określonym zakresie w celu uniknięcia pojedynczych długotrwałych transakcji
• Przyjmij wzorzec przełączania partycji dla dużych modyfikacji danej partycji

Minimalne kontra pełne rejestrowanie

W przeciwieństwie do w pełni zarejestrowanych operacji, które używają dziennika transakcji do śledzenia każdej zmiany wiersza, minimalnie rejestrowane operacje śledzą tylko alokacje zakresów i zmiany metadanych. W związku z tym minimalne rejestrowanie polega na rejestrowaniu tylko tych informacji, które są potrzebne do wycofania transakcji po awarii lub na wyraźne żądanie (ROLLBACK TRAN). Ponieważ w dzienniku transakcji śledzone jest znacznie mniej informacji, minimalnie zarejestrowana operacja działa lepiej niż operacja w pełni rejestrowana o podobnym rozmiarze. Ponadto, ponieważ mniej operacji zapisu odbywa się w dzienniku transakcji, generowana jest znacznie mniejsza ilość danych dziennika, co zwiększa wydajność operacji wejścia/wyjścia.

Limity bezpieczeństwa transakcji dotyczą tylko w pełni zarejestrowanych operacji.

Uwaga

Minimalnie zarejestrowane operacje mogą uczestniczyć w jawnych transakcjach. Ze względu na to, że wszystkie zmiany w strukturach alokacji są śledzone, można wycofać operacje z minimalnym rejestrowaniem.

Minimalnie zarejestrowane operacje

Następujące operacje mogą być rejestrowane minimalnie:

• CREATE TABLE AS SELECT (CTAS)
• WSTAWIAĆ.. WYBRAĆ
• TWORZENIE INDEKSU
• PRZEBUDUJ INDEKS ALTER
• DROP INDEX
• USUŃ WSZYSTKIE WIERSZE Z TABELI
• DROP TABLE
• ALTER TABLE SWITCH PARTITION

Uwaga

Wewnętrzne operacje przenoszenia danych (takie jak BROADCAST i SHUFFLE) nie mają wpływu na limit bezpieczeństwa transakcji.

Minimalne rejestrowanie z masowym ładowaniem

CTAS i INSERT... SELECT to operacje ładowania zbiorczego. Jednak oba te elementy mają wpływ na definicję tabeli docelowej i zależą od scenariusza ładowania. W poniższej tabeli wyjaśniono, kiedy operacje zbiorcze są w pełni lub minimalnie rejestrowane:

Indeks podstawowy	Scenariusz ładowania	Tryb rejestrowania
Kupa	Jakikolwiek	Minimalny
Indeks klastrowany	Pusta tabela docelowa	Minimalny
Indeks klastrowany	Załadowane wiersze nie nakładają się na istniejące strony w celu	Minimalny
Indeks klastrowany	Załadowane wiersze nakładają się na istniejące strony w miejscu docelowym.	Pełny
Indeks klastrowany columnstore	Rozmiar partii >= 102 400 na dystrybucję wyrównaną do partycji	Minimalny
Indeks klastrowy kolumnowy	Rozmiar partii < 102 400 na partycję w wyrównanej dystrybucji	Pełny

Warto zauważyć, że wszystkie operacje zapisu do aktualizacji indeksów wtórnych lub nieklastrowanych zawsze będą w pełni rejestrowane.

Ważne

Dedykowana pula SQL ma 60 dystrybucji. W związku z tym, przy założeniu, że wszystkie wiersze są równomiernie rozproszone i trafiają do jednej partycji, partia będzie musiała zawierać 6 144 000 wierszy lub więcej, aby możliwe było minimalne logowanie podczas zapisywania do klastrowanego indeksu columnstore. Jeśli tabela jest podzielona na partycje, a wstawiane wiersze obejmują granice partycji, będzie potrzebnych 6 144 000 wierszy na granicę partycji przy założeniu równomiernej dystrybucji danych. Każda partycja w każdej dystrybucji musi samodzielnie przekroczyć próg 102 400 wierszy, aby operacja wstawiania była minimalnie rejestrowana w dystrybucji.

Ładowanie danych do niepustej tabeli z indeksem klastrowanym może często prowadzić do pojawienia się kombinacji wierszy w pełni zarejestrowanych i częściowo zarejestrowanych. Indeks klastrowany jest drzewem zrównoważonym (b-tree) stron. Jeśli strona, do której są dokonywane zapisy, zawiera już wiersze z innej transakcji, wówczas te zapisy zostaną w pełni zarejestrowane. Jeśli jednak strona jest pusta, zapis na tej stronie będzie minimalnie rejestrowany.

Optymalizacja usuwania

DELETE to w pełni zarejestrowana operacja. Jeśli musisz usunąć dużą ilość danych w tabeli lub partycji, często warto SELECT danych, które chcesz zachować, co można uruchomić jako minimalnie zarejestrowaną operację. Aby wybrać dane, utwórz nową tabelę z CTAS. Po utworzeniu użyj RENAME, aby zamienić starą tabelę na nowo utworzoną tabelę.

-- Delete all sales transactions for Promotions except PromotionKey 2.

--Step 01. Create a new table select only the records we want to kep (PromotionKey 2)
CREATE TABLE [dbo].[FactInternetSales_d]
WITH
(    CLUSTERED COLUMNSTORE INDEX
,    DISTRIBUTION = HASH([ProductKey])
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20000101, 20010101, 20020101, 20030101, 20040101, 20050101
                                                ,    20060101, 20070101, 20080101, 20090101, 20100101, 20110101
                                                ,    20120101, 20130101, 20140101, 20150101, 20160101, 20170101
                                                ,    20180101, 20190101, 20200101, 20210101, 20220101, 20230101
                                                ,    20240101, 20250101, 20260101, 20270101, 20280101, 20290101
                                                )
)
AS
SELECT     *
FROM     [dbo].[FactInternetSales]
WHERE    [PromotionKey] = 2
OPTION (LABEL = 'CTAS : Delete')
;

--Step 02. Rename the Tables to replace the
RENAME OBJECT [dbo].[FactInternetSales]   TO [FactInternetSales_old];
RENAME OBJECT [dbo].[FactInternetSales_d] TO [FactInternetSales];

Optymalizowanie aktualizacji

UPDATE to w pełni zarejestrowana operacja. Jeśli musisz zaktualizować dużą liczbę wierszy w tabeli lub partycji, często może być znacznie bardziej wydajne użycie operacji minimalnie rejestrowanej, takiej jak CTAS.

W poniższym przykładzie aktualizacja pełnej tabeli została przekształcona w CTAS, aby możliwe było minimalne rejestrowanie.

W tym przypadku retrospektywnie dodajemy kwotę rabatu do sprzedaży w tabeli:

--Step 01. Create a new table containing the "Update".
CREATE TABLE [dbo].[FactInternetSales_u]
WITH
(    CLUSTERED INDEX
,    DISTRIBUTION = HASH([ProductKey])
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20000101, 20010101, 20020101, 20030101, 20040101, 20050101
                                                ,    20060101, 20070101, 20080101, 20090101, 20100101, 20110101
                                                ,    20120101, 20130101, 20140101, 20150101, 20160101, 20170101
                                                ,    20180101, 20190101, 20200101, 20210101, 20220101, 20230101
                                                ,    20240101, 20250101, 20260101, 20270101, 20280101, 20290101
                                                )
                )
)
AS
SELECT
    [ProductKey]  
,    [OrderDateKey]
,    [DueDateKey]  
,    [ShipDateKey]
,    [CustomerKey]
,    [PromotionKey]
,    [CurrencyKey]
,    [SalesTerritoryKey]
,    [SalesOrderNumber]
,    [SalesOrderLineNumber]
,    [RevisionNumber]
,    [OrderQuantity]
,    [UnitPrice]
,    [ExtendedAmount]
,    [UnitPriceDiscountPct]
,    ISNULL(CAST(5 as float),0) AS [DiscountAmount]
,    [ProductStandardCost]
,    [TotalProductCost]
,    ISNULL(CAST(CASE WHEN [SalesAmount] <=5 THEN 0
         ELSE [SalesAmount] - 5
         END AS MONEY),0) AS [SalesAmount]
,    [TaxAmt]
,    [Freight]
,    [CarrierTrackingNumber]
,    [CustomerPONumber]
FROM    [dbo].[FactInternetSales]
OPTION (LABEL = 'CTAS : Update')
;

--Step 02. Rename the tables
RENAME OBJECT [dbo].[FactInternetSales]   TO [FactInternetSales_old];
RENAME OBJECT [dbo].[FactInternetSales_u] TO [FactInternetSales];

--Step 03. Drop the old table
DROP TABLE [dbo].[FactInternetSales_old]

Uwaga

Ponowne tworzenie dużych tabel może korzystać z funkcji zarządzania obciążeniami dedykowanej puli SQL. Aby uzyskać więcej informacji, zobacz Klasy zasobów na potrzeby zarządzania obciążeniami.

Optymalizowanie za pomocą przełączania partycji

Jeśli masz do czynienia z modyfikacjami na dużą skalę wewnątrz partycji tabeli , stosowanie wzorca przełączania partycji ma sens. Jeśli modyfikacja danych jest znacząca i obejmuje wiele partycji, iteracja na partycjach osiąga ten sam wynik.

Kroki umożliwiające wykonanie przełącznika partycji są następujące:

1. Utwórz pustą partycję wyjściową
2. Wykonaj 'update' jako CTAS
3. Zamień istniejące dane w tabeli wyjściowej
4. Zamień na nowe dane
5. Czyszczenie danych

Aby jednak ułatwić identyfikację partycji do przełączenia, utwórz następującą procedurę pomocnika.

CREATE PROCEDURE dbo.partition_data_get
    @schema_name           NVARCHAR(128)
,    @table_name               NVARCHAR(128)
,    @boundary_value           INT
AS
IF OBJECT_ID('tempdb..#ptn_data') IS NOT NULL
BEGIN
    DROP TABLE #ptn_data
END
CREATE TABLE #ptn_data
WITH    (    DISTRIBUTION = ROUND_ROBIN
        ,    HEAP
        )
AS
WITH CTE
AS
(
SELECT     s.name                            AS [schema_name]
,        t.name                            AS [table_name]
,         p.partition_number                AS [ptn_nmbr]
,        p.[rows]                        AS [ptn_rows]
,        CAST(r.[value] AS INT)            AS [boundary_value]
FROM        sys.schemas                    AS s
JOIN        sys.tables                    AS t    ON  s.[schema_id]        = t.[schema_id]
JOIN        sys.indexes                    AS i    ON     t.[object_id]        = i.[object_id]
JOIN        sys.partitions                AS p    ON     i.[object_id]        = p.[object_id]
                                                AND i.[index_id]        = p.[index_id]
JOIN        sys.partition_schemes        AS h    ON     i.[data_space_id]    = h.[data_space_id]
JOIN        sys.partition_functions        AS f    ON     h.[function_id]        = f.[function_id]
LEFT JOIN    sys.partition_range_values    AS r     ON     f.[function_id]        = r.[function_id]
                                                AND r.[boundary_id]        = p.[partition_number]
WHERE i.[index_id] <= 1
)
SELECT    *
FROM    CTE
WHERE    [schema_name]        = @schema_name
AND        [table_name]        = @table_name
AND        [boundary_value]    = @boundary_value
OPTION (LABEL = 'dbo.partition_data_get : CTAS : #ptn_data')
;
GO

Ta procedura maksymalizuje ponowne użycie kodu i utrzymuje, że przykład przełączania partycji jest bardziej kompaktowy.

Poniższy kod demonstruje kroki wymienione wcześniej w celu osiągnięcia pełnej procedury przełączania partycji.

--Create a partitioned aligned empty table to switch out the data
IF OBJECT_ID('[dbo].[FactInternetSales_out]') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FactInternetSales_out]
END

CREATE TABLE [dbo].[FactInternetSales_out]
WITH
(    DISTRIBUTION = HASH([ProductKey])
,    CLUSTERED COLUMNSTORE INDEX
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20020101, 20030101
                                                )
                )
)
AS
SELECT *
FROM    [dbo].[FactInternetSales]
WHERE 1=2
OPTION (LABEL = 'CTAS : Partition Switch IN : UPDATE')
;

--Create a partitioned aligned table and update the data in the select portion of the CTAS
IF OBJECT_ID('[dbo].[FactInternetSales_in]') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FactInternetSales_in]
END

CREATE TABLE [dbo].[FactInternetSales_in]
WITH
(    DISTRIBUTION = HASH([ProductKey])
,    CLUSTERED COLUMNSTORE INDEX
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20020101, 20030101
                                                )
                )
)
AS
SELECT
    [ProductKey]  
,    [OrderDateKey]
,    [DueDateKey]  
,    [ShipDateKey]
,    [CustomerKey]
,    [PromotionKey]
,    [CurrencyKey]
,    [SalesTerritoryKey]
,    [SalesOrderNumber]
,    [SalesOrderLineNumber]
,    [RevisionNumber]
,    [OrderQuantity]
,    [UnitPrice]
,    [ExtendedAmount]
,    [UnitPriceDiscountPct]
,    ISNULL(CAST(5 as float),0) AS [DiscountAmount]
,    [ProductStandardCost]
,    [TotalProductCost]
,    ISNULL(CAST(CASE WHEN [SalesAmount] <=5 THEN 0
         ELSE [SalesAmount] - 5
         END AS MONEY),0) AS [SalesAmount]
,    [TaxAmt]
,    [Freight]
,    [CarrierTrackingNumber]
,    [CustomerPONumber]
FROM    [dbo].[FactInternetSales]
WHERE    OrderDateKey BETWEEN 20020101 AND 20021231
OPTION (LABEL = 'CTAS : Partition Switch IN : UPDATE')
;

--Use the helper procedure to identify the partitions
--The source table
EXEC dbo.partition_data_get 'dbo','FactInternetSales',20030101
DECLARE @ptn_nmbr_src INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_src

--The "in" table
EXEC dbo.partition_data_get 'dbo','FactInternetSales_in',20030101
DECLARE @ptn_nmbr_in INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_in

--The "out" table
EXEC dbo.partition_data_get 'dbo','FactInternetSales_out',20030101
DECLARE @ptn_nmbr_out INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_out

--Switch the partitions over
DECLARE @SQL NVARCHAR(4000) = '
ALTER TABLE [dbo].[FactInternetSales]    SWITCH PARTITION '+CAST(@ptn_nmbr_src AS VARCHAR(20))    +' TO [dbo].[FactInternetSales_out] PARTITION '    +CAST(@ptn_nmbr_out AS VARCHAR(20))+';
ALTER TABLE [dbo].[FactInternetSales_in] SWITCH PARTITION '+CAST(@ptn_nmbr_in AS VARCHAR(20))    +' TO [dbo].[FactInternetSales] PARTITION '        +CAST(@ptn_nmbr_src AS VARCHAR(20))+';'
EXEC sp_executesql @SQL

--Perform the clean-up
TRUNCATE TABLE dbo.FactInternetSales_out;
TRUNCATE TABLE dbo.FactInternetSales_in;

DROP TABLE dbo.FactInternetSales_out
DROP TABLE dbo.FactInternetSales_in
DROP TABLE #ptn_data

Minimalizuj rejestrowanie przy użyciu małych partii

W przypadku operacji modyfikacji dużych danych warto podzielić operację na fragmenty lub partie w celu określenia zakresu jednostki pracy.

Poniższy kod jest przykładem roboczym. Rozmiar partii został ustawiony na liczbę trywialną, aby wyróżnić technikę. W rzeczywistości rozmiar partii byłby znacznie większy.

SET NO_COUNT ON;
IF OBJECT_ID('tempdb..#t') IS NOT NULL
BEGIN
    DROP TABLE #t;
    PRINT '#t dropped';
END

CREATE TABLE #t
WITH    (    DISTRIBUTION = ROUND_ROBIN
        ,    HEAP
        )
AS
SELECT    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS seq_nmbr
,        SalesOrderNumber
,        SalesOrderLineNumber
FROM    dbo.FactInternetSales
WHERE    [OrderDateKey] BETWEEN 20010101 and 20011231
;

DECLARE    @seq_start        INT = 1
,        @batch_iterator    INT = 1
,        @batch_size        INT = 50
,        @max_seq_nmbr    INT = (SELECT MAX(seq_nmbr) FROM dbo.#t)
;

DECLARE    @batch_count    INT = (SELECT CEILING((@max_seq_nmbr*1.0)/@batch_size))
,        @seq_end        INT = @batch_size
;

SELECT COUNT(*)
FROM    dbo.FactInternetSales f

PRINT 'MAX_seq_nmbr '+CAST(@max_seq_nmbr AS VARCHAR(20))
PRINT 'MAX_Batch_count '+CAST(@batch_count AS VARCHAR(20))

WHILE    @batch_iterator <= @batch_count
BEGIN
    DELETE
    FROM    dbo.FactInternetSales
    WHERE EXISTS
    (
            SELECT    1
            FROM    #t t
            WHERE    seq_nmbr BETWEEN  @seq_start AND @seq_end
            AND        FactInternetSales.SalesOrderNumber        = t.SalesOrderNumber
            AND        FactInternetSales.SalesOrderLineNumber    = t.SalesOrderLineNumber
    )
    ;

    SET @seq_start = @seq_end
    SET @seq_end = (@seq_start+@batch_size);
    SET @batch_iterator +=1;
END

Wskazówki dotyczące wstrzymywania i skalowania

Dedykowana pula SQL umożliwia wstrzymywanie, wznawianie i skalowanie dedykowanej puli SQL na żądanie. Gdy wstrzymujesz lub zmieniasz rozmiar dedykowanej puli SQL, ważne jest zrozumienie, że wszystkie bieżące transakcje są natychmiast przerwane, co powoduje wycofanie wszystkich otwartych transakcji. Jeśli obciążenie wykonało długotrwałą i niekompletną modyfikację danych przed operacją wstrzymania lub skalowania, trzeba cofnąć ten proces. Cofnięcie tej operacji może mieć wpływ na czas potrzebny na wstrzymanie lub skalowanie dedykowanej puli SQL.

Ważne

Zarówno UPDATE, jak i DELETE są w pełni rejestrowanymi operacjami i dlatego cofanie/ponowne wykonywanie może trwać znacznie dłużej niż równoważne operacje rejestrowane minimalnie.

Najlepszym scenariuszem jest możliwość ukończenia transakcji modyfikacji danych lotu przed wstrzymaniem lub skalowaniem dedykowanej puli SQL. Jednak ten scenariusz nie zawsze może być praktyczny. Aby ograniczyć ryzyko długiego cofnięcia zmian, rozważ jedną z następujących opcji:

• Ponowne zapisywanie długotrwałych operacji przy użyciu CTAS
• Podziel operację na fragmenty; pracuj na wybranej grupie wierszy

Następne kroki

Sprawdź Transakcje w dedykowanej puli SQL, aby uzyskać więcej informacji o poziomach izolacji i limitach transakcyjnych. Aby zapoznać się z omówieniem innych najlepszych rozwiązań, zobacz Najlepsze rozwiązania dotyczące dedykowanej puli SQL.