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ługiego wycofywania.

Transakcje i rejestrowanie

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

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. Jednak chociaż ten proces gwarantuje, że zapis wprowadza 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.

• Zawsze, gdy jest to możliwe, używaj minimalnych konstrukcji rejestrowania
• Przetwarzanie danych przy użyciu partii o określonym zakresie w celu uniknięcia pojedynczych długotrwałych transakcji
• Wdrażanie wzorca przełączania partycji w przypadku dużych modyfikacji dla danej partycji

Minimalne i 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ą alokacje zakresów i tylko zmiany metadanych. W związku z tym minimalne rejestrowanie obejmuje rejestrowanie tylko informacji wymaganych do wycofania transakcji po niepowodzeniu lub w przypadku jawnego żądania (ROLLBACK TRAN). Ponieważ mniej informacji jest śledzonych w dzienniku transakcji, minimalnie zarejestrowana operacja działa lepiej niż w pełni rejestrowana operacja o podobnym rozmiarze. Ponadto, ponieważ mniej operacji zapisu odbywa się w dzienniku transakcji, generowanych jest znacznie mniejsza ilość danych dziennika, co zwiększa wydajność operacji we/wy.

Limity bezpieczeństwa transakcji mają zastosowanie tylko do w pełni zarejestrowanych operacji.

Uwaga

Minimalnie rejestrowane operacje mogą uczestniczyć w jawnych transakcjach. W miarę śledzenia wszystkich zmian w strukturach alokacji można wycofać minimalnie zarejestrowane operacje.

Minimalnie rejestrowane operacje

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

• CREATE TABLE AS SELECT (CTAS)
• WSTAWIĆ.. WYBIERZ
• CREATE INDEX
• ALTER INDEX REBUILD
• DROP INDEX
• TRUNCATE TABLE
• 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 obciążeniem zbiorczym

CTAS i INSERT... Funkcja SELECT to operacje ładowania zbiorczego. Oba te elementy mają jednak 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 co najmniej rejestrowane:

Indeks podstawowy	Scenariusz ładowania	Tryb rejestrowania
Sterta	Dowolne	Minimalny
Indeks klastrowany	Pusta tabela docelowa	Minimalny
Indeks klastrowany	Załadowane wiersze nie nakładają się na istniejące strony w obiekcie docelowym	Minimalny
Indeks klastrowany	Załadowane wiersze nakładają się na istniejące strony w obiekcie docelowym	Pełne
Indeks klastrowanego magazynu kolumn	Rozmiar >partii = 102 400 na partycję wyrównaną dystrybucję	Minimalny
Indeks klastrowanego magazynu kolumn	Rozmiar < partii 102 400 na partycję wyrównaną dystrybucję	Pełne

Warto zauważyć, że wszystkie operacje zapisu w celu zaktualizowania indeksów pomocniczych lub nieklasterowanych będą zawsze 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 rozłożone i docelowe w jednej partycji, partia będzie musiała zawierać 6 144 000 wierszy lub większe, aby być minimalnie rejestrowane podczas zapisywania w indeksie klastrowanego magazynu kolumn. 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ównomiernego rozkładu danych. Każda partycja w każdej dystrybucji musi niezależnie przekroczyć próg 102 400 wierszy, aby wstawienie było minimalnie rejestrowane w dystrybucji.

Ładowanie danych do niepustej tabeli z indeksem klastrowanym często może zawierać kombinację w pełni zarejestrowanych i minimalnych zarejestrowanych wierszy. Indeks grupowany jest zrównoważonym drzewem (b-tree) stron. Jeśli strona zapisywana w pliku zawiera już wiersze z innej transakcji, te zapisy zostaną w pełni zarejestrowane. Jeśli jednak strona jest pusta, zapis na tej stronie będzie minimalnie rejestrowany.

Optymalizowanie usuwania

DELETE to w pełni zarejestrowana operacja. Jeśli musisz usunąć dużą ilość danych w tabeli lub partycji, często ma to większe znaczenie SELECT dla danych, które chcesz zachować, co można uruchomić jako minimalnie zarejestrowaną operację. Aby wybrać dane, utwórz nową tabelę za pomocą funkcji CTAS. Po utworzeniu użyj polecenia 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 trzeba zaktualizować dużą liczbę wierszy w tabeli lub partycji, często może być znacznie bardziej wydajna, aby użyć minimalnej rejestrowanej operacji, takiej jak CTAS , w tym celu.

W poniższym przykładzie pełna aktualizacja tabeli została przekonwertowana na element 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.

Optymalizacja przy użyciu przełączania partycji

W przypadku modyfikacji na dużą skalę wewnątrz partycji tabeli ma sens wzorzec przełączania partycji. Jeśli modyfikacja danych jest znacząca i obejmuje wiele partycji, iteracja na partycjach osiąga ten sam wynik.

Kroki do wykonania przełącznika partycji są następujące:

1. Tworzenie pustej partycji
2. Wykonywanie aktualizacji jako CTAS
3. Przełączanie istniejących danych do tabeli out
4. Przełączanie w nowych danych
5. Czyszczenie danych

Jednak aby 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 przykład przełączania partycji bardziej kompaktowy.

Poniższy kod przedstawia 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

Minimalizowanie rejestrowania 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 trywialny numer, 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. W przypadku wstrzymania lub skalowania dedykowanej puli SQL ważne jest, aby zrozumieć, że wszystkie transakcje w locie zostaną natychmiast zakończone; powoduje wycofanie wszystkich otwartych transakcji. Jeśli obciążenie wystawiło długotrwałą i niekompletną modyfikację danych przed wstrzymaniem lub operacją skalowania, ta praca będzie musiała zostać cofniętą. Takie cofnięcie może wpłynąć na czas wstrzymywania lub skalowania dedykowanej puli SQL.

Ważne

Zarówno operacje, jak UPDATE i DELETE są w pełni rejestrowane, a więc te operacje cofania/ponownego wykonania mogą trwać znacznie dłużej niż równoważne minimalnie zarejestrowane operacje.

Najlepszym scenariuszem jest wpuszczanie transakcji modyfikacji danych lotów przed wstrzymaniem lub skalowaniem dedykowanej puli SQL. Jednak ten scenariusz może nie zawsze być praktyczny. Aby ograniczyć ryzyko długiego wycofywania, rozważ jedną z następujących opcji:

• Ponowne zapisywanie długotrwałych operacji przy użyciu usługi CTAS
• Podziel operację na fragmenty; działanie na podzestawie wierszy

Następne kroki

Zobacz Transakcje w dedykowanej puli SQL , aby dowiedzieć się więcej na temat poziomów izolacji i limitów transakcyjnych. Aby zapoznać się z omówieniem innych najlepszych rozwiązań, zobacz Dedykowane rozwiązania dotyczące puli SQL.