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

Zjistěte, jak optimalizovat výkon transakčního kódu ve vyhrazeném fondu SQL a minimalizovat riziko zpožděného vrácení transakcí.

Transakce a protokolování

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

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

• Kdykoli je to možné, používejte minimální prvky protokolování.
• Zpracovávejte data pomocí omezených dávek, abyste se vyhnuli dlouho běžícím jednotným transakcím.
• Přijmout vzorec přepínání oddílů pro značné ú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, minimální protokolované operace sledují pouze přidělování rozsahů a změny meta-dat. Minimální protokolování proto zahrnuje protokolování pouze informací, které jsou nutné k vrácení transakce po selhání, nebo pro explicitní požadavek (ROLLBACK TRAN). Mnohem méně informací je sledováno v transakčním protokolu, minimální protokolovaná operace funguje lépe než podobně velká plně protokolovaná operace. Vzhledem k tomu, že méně zápisů směřuje do transakčního protokolu, vygeneruje se mnohem menší množství dat protokolu, což zvyšuje efektivitu vstupně-výstupních operací.

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

Poznámka:

Minimální protokolované operace se mohou účastnit explicitních transakcí. Vzhledem k tomu, že se sledují všechny změny struktur přidělování, je možné vrátit zpět minimálně protokolované operace.

Minimální protokolované operace

Následující operace mohou být protokolovány minimálně:

• VYTVOŘIT TABULKU JAKO VÝBĚR (CTAS)
• VLOŽIT.. VYBRAT
• Vytvořit index
• ZMĚNA INDEXU OBNOVENÍ
• Odstranit index
• Odstranit data z tabulky (TRUNCATE TABLE)
• The original translation remains suitable without changes if targeting an audience familiar with SQL.
• ZMĚNIT TABULKU PŘEPNOUT ODDÍL

Poznámka:

Operace interního přesunu dat (například BROADCAST a SHUFFLE) nejsou ovlivněny limitem bezpečnosti transakcí.

Minimální protokolování s hromadným nahráváním

CTAS a INSERT...SELECT jsou operace hromadného načítání. Obě jsou však ovlivněny definicí cílové tabulky a závisí na scénáři načítání. Následující tabulka vysvětluje, kdy jsou hromadné operace plně nebo minimálně zaprotokolovány:

Primární index	Načítací scénář	Režim protokolování
Halda	Jakýkoliv	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ílovém místě.	Úplný
Index clusterovaného sloupcového úložiště	Velikost dávky >= 102 400 na rozdělení zarovnané podle partitionu	Minimální
Clusterovaný index Columnstore	Velikost dávky < 102 400 podle zarovnaného rozdělení	Úplný

Stojí za zmínku, ž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é prostředí SQL má 60 distribucí. Proto, pokud předpokládáme, že všechny řádky jsou rovnoměrně rozloženy a skončí v jednom oddílu, bude muset vaše dávka obsahovat 6 144 000 řádků nebo více, aby byl zápis do clusterovaného Columnstore indexu minimálně zaprotokolován. Pokud je tabulka rozdělena na oddíly a řádky jsou vkládány napříč hranicemi oddílů, budete potřebovat 6 144 000 řádků na každou hranici oddílu při rovnoměrném rozdělení dat. Každý oddíl v každé distribuci musí nezávisle překročit prahovou hodnotu 102 400 řádků, aby záznam o vložení do distribuce byl minimální.

Načítání dat do neprázdné tabulky s clusterovaným indexem může často obsahovat kombinaci plně zaznamenaných a minimálně protokolovaných řádků. Skupinový index je vyvážený strom (b-strom) stránek. Pokud stránka, do které je zapisováno, 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, je často vhodnější SELECT data, která chcete zachovat, což může být spuštěno jako operace s minimálním záznamem do protokolu. Pokud chcete vybrat data, vytvořte novou tabulku s CTAS . Po vytvoření použijte PŘEJMENOVAT k nahrazení vaší staré tabulky 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 operaci s minimálním zalogováním, například CTAS.

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

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

--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 těžit z používání vyhrazených funkcí správy úloh fondu SQL. Další informace najdete v tématu Třídy prostředků pro správu úloh.

Optimalizace pomocí přepínání diskových oddílů

Pokud dochází k rozsáhlým úpravám uvnitř oddílu tabulky, pak dává smysl použít vzor pro přepínání oddílů. Pokud je úprava dat významná a zahrnuje více oddílů, iterace přes oddíly dosáhne stejného výsledku.

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

1. Vytvořit prázdný oddíl
2. Proveďte "aktualizaci" jako CTAS
3. Vymazání existujících dat do tabulky "out".
4. Přepněte na nová data
5. Vyčištění dat

Chcete-li identifikovat oddíly, které je třeba přepnout, vytvořte následující pomocnou proceduru.

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 udržuje příklad přepínání oddílů kompaktnějším.

Následující kód demonstruje dříve zmíněné kroky pro spuštění ú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

Minimalizujte protokolování s malými dávkami

U velkých operací úprav dat může být vhodné rozdělit operaci na bloky dat nebo dávky pro určení rozsahu jednotky práce.

Následující kód je funkční příklad. Velikost dávky byla nastavena na triviální číslo, které zvýrazní techniku. Ve skutečnosti by velikost dávky byla 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 měníte velikost vyhrazeného fondu SQL, je důležité uvědomit si, že všechny probíhající transakce se okamžitě ukončí, což způsobí, že všechny otevřené transakce budou vráceny zpět. Pokud vaše pracovní zatížení zahájilo dlouhotrvající a neúplnou úpravu dat před operací pozastavení nebo škálování, bude potřeba tuto úpravu zrušit. Toto zrušení může ovlivnit dobu potřebnou k pozastavení nebo škálování vyhrazeného fondu SQL.

Důležité

Operace UPDATE i DELETE jsou plně protokolované, takže tyto operace vrácení zpět a opakování mohou trvat výrazně déle než ekvivalentní minimálně protokolované operace.

Nejlepším scénářem je nechat transakce úprav testovacích dat dokončené 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řepište dlouhotrvající operace použitím CTAS
• Rozdělte operaci na části; provádění operace na podmnožině řádků

Další kroky

Další informace o úrovních izolace a transakčních limitech 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 vyhrazeného fondu SQL.