Transacties optimaliseren in een toegewezen SQL-pool in Azure Synapse Analytics

Artikel
2025-04-02

Meer informatie over het optimaliseren van de prestaties van uw transactionele code in een toegewezen SQL-pool, terwijl u het risico voor lange terugdraaiacties minimaliseert.

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 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 voor transactielogboeken worden automatisch uitgevoerd. Er is geen configuratie vereist. Hoewel dit proces de schrijfbewerking garandeert, leidt dit echter tot een overhead in het systeem. U kunt deze impact minimaliseren door transactioneel efficiënte code te schrijven. Transactioneel efficiënte code valt breed in twee categorieën.

Minimale logboekregistratieconstructies gebruiken waar mogelijk
Gegevens verwerken met behulp van batches binnen bereik om enkelvoudige langlopende transacties te voorkomen
Een partitiewisselpatroon gebruiken voor grote wijzigingen in een bepaalde partitie

Minimale versus volledige logboekregistratie

In tegenstelling tot volledig vastgelegde bewerkingen, die het transactielogboek gebruiken om elke rijwijziging bij te houden, houden minimale vastgelegde bewerkingen de omvangstoewijzingen en wijzigingen in metagegevens 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 vastgelegde bewerking beter dan een vergelijkbare volledig vastgelegde bewerking. Bovendien, omdat er minder schrijfbewerkingen naar het transactielogboek gaan, wordt er een veel kleinere hoeveelheid logboekgegevens gegenereerd en is het dus efficiënter met I/O.

De limieten voor transactieveiligheid zijn alleen van toepassing op volledig geregistreerde bewerkingen.

Notitie

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

Minimaal vastgelegde bewerkingen

De volgende bewerkingen kunnen minimaal worden vastgelegd:

CREËER TABEL ALS SELECT (CTAS)
INVOEGEN.. SELECTEREN
INDEX AANMAKEN
ALTER INDEX REBUILD (Herbouw index)
DROP INDEX
TABEL TRUNCEREN
DROP TABLE
ALTER TABLE SWITCH PARTITION

Notitie

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

Minimale logboekregistratie met bulkladen

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 belastingsscenario. In de volgende tabel wordt uitgelegd wanneer bulkbewerkingen volledig of minimaal worden geregistreerd:

Primaire index	Laden scenario	Logboekregistratiemodus
Stapel	Welk dan ook	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 doelgebied	Volledig
Geclusterde Columnstore-Index	Batchgrootte >= 102.400 per uitgelijnde partitieverdeling	Minimaal
Geclusterde Columnstore-index	Batchgrootte < 102.400 per partitie voor uitgelijnde verdeling	Volledig

Het is de moeite waard te vermelden dat schrijfbewerkingen voor het bijwerken van secundaire of niet-geclusterde indexen altijd volledig geregistreerde bewerkingen zijn.

Belangrijk

Een toegewezen SQL-pool heeft 60 distributies. Ervan uitgaande dat alle rijen gelijkmatig zijn verdeeld en in één partitie landen, moet uw batch 6.144.000 rijen of groter bevatten om minimaal te worden geregistreerd bij het schrijven naar een geclusterde Columnstore-index. Als de tabel is gepartitioneerd en de rijen die worden ingevoegd, beslaan partitiegrenzen, hebt u 6.144.000 rijen per partitiegrens nodig, uitgaande van zelfs gegevensdistributie. Elke partitie in elke distributie moet onafhankelijk de drempelwaarde voor 102.400 rijen overschrijden, zodat de invoeging minimaal is aangemeld 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 vastgelegde rijen bevatten. Een geclusterde index is een evenwichtige structuur (b-boomstructuur) van pagina's. Als de pagina die wordt geschreven naar 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.

Het optimaliseren van verwijderingen

DELETE is een volledig geregistreerde bewerking. Als u een grote hoeveelheid gegevens in een tabel of partitie wilt verwijderen, is het vaak verstandiger om de gegevens die u wilt behouden te SELECT, wat kan 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 RENAME om uw oude tabel te wisselen met 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 te gebruiken, zoals CTAS- om dit te doen.

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

In dit geval voegen we achteraf 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 profiteren van het gebruik van beheerfuncties voor toegewezen SQL-poolworkloads. Zie Resourceklassen voor workloadbeheervoor meer informatie.

Optimaliseren met partitiewisseling

Als u te maken hebt met grootschalige wijzigingen binnen een tabelpartitie, is een partitiewisselingspatroon logisch. Als de gegevenswijziging aanzienlijk is en meerdere partities omvat, bereikt u hetzelfde resultaat door de partities te herhalen.

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 uittabel verplaatsen.
Vervang de nieuwe gegevens
De gegevens opschonen

Als u echter wilt helpen bij het identificeren van de partities die moeten worden overgeschakeld, 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 het schakelen tussen partities compacter.

De volgende code demonstreert de stappen die eerder zijn genoemd 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

Loggen minimaliseren met kleine batches

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

Een volgende code is een werkvoorbeeld. De batchgrootte is ingesteld op een triviaal getal om de techniek te markeren. In werkelijkheid is de batchgrootte aanzienlijk groter.

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 lopende transacties onmiddellijk worden beëindigd, waardoor openstaande transacties worden teruggedraaid. Als uw workload een langlopende en onvolledige gegevenswijziging heeft uitgevoerd vóór de onderbrekings- of schaalbewerking, moet dit werk ongedaan worden gemaakt. Deze ongedaan maken kan invloed hebben op de tijd die het kost om uw toegewezen SQL-pool te onderbreken of te schalen.

Belangrijk

Zowel UPDATE als DELETE zijn volledig vastgelegde bewerkingen en dus kunnen deze ongedaan maken/opnieuw uitvoeren aanzienlijk langer duren dan equivalente minimaal vastgelegde bewerkingen.

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

Herschrijf langdurige bewerkingen 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. Voor een overzicht van andere aanbevolen procedures, zie beste werkwijzen voor toegewezen SQL-pools.

Delen via

Transacties optimaliseren in een toegewezen SQL-pool in Azure Synapse Analytics

Transacties en logboekregistratie

Minimale versus volledige logboekregistratie

Minimaal vastgelegde bewerkingen

Minimale logboekregistratie met bulkladen

Het optimaliseren van verwijderingen

Updates optimaliseren

Optimaliseren met partitiewisseling

Loggen minimaliseren met kleine batches

Richtlijnen voor onderbreken en schalen

Volgende stappen

Feedback

Aanvullende resources