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. Enkla INSERT-, UPDATE- och DELETE-instruktioner är alla 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. Det krävs ingen konfiguration. Dock, även om den här processen garanterar skrivning, medför den en omkostnad 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 begränsade batchar för att undvika enstaka tidskrävande transaktioner
• Använda ett mönster för partitionsväxling för stora ändringar i en viss partition

Minimal eller fullständig loggning

Till skillnad från fullständigt 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 fullloggad åtgärd i liknande storlek. Eftersom färre skrivningar går till transaktionsloggen genereras dessutom en mycket mindre mängd loggdata och det är också mer I/O-effektivt.

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

Minimalt loggade åtgärder

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

• SKAPA TABELL SOM SELECT (CTAS)
• INSERT..SELECT
• skapa index
• ALTER INDEX REBUILD
• DROP INDEX
• TRUNKERA TABELL
• TA BORT TABELL
• ALTER TABLE SWITCH PARTITION

Minimal loggning med massinläsning

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

Det är värt att notera att alla skrivningar som uppdaterar sekundära eller icke-klustrade index alltid kommer att vara fullständigt loggade åtgärder.

Att ladda in data i en icke-tom tabell med ett grupperat index kan leda till 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. Dock, om sidan är tom, kommer skrivningen på den sidan att loggas 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 att SELECT de data som du vill behålla, vilket 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 din gamla tabell 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 i efterhand till ett rabattbelopp på 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]

Optimera med partitionsväxling

Om du ställs inför 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. Byt till nya data
5. Rensa data

Skapa dock följande hjälpprocedur för att identifiera de partitioner som ska växlas.

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 exemplet på partitionsväxling mer kompakt.

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

--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 själva verket 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 arbetsuppgift hade utfärdat en tidskrävande och ofullständig dataändring före paus- eller skalningsoperationen måste det här arbetet ångras. Den här ångraningen kan påverka den tid det tar att pausa eller skala din dedikerade SQL-pool.

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. Men det här scenariot kanske inte alltid är praktiskt. Om du vill minska risken för en lång återgång bör du överväga något av följande alternativ:

• Skriv om tidskrävande operationer med hjälp av 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 dedikerade SQL- pooler. För en översikt över andra bästa praxis, se bästa praxis för dedikerad SQL-pool.