Optimalizace transakcí ve vyhrazeném fondu SQL ve službě Azure Synapse Analytics

Článek
06/01/2023

Zjistěte, jak optimalizovat výkon transakčního kódu ve vyhrazeném fondu SQL a současně minimalizovat riziko dlouhých vrácení zpět.

Transakce a protokolování

Transakce jsou důležitou součástí modulu relačního fondu SQL. Transakce se používají při úpravě dat. Tyto transakce mohou být explicitní nebo implicitní. Jednotlivé příkazy INSERT, UPDATE a DELETE jsou příklady implicitních transakcí. Explicitní transakce používají BEGIN TRAN, COMMIT TRAN nebo ROLLBACK TRAN. Explicitní transakce se obvykle používají v případech, kdy je třeba v jedné atomické jednotce spojit více příkazů změn.

Změny fondu SQL se sledují pomocí transakčních protokolů. Každá distribuce má svůj vlastní transakční protokol. Zápisy do transakčního protokolu jsou automatické. Nevyžaduje se žádná konfigurace. Tento proces sice zaručuje zápis, ale v systému představuje režijní náklady. Tento dopad můžete minimalizovat napsáním transakčního kódu. Transakční efektivní kód obecně spadá do dvou kategorií.

Kdykoli je to možné, používejte minimální konstrukty protokolování.
Zpracování dat pomocí vymezených dávek, aby se zabránilo jednotnému dlouhotrvajícímu zpracování transakcí
Použití vzoru přepínání oddílů pro velké úpravy daného oddílu

Minimální vs. úplné protokolování

Na rozdíl od plně protokolovaných operací, které používají transakční protokol ke sledování každé změny řádku, udržují minimální protokolované operace přehled pouze o přidělení rozsahu a změnách metadat. Minimální protokolování proto zahrnuje protokolování pouze informací, které jsou potřeba k vrácení transakce zpět po selhání, nebo pro explicitní požadavek (ROLLBACK TRAN). Vzhledem k tomu, že v transakčním protokolu je sledováno mnohem méně informací, minimální protokolovaná operace funguje lépe než operace podobné velikosti plně protokolované. Vzhledem k tomu, že transakčním protokolem jde méně zápisů, generuje se mnohem menší množství dat protokolu, a proto je vstupně-výstupní operace efektivnější.

Limity bezpečnosti transakcí se vztahují pouze na plně protokolované operace.

Poznámka

Na explicitních transakcích se můžou podílet minimální protokolované operace. Vzhledem k tomu, že se sledují všechny změny ve strukturách přidělování, je možné vrátit minimální protokolované operace.

Minimální protokolované operace

Následující operace se dají protokolovat minimálně:

CREATE TABLE AS SELECT (CTAS)
VLOŽIT.. VYBERTE
CREATE INDEX
ALTER INDEX REBUILD
DROP INDEX
TRUNCATE TABLE
DROP TABLE
ALTER TABLE SWITCH PARTITION

Poznámka

Interní operace přesunu dat (například BROADCAST a SHUFFLE) nejsou bezpečnostními limity transakcí ovlivněny.

Minimální protokolování s hromadným načtením

CTAS a INSERT... Příkaz SELECT jsou operace hromadného načtení. Obojí je však ovlivněno definicí cílové tabulky a závisí na scénáři zatížení. Následující tabulka vysvětluje, kdy se hromadné operace protokolují úplně nebo minimálně:

Primární index	Scénář načtení	Režim protokolování
Halda	Všechny	Minimální
Clusterovaný index	Prázdná cílová tabulka	Minimální
Clusterovaný index	Načtené řádky se nepřekrývají s existujícími stránkami v cíli.	Minimální
Clusterovaný index	Načtené řádky se překrývají s existujícími stránkami v cíli.	Do bloku
Clusterovaný index columnstore	Velikost >dávky = 102 400 na distribuci zarovnanou do oddílu	Minimální
Clusterovaný index columnstore	Velikost < dávky 102 400 na distribuci zarovnanou na oddíl	Do bloku

Je třeba poznamenat, že všechny zápisy pro aktualizaci sekundárních nebo ne clusterovaných indexů budou vždy plně protokolované operace.

Důležité

Vyhrazený fond SQL má 60 distribucí. Proto za předpokladu, že jsou všechny řádky rovnoměrně distribuované a cílí do jednoho oddílu, bude vaše dávka muset obsahovat 6 144 000 řádků nebo více, aby se při zápisu do clusterovaného indexu columnstore protokolovaly minimálně. Pokud je tabulka rozdělená na oddíly a řádky, které se vkládají, zahrnují hranice oddílů, budete potřebovat 6 144 000 řádků na hranici oddílu za předpokladu rovnoměrného rozdělení dat. Každý oddíl v každé distribuci musí nezávisle překročit prahovou hodnotu 102 400 řádků, aby vložení bylo do distribuce zaznamenáno minimálně.

