Transacties optimaliseren in toegewezen SQL-pool in Azure Synapse Analytics

Artikel
06/01/2023

Meer informatie over het optimaliseren van de prestaties van uw transactionele code in een toegewezen SQL-pool terwijl het risico op lange terugdraaiacties wordt geminimaliseerd.

Transacties en logboekregistratie

Transacties zijn een belangrijk onderdeel van een relationele SQL-poolengine. Transacties worden gebruikt tijdens het wijzigen van gegevens. Deze transacties kunnen expliciet of impliciet zijn. Enkele INSERT-, UPDATE- en DELETE-instructies zijn allemaal voorbeelden van impliciete transacties. Expliciete transacties maken gebruik van BEGIN TRAN, COMMIT TRAN of ROLLBACK TRAN. Expliciete transacties worden doorgaans gebruikt wanneer meerdere wijzigingsinstructies aan elkaar moeten worden gekoppeld in één atomische eenheid.

Wijzigingen in de SQL-pool worden bijgehouden met behulp van transactielogboeken. Elke distributie heeft een eigen transactielogboek. Schrijfbewerkingen in transactielogboeken worden automatisch uitgevoerd. Er is geen configuratie vereist. Hoewel dit proces de schrijfbewerking garandeert, leidt het wel tot overhead in het systeem. U kunt deze impact minimaliseren door transactioneel efficiënte code te schrijven. Transactioneel efficiënte code valt in grote lijnen in twee categorieën.

Gebruik waar mogelijk minimale logboekconstructies
Gegevens verwerken met behulp van scoped batches om langlopende transacties te voorkomen
Een partitiewisselpatroon gebruiken voor grote wijzigingen in een bepaalde partitie

Minimale versus volledige logboekregistratie

In tegenstelling tot volledig geregistreerde bewerkingen, die het transactielogboek gebruiken om elke rijwijziging bij te houden, houden minimaal geregistreerde bewerkingen alleen omvangtoewijzingen en metagegevenswijzigingen bij. Daarom omvat minimale logboekregistratie alleen de informatie die nodig is om de transactie terug te draaien na een fout of voor een expliciete aanvraag (ROLLBACK TRAN). Omdat er veel minder informatie wordt bijgehouden in het transactielogboek, presteert een minimaal geregistreerde bewerking beter dan een volledig geregistreerde bewerking van vergelijkbare grootte. Omdat er minder schrijfbewerkingen naar het transactielogboek gaan, wordt bovendien een veel kleinere hoeveelheid logboekgegevens gegenereerd en is de I/O-efficiëntie dus efficiënter.

De veiligheidslimieten voor transacties zijn alleen van toepassing op volledig geregistreerde bewerkingen.

Notitie

Minimaal geregistreerde bewerkingen kunnen deelnemen aan expliciete transacties. Omdat alle wijzigingen in toewijzingsstructuren worden bijgehouden, is het mogelijk om minimaal vastgelegde bewerkingen terug te draaien.

Minimaal geregistreerde bewerkingen

De volgende bewerkingen kunnen minimaal worden geregistreerd:

CREATE TABLE AS SELECT (CTAS)
INVOEGEN.. SELECTEER
CREATE INDEX
ALTER INDEX REBUILD
DROP INDEX
TRUNCATE TABLE
DROP TABLE
ALTER TABLE SWITCH PARTITION

Notitie

Interne gegevensverplaatsingsbewerkingen (zoals BROADCAST en SHUFFLE) worden niet beïnvloed door de veiligheidslimiet voor transacties.

Minimale logboekregistratie met bulksgewijs laden

CTAS en INSERT... SELECT zijn beide bewerkingen voor bulksgewijs laden. Beide worden echter beïnvloed door de definitie van de doeltabel en zijn afhankelijk van het laadscenario. In de volgende tabel wordt uitgelegd wanneer bulkbewerkingen volledig of minimaal worden geregistreerd:

Primaire index	Scenario laden	Modus voor logboekregistratie
Heap	Alle	Minimaal
Geclusterde index	Lege doeltabel	Minimaal
Geclusterde index	Geladen rijen overlappen niet met bestaande pagina's in het doel	Minimaal
Geclusterde index	Geladen rijen overlappen met bestaande pagina's in het doel	Volledig
Geclusterde columnstore-index	Batchgrootte >= 102.400 per uitgelijnde partitieverdeling	Minimaal
Geclusterde columnstore-index	Batchgrootte < 102.400 per partitie uitgelijnde distributie	Volledig

Het is vermeldenswaardig dat schrijfbewerkingen voor het bijwerken van secundaire of niet-geclusterde indexen altijd volledig geregistreerde bewerkingen zijn.

Belangrijk

Een toegewezen SQL-pool heeft 60 distributies. Daarom moet uw batch 6.144.000 rijen of groter bevatten om minimaal te kunnen worden geregistreerd wanneer u naar een geclusterde columnstore-index schrijft. Als de tabel is gepartitioneerd en de rijen die worden ingevoegd partitiegrenzen omvatten, hebt u 6.144.000 rijen per partitiegrens nodig, uitgaande van een gelijkmatige gegevensdistributie. Elke partitie in elke distributie moet onafhankelijk de drempelwaarde van 102.400 rijen overschrijden om de insert minimaal te laten aanmelden bij de distributie.

