Tranzakciók optimalizálása dedikált SQL-készletben a Azure Synapse Analyticsben

Megtudhatja, hogyan optimalizálhatja a tranzakciós kód teljesítményét a dedikált SQL-készletben, miközben minimalizálhatja a hosszú visszaállítások kockázatát.

Tranzakciók és naplózás

A tranzakciók a relációs SQL-készlet motorjának fontos összetevői. A tranzakciók az adatmódosítás során használatosak. Ezek a tranzakciók lehetnek explicitek vagy implicitek. Az egyetlen INSERT, UPDATE és DELETE utasítás mind implicit tranzakciókra mutat példát. Az explicit tranzakciók a BEGIN TRAN, COMMIT TRAN vagy ROLLBACK TRAN műveletet használják. Az explicit tranzakciókat általában akkor használják, ha több módosítási utasítást kell egyetlen atomi egységben összekapcsolni.

Az SQL-készlet módosításait tranzakciónaplók követik nyomon. Minden disztribúció saját tranzakciónaplóval rendelkezik. A tranzakciónapló-írások automatikusak. Nincs szükség konfigurációra. Bár ez a folyamat garantálja az írást, ez többletterhelést jelent a rendszerben. A tranzakciós szempontból hatékony kód megírásával minimalizálhatja ezt a hatást. A tranzakciós szempontból hatékony kód nagyjából két kategóriába sorolható.

• Amikor csak lehetséges, használjon minimális naplózási szerkezeteket
• Adatok feldolgozása hatókörön belüli kötegek használatával a hosszú ideig futó egyedi tranzakciók elkerülése érdekében
• Partícióváltási minta bevezetése egy adott partíció nagy módosításaihoz

Minimális és teljes naplózás

A teljes naplózott műveletekkel ellentétben, amelyek a tranzakciónapló használatával követik nyomon az összes sorváltozást, a minimálisan naplózott műveletek csak a mértékkiosztásokat és a metaadat-változásokat követik nyomon. A minimális naplózás ezért csak azokat az adatokat naplózhatja, amelyek szükségesek a tranzakció hiba utáni visszaállításához, vagy egy explicit kéréshez (ROLLBACK TRAN). Mivel a tranzakciónaplóban sokkal kevesebb információ van nyomon követve, a minimálisan naplózott művelet jobban teljesít, mint egy hasonló méretű, teljes naplózott művelet. Továbbá, mivel kevesebb írás megy a tranzakciónaplóba, sokkal kisebb mennyiségű naplóadat jön létre, és így hatékonyabb az I/O..

A tranzakcióbiztonsági korlátozások csak a teljes naplózott műveletekre vonatkoznak.

Megjegyzés

A minimálisan naplózott műveletek részt vehetnek az explicit tranzakciókban. A foglalási struktúrák minden változásának nyomon követése után a minimálisan naplózott műveletek visszaállíthatók.

Minimálisan naplózott műveletek

A következő műveletek minimálisan naplózhatóak:

• CREATE TABLE AS SELECT (CTAS)
• BESZÚRÁSA.. VÁLASSZA KI
• CREATE INDEX
• INDEX ÚJRAÉPÍTÉSÉNEK MÓDOSÍTÁSA
• DROP INDEX
• TRUNCATE TABLE
• DROP TABLE
• ALTER TABLE SWITCH PARTITION

Megjegyzés

A belső adatáthelyezési műveleteket (például a BROADCAST és a SHUFFLE) nem érinti a tranzakció biztonsági korlátja.

Minimális naplózás tömeges terheléssel

CTAS és INSERT... A SELECT mindkét tömeges betöltési művelet. Mindkettőt befolyásolja azonban a céltábla definíciója, és a terhelési forgatókönyvtől függ. Az alábbi táblázat ismerteti, hogy a tömeges műveletek teljes vagy minimális naplózása mikor történik meg:

Elsődleges index	Betöltési forgatókönyv	Naplózási mód
Halommemória	Bármelyik	Minimális
Fürtözött index	Üres céltábla	Minimális
Fürtözött index	A betöltött sorok nem fedik át a célban lévő meglévő lapokat	Minimális
Fürtözött index	A betöltött sorok átfedésben vannak a célban lévő meglévő lapokkal	Összes
Fürtözött oszlopcentrikus index	Kötegméret >= 102 400 partícióhoz igazított eloszlás	Minimális
Fürtözött oszlopcentrikus index	Kötegméret < 102 400 partícióhoz igazított eloszlásonként	Összes

Érdemes megjegyezni, hogy a másodlagos vagy nem fürtözött indexek frissítésére szolgáló írások mindig teljes naplózott műveletek lesznek.

Fontos

Egy dedikált SQL-készlet 60 disztribúcióval rendelkezik. Ezért feltételezve, hogy az összes sor egyenletesen oszlik el, és egyetlen partíción landol, a kötegnek legalább 6 144 000 sort kell tartalmaznia ahhoz, hogy a fürtözött oszlopcentrikus indexbe való íráskor minimálisan naplózható legyen. Ha a tábla particionálva van, és a beszúrt sorok átnyúlnak a partícióhatárok között, akkor partícióhatáronként 6 144 000 sorra lesz szüksége, feltéve, hogy egyenletes az adateloszlás. Az egyes disztribúciók minden partíciójának függetlenül meg kell haladnia a 102 400 sor küszöbértékét ahhoz, hogy a beszúrás minimálisan be legyen jelentkezve az elosztásba.