Načítání dat do neprázdné tabulky s clusterovaným indexem může často obsahovat kombinaci plně protokolovaných řádků s minimálním protokolem. Skupinový index je vyvážený strom (b-strom) stránek. Pokud stránka, na kterou se zapisuje, již obsahuje řádky z jiné transakce, budou tyto zápisy plně protokolovány. Pokud je však stránka prázdná, zápis na tuto stránku se zaprotokoluje minimálně.

Optimalizace odstranění

DELETE je plně protokolovaná operace. Pokud potřebujete odstranit velké množství dat v tabulce nebo oddílu, často dává větší smysl SELECT data, která chcete zachovat, a která se dají spustit jako minimální protokolovaná operace. Pokud chcete vybrat data, vytvořte novou tabulku pomocí CTAS. Po vytvoření použijte příkaz PŘEJMENOVAT k prohození staré tabulky s nově vytvořenou tabulkou.

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

Optimalizace aktualizací

UPDATE je plně protokolovaná operace. Pokud potřebujete aktualizovat velký počet řádků v tabulce nebo oddílu, může být často mnohem efektivnější použít k tomu minimálně protokolovanou operaci, jako je CTAS .

V následujícím příkladu byla úplná aktualizace tabulky převedena na CTAS, aby bylo možné minimální protokolování.

V tomto případě zpětně přidáváme k prodejům v tabulce částku slevy:

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

Poznámka

Opětovné vytváření velkých tabulek může být přínosné z používání funkcí správy úloh vyhrazeného fondu SQL. Další informace najdete v tématu Třídy prostředků pro správu úloh.

Optimalizace pomocí přepínání oddílů

Pokud se v tabulkovém oddílu setkáte s rozsáhlými úpravami, je vhodné použít vzorec přepínání oddílů. Pokud je změna dat významná a zahrnuje více oddílů, dosáhne iterace přes oddíly stejného výsledku.

Postup přepnutí oddílu je následující:

Vytvoření vyprázdnění oddílu
Proveďte aktualizaci jako CTAS.
Přepnutí existujících dat do odchozí tabulky
Přepnutí v nových datech
Vyčištění dat

Pokud ale chcete pomoct s identifikací oddílů, které se mají přepnout, vytvořte následující pomocný postup.

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

Tento postup maximalizuje opakované použití kódu a zajistí kompaktnější příklad přepínání oddílů.

Následující kód ukazuje výše uvedené kroky k dosažení úplné rutiny přepínání oddílů.

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

Minimalizace protokolování pomocí malých dávek

V případě operací velkých úprav dat může být vhodné rozdělit operaci na bloky dat nebo dávky, aby se rozsah pracovní jednotky vyčlenil.

Následující kód je funkční příklad. Velikost dávky byla nastavena na triviální číslo, které zvýrazňuje techniku. Ve skutečnosti by byla dávka výrazně větší.

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

Pokyny k pozastavení a škálování

Vyhrazený fond SQL umožňuje pozastavit, obnovit a škálovat vyhrazený fond SQL na vyžádání. Když pozastavíte nebo škálujete vyhrazený fond SQL, je důležité si uvědomit, že všechny probíhající transakce se okamžitě ukončí. způsobí vrácení všech otevřených transakcí zpět. Pokud vaše úloha před operací pozastavení nebo škálování vydala dlouho běžící a neúplné úpravy dat, bude potřeba tuto práci vrátit zpět. Toto vrácení může mít vliv na dobu potřebnou k pozastavení nebo škálování vyhrazeného fondu SQL.

Důležité

Operace i UPDATEDELETE jsou plně protokolované operace, takže tyto operace vrácení zpět nebo opakování můžou trvat výrazně déle než ekvivalentní minimálně protokolované operace.

Nejlepším scénářem je nechat transakce úprav dat v letu dokončit před pozastavením nebo škálováním vyhrazeného fondu SQL. Tento scénář ale nemusí být vždy praktický. Pokud chcete zmírnit riziko dlouhého vrácení zpět, zvažte jednu z následujících možností:

Přepsání dlouhotrvajících operací pomocí CTAS
Rozdělit operaci na bloky; provoz na podmnožině řádků

Další kroky

Další informace o úrovních izolace a limitech transakcí najdete v tématu Transakce ve vyhrazeném fondu SQL . Přehled dalších osvědčených postupů najdete v tématu Osvědčené postupy pro vyhrazený fond SQL.

Share via

Optimalizace transakcí ve vyhrazeném fondu SQL ve službě Azure Synapse Analytics

Transakce a protokolování

Minimální vs. úplné protokolování

Minimální protokolované operace

Minimální protokolování s hromadným načtením

Optimalizace odstranění

Optimalizace aktualizací

Optimalizace pomocí přepínání oddílů

Minimalizace protokolování pomocí malých dávek

Pokyny k pozastavení a škálování

Další kroky

Váš názor

Váš názor

Další materiály