Het laden van gegevens in een niet-lege tabel met een geclusterde index kan vaak een combinatie van volledig geregistreerde en minimaal geregistreerde rijen bevatten. Een geclusterde index is een evenwichtige structuur (b-structuur) van pagina's. Als de pagina naar wordt geschreven al rijen van een andere transactie bevat, worden deze schrijfbewerkingen volledig geregistreerd. Als de pagina echter leeg is, wordt de schrijfbewerking naar die pagina minimaal geregistreerd.

Verwijderingen optimaliseren

DELETE is een volledig geregistreerde bewerking. Als u een grote hoeveelheid gegevens in een tabel of partitie wilt verwijderen, is het vaak logischer om SELECT de gegevens die u wilt behouden, die kunnen worden uitgevoerd als een minimaal vastgelegde bewerking. Als u de gegevens wilt selecteren, maakt u een nieuwe tabel met CTAS. Nadat u de tabel hebt gemaakt, gebruikt u NAAM WIJZIGEN om de oude tabel te vervangen door de zojuist gemaakte 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];

Updates optimaliseren

UPDATE is een volledig geregistreerde bewerking. Als u een groot aantal rijen in een tabel of partitie moet bijwerken, kan het vaak veel efficiënter zijn om een minimaal vastgelegde bewerking zoals CTAS te gebruiken om dit te doen.

In het onderstaande voorbeeld is een volledige tabelupdate geconverteerd naar een CTAS, zodat een minimale logboekregistratie mogelijk is.

In dit geval voegen we met terugwerkende kracht een kortingsbedrag toe aan de verkoop in de tabel:

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

Notitie

Het opnieuw maken van grote tabellen kan baat hebben bij het gebruik van toegewezen workloadbeheerfuncties voor SQL-pools. Zie Resourceklassen voor workloadbeheer voor meer informatie.

Optimaliseren met partitiewisseling

Als u te maken krijgt met grootschalige wijzigingen in een tabelpartitie, is een patroon voor partitiewisseling logisch. Als de gegevenswijziging significant is en meerdere partities omvat, krijgt het herhalen van de partities hetzelfde resultaat.

De stappen voor het uitvoeren van een partitieswitch zijn als volgt:

Een lege partitie maken
De 'update' uitvoeren als een CTAS
De bestaande gegevens naar de out-tabel verplaatsen
Schakelen tussen de nieuwe gegevens
De gegevens opschonen

Als u echter wilt helpen bij het identificeren van de partities om over te schakelen, maakt u de volgende helperprocedure.

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

Met deze procedure wordt het hergebruik van code gemaximaliseerd en blijft het voorbeeld van partitiewisseling compacter.

De volgende code demonstreert de eerder genoemde stappen om een volledige partitiewisselroutine te bereiken.

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

Logboekregistratie minimaliseren met kleine batches

Voor bewerkingen voor het wijzigen van grote gegevens kan het zinvol zijn om de bewerking op te delen in segmenten of batches om het bereik van de werkeenheid te bepalen.

De volgende code is een werkend voorbeeld. De batchgrootte is ingesteld op een klein getal om de techniek te markeren. In werkelijkheid zou de batchgrootte aanzienlijk groter zijn.

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

Richtlijnen voor onderbreken en schalen

Met een toegewezen SQL-pool kunt u uw toegewezen SQL-pool op aanvraag onderbreken, hervatten en schalen . Wanneer u uw toegewezen SQL-pool onderbreekt of schaalt, is het belangrijk om te begrijpen dat alle in-flight transacties onmiddellijk worden beëindigd. waardoor alle openstaande transacties worden teruggedraaid. Als uw workload een langdurige en onvolledige gegevenswijziging heeft uitgevoerd vóór de onderbrekings- of schaalbewerking, moet dit werk ongedaan worden gemaakt. Dit ongedaan maken kan van invloed zijn op de tijd die nodig is om uw toegewezen SQL-pool te onderbreken of te schalen.

Belangrijk

Zowel UPDATE als DELETE zijn volledig geregistreerde bewerkingen, waardoor deze bewerkingen voor ongedaan maken/opnieuw uitvoeren aanzienlijk langer kunnen duren dan equivalente minimaal vastgelegde bewerkingen.

Het beste scenario is om vluchtgegevenswijzigingstransacties te laten voltooien voordat een toegewezen SQL-pool wordt onderbroken of geschaald. Dit scenario is echter niet altijd praktisch. Als u het risico van een lange terugdraaiactie wilt beperken, kunt u een van de volgende opties overwegen:

Langlopende bewerkingen herschrijven met CTAS
De bewerking opsplitsen in segmenten; werken op een subset van de rijen

Volgende stappen

Zie Transacties in toegewezen SQL-pool voor meer informatie over isolatieniveaus en transactionele limieten. Zie Aanbevolen procedures voor toegewezen SQL-pools voor een overzicht van andere best practices.

Delen via

Transacties optimaliseren in toegewezen SQL-pool in Azure Synapse Analytics

Transacties en logboekregistratie

Minimale versus volledige logboekregistratie

Minimaal geregistreerde bewerkingen

Minimale logboekregistratie met bulksgewijs laden

Verwijderingen optimaliseren

Updates optimaliseren

Optimaliseren met partitiewisseling

Logboekregistratie minimaliseren met kleine batches

Richtlijnen voor onderbreken en schalen

Volgende stappen

Feedback

Aanvullende resources