Az adatok fürtözött indexszel rendelkező, nem üres táblába való betöltése gyakran teljesen naplózott és minimálisan naplózott sorok keverékét is tartalmazhatja. A fürtözött index az oldalak kiegyensúlyozott fája (b-fa). Ha a megírt lap már tartalmaz egy másik tranzakció sorait, akkor ezek az írások teljes mértékben naplózva lesznek. Ha azonban a lap üres, akkor a rendszer minimálisan naplózza az oldal írását.

Törlés optimalizálása

A DELETE egy teljes mértékben naplózott művelet. Ha nagy mennyiségű adatot kell törölnie egy táblából vagy egy partícióból, az gyakran több értelmet ad a megtartani kívánt adatoknak SELECT , amelyek minimálisan naplózott műveletként futtathatók. Az adatok kiválasztásához hozzon létre egy új táblát a CTAS használatával. A létrehozás után a RENAME paranccsal cserélje fel a régi táblát az újonnan létrehozott táblára.

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

Frissítések optimalizálása

Az UPDATE egy teljes mértékben naplózott művelet. Ha egy táblában vagy partíción nagy számú sort kell frissítenie, gyakran sokkal hatékonyabb lehet egy minimálisan naplózott művelet, például a CTAS használata.

Az alábbi példában egy teljes táblafrissítés CTAS-vé lett konvertálva, hogy a minimális naplózás lehetséges legyen.

Ebben az esetben visszamenőlegesen hozzáadunk egy kedvezményösszeget a táblázatban szereplő értékesítésekhez:

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

Megjegyzés

A nagy méretű táblák újbóli létrehozása előnyös lehet a dedikált SQL-készlet számításifeladat-kezelési funkcióinak használatával. További információ: Erőforrásosztályok a számítási feladatok kezeléséhez.

Optimalizálás partícióváltással

Ha nagy léptékű módosításokkal szembesül egy táblapartíción belül, akkor a partícióváltási minta logikus. Ha az adatmódosítás jelentős, és több partícióra is kiterjed, akkor a partíciók iterálása ugyanazt az eredményt eredményezi.

A partícióváltás végrehajtásának lépései a következők:

1. Üres partíció létrehozása
2. A "frissítés" végrehajtása CTAS-ként
3. A meglévő adatok kiváltása a kifelé táblára
4. Váltás az új adatokban
5. Az adatok törlése

A váltani kívánt partíciók azonosításához azonban hozza létre a következő segítő eljárást.

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

Ez az eljárás maximalizálja a kód újrafelhasználását, és kompaktabbá teszi a partícióváltási példát.

Az alábbi kód a korábban említett lépéseket mutatja be a teljes partícióváltási rutin eléréséhez.

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

Kis kötegekkel végzett naplózás minimalizálása

Nagy adatmódosítási műveletek esetén érdemes lehet a műveletet adattömbökre vagy kötegekre osztani a munkaegység hatókörének meghatározásához.

Az alábbi kód egy működő példa. A köteg mérete triviális számra van állítva a technika kiemeléséhez. A valóságban a köteg mérete jelentősen nagyobb lenne.

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

Szüneteltetési és skálázási útmutató

A dedikált SQL-készlet lehetővé teszi a dedikált SQL-készlet igény szerinti szüneteltetését, folytatását és skálázását . Amikor szünetelteti vagy skálázza a dedikált SQL-készletet, fontos tisztában lenni azzal, hogy a repülés közbeni tranzakciók azonnal leállnak; a nyitott tranzakciók visszagördítését okozza. Ha a számítási feladat hosszú ideig futó és hiányos adatmódosítást adott ki a szüneteltetési vagy skálázási művelet előtt, akkor ezt a munkát vissza kell vonni. A visszavonás hatással lehet a dedikált SQL-készlet szüneteltetéséhez vagy skálázásához szükséges időtartamra.

Fontos

DELETE Mindkettő UPDATE teljes mértékben naplózott művelet, így ezek a visszavonási/újraműveletek jelentősen tovább tarthatnak, mint a minimálisan naplózott műveletek.

A legjobb forgatókönyv az, ha egy dedikált SQL-készlet szüneteltetése vagy skálázása előtt engedélyezi a repülési adatmódosítási tranzakciók befejezését. Ez a forgatókönyv azonban nem mindig praktikus. A hosszú visszaállítás kockázatának mérsékléséhez fontolja meg az alábbi lehetőségek egyikét:

• Hosszú ideig futó műveletek újraírása a CTAS használatával
• Bontsa fel a műveletet adattömbökre; a sorok egy részhalmazán működik

Következő lépések

Az elkülönítési szintekről és a tranzakciós korlátokról további információt a Dedikált SQL-készlet tranzakciói című témakörben talál. A többi ajánlott eljárás áttekintését a Dedikált SQL-készlet ajánlott eljárásai című témakörben tekintheti meg.