Optimera transaktioner i en dedikerad SQL-pool i Azure Synapse Analytics

Lär dig hur du optimerar prestanda för din transaktionskod i en dedikerad SQL-pool samtidigt som du minimerar risken för långa återställningar.

Transaktioner och loggning

Transaktioner är en viktig komponent i en relationsbaserad SQL-poolmotor. Transaktioner används vid dataändring. Dessa transaktioner kan vara explicita eller implicita. Enskilda INSERT-, UPDATE- och DELETE-instruktioner är exempel på implicita transaktioner. Explicita transaktioner använder BEGIN TRAN, COMMIT TRAN eller ROLLBACK TRAN. Explicita transaktioner används vanligtvis när flera ändringsinstruktioner måste kopplas samman i en enda atomisk enhet.

Ändringar i SQL-poolen spåras med hjälp av transaktionsloggar. Varje distribution har en egen transaktionslogg. Transaktionsloggskrivningar är automatiska. Ingen konfiguration krävs. Men även om den här processen garanterar att skrivningen medför ett omkostnader i systemet. Du kan minimera den här effekten genom att skriva transaktionseffektiv kod. Transaktionseffektiv kod delas i stort sett in i två kategorier.

• Använd minimala loggningskonstruktioner när det är möjligt
• Bearbeta data med hjälp av begränsade batchar för att undvika enskilda långvariga transaktioner
• Implementera ett mönster för partitionsväxling för stora ändringar i en viss partition

Minimal eller fullständig loggning

Till skillnad från helt loggade åtgärder, som använder transaktionsloggen för att hålla reda på varje radändring, håller minimalt loggade åtgärder reda på omfattningsallokeringar och ändringar av metadata. Därför innebär minimal loggning endast loggning av den information som krävs för att återställa transaktionen efter ett fel, eller för en explicit begäran (ROLLBACK TRAN). Eftersom mycket mindre information spåras i transaktionsloggen presterar en minimalt loggad åtgärd bättre än en helt loggad åtgärd i liknande storlek. Eftersom färre skrivningar går till transaktionsloggen genereras dessutom en mycket mindre mängd loggdata, vilket är mer I/O-effektivt.

Transaktionssäkerhetsgränserna gäller endast för helt loggade åtgärder.

Anteckning

Minimalt loggade åtgärder kan delta i explicita transaktioner. Eftersom alla ändringar i allokeringsstrukturer spåras är det möjligt att återställa minimalt loggade åtgärder.

Minimalt loggade åtgärder

Följande åtgärder kan vara minimalt loggade:

• SKAPA TABELL SOM SELECT (CTAS)
• INFOGA.. VÄLJ
• CREATE INDEX
• ALTER INDEX REBUILD
• DROP INDEX
• TRUNCATE TABLE
• DROP TABLE
• ÄNDRA TABELLVÄXELPARTITION

Anteckning

Interna dataförflyttningsåtgärder (till exempel BROADCAST och SHUFFLE) påverkas inte av transaktionssäkerhetsgränsen.

Minimal loggning med massinläsning

CTAS och INSERT... SELECT är båda massinläsningsåtgärder. Båda påverkas dock av måltabelldefinitionen och beror på belastningsscenariot. I följande tabell förklaras när massåtgärder är helt eller minimalt loggade:

Primärt index	Inläsningsscenario	Loggningsläge
Heap	Valfri	Minimal
Grupperat index	Tom måltabell	Minimal
Grupperat index	Inlästa rader överlappar inte befintliga sidor i målet	Minimal
Grupperat index	Inlästa rader överlappar befintliga sidor i målet	Fullständig
Grupperat kolumnlagringsindex	Batchstorlek >= 102 400 per partitionsjusterad fördelning	Minimal
Grupperat kolumnlagringsindex	Batchstorlek < 102 400 per partitionsjusterad distribution	Fullständig

Det är värt att notera att skrivningar för att uppdatera sekundära eller icke-klustrade index alltid kommer att vara helt loggade åtgärder.

Viktigt

En dedikerad SQL-pool har 60 distributioner. Därför måste batchen innehålla 6 144 000 rader eller större för att loggas minimalt när du skriver till ett grupperat columnstore-index, förutsatt att alla rader är jämnt fördelade och hamnar i en enda partition. Om tabellen är partitionerad och raderna som infogas sträcker sig över partitionsgränserna behöver du 6 144 000 rader per partitionsgräns, förutsatt att även datadistribution sker. Varje partition i varje distribution måste oberoende överskrida tröskelvärdet på 102 400 rader för att infogningen ska vara minimalt inloggad i fördelningen.

Inläsning av data till en tabell som inte är tom med ett grupperat index kan ofta innehålla en blandning av fullständigt loggade och minimalt loggade rader. Ett grupperat index är ett balanserat träd (b-träd) med sidor. Om sidan som skrivs till redan innehåller rader från en annan transaktion loggas dessa skrivningar fullständigt. Men om sidan är tom loggas skrivningen till den sidan minimalt.

Optimera borttagningar

DELETE är en helt loggad åtgärd. Om du behöver ta bort en stor mängd data i en tabell eller en partition är det ofta mer meningsfullt för SELECT de data som du vill behålla, som kan köras som en minimalt loggad åtgärd. Om du vill välja data skapar du en ny tabell med CTAS. När du har skapat den använder du RENAME för att växla ut den gamla tabellen med den nyligen skapade tabellen.

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

Optimera uppdateringar

UPDATE är en helt loggad åtgärd. Om du behöver uppdatera ett stort antal rader i en tabell eller en partition kan det ofta vara mycket effektivare att använda en minimalt loggad åtgärd, till exempel CTAS för att göra det.

I exemplet nedan har en fullständig tabelluppdatering konverterats till en CTAS så att minimal loggning är möjlig.

I det här fallet lägger vi retroaktivt till ett rabattbelopp till försäljningen i tabellen:

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

Anteckning

Att återskapa stora tabeller kan dra nytta av att använda arbetsbelastningshanteringsfunktioner för dedikerade SQL-pooler. Mer information finns i Resursklasser för arbetsbelastningshantering.

Optimera med partitionsväxling

Om du stöter på storskaliga ändringar i en tabellpartition är ett mönster för partitionsväxling meningsfullt. Om dataändringen är betydande och sträcker sig över flera partitioner uppnår iterering över partitionerna samma resultat.

Stegen för att utföra en partitionsväxel är följande:

1. Skapa en tom partition
2. Utför uppdateringen som en CTAS
3. Växla ut befintliga data till uttabellen
4. Växla in nya data
5. Rensa data

Men för att identifiera de partitioner som ska växlas skapar du följande hjälpprocedur.

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

Den här proceduren maximerar återanvändningen av kod och håller partitionsväxlingsexemplet mer kompakt.

Följande kod visar de steg som nämnts tidigare för att uppnå en fullständig växlingsrutin för partitioner.

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

Minimera loggning med små batchar

För stora dataändringsåtgärder kan det vara klokt att dela upp åtgärden i segment eller batchar för att begränsa arbetsenheten.

Följande kod är ett fungerande exempel. Batchstorleken har angetts till ett trivialt tal för att markera tekniken. I verkligheten skulle batchstorleken vara betydligt större.

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

Vägledning för paus och skalning

Med en dedikerad SQL-pool kan du pausa, återuppta och skala din dedikerade SQL-pool på begäran. När du pausar eller skalar din dedikerade SQL-pool är det viktigt att förstå att alla transaktioner under flygning avslutas omedelbart. vilket gör att alla öppna transaktioner återställs. Om din arbetsbelastning hade utfärdat en tidskrävande och ofullständig dataändring före paus- eller skalningsåtgärden måste det här arbetet ångras. Ångrandet hade i så fall kunnat påverka hur lång tid det tar att pausa eller skala din dedikerade SQL-pool.

Viktigt

Både UPDATE och DELETE är helt loggade åtgärder och därför kan dessa ångra/göra om-åtgärder ta betydligt längre tid än motsvarande minimalt loggade åtgärder.

Det bästa scenariot är att låta transaktioner för ändring av flygdata slutföras innan du pausar eller skalar en dedikerad SQL-pool. Det här scenariot kanske dock inte alltid är praktiskt. Om du vill minska risken för en lång återställning bör du överväga något av följande alternativ:

• Skriv om långvariga åtgärder med CTAS
• Dela upp åtgärden i segment. arbeta på en delmängd av raderna

Nästa steg

Mer information om isoleringsnivåer och transaktionsgränser finns i Transaktioner i en dedikerad SQL-pool . En översikt över andra metodtips finns i Metodtips för dedikerad SQL-pool.