Freigeben über

Optimieren von Transaktionen im dedizierten SQL-Pool in Azure Synapse Analytics

Erfahren Sie, wie Sie die Leistung Ihres Transaktionscodes im dedizierten SQL-Pool optimieren und gleichzeitig das Risiko für lange Rollbacks minimieren.

Transaktionen und Protokollierung

Transaktionen sind eine wichtige Komponente eines relationalen SQL-Poolmoduls. Transaktionen werden während der Datenänderung verwendet. Diese Transaktionen können explizit oder implizit sein. Einzelne INSERT-, UPDATE- und DELETE-Anweisungen sind beispiele für implizite Transaktionen. Explizite Transaktionen verwenden BEGIN TRAN, COMMIT TRAN oder ROLLBACK TRAN. Explizite Transaktionen werden in der Regel verwendet, wenn mehrere Änderungsanweisungen in einer einzelnen Atomeinheit miteinander verknüpft werden müssen.

Änderungen am SQL-Pool werden mithilfe von Transaktionsprotokollen nachverfolgt. Jede Verteilung verfügt über ein eigenes Transaktionsprotokoll. Transaktionsprotokoll-Schreibvorgänge werden automatisch ausgeführt. Es ist keine Konfiguration erforderlich. Während dieser Prozess jedoch garantiert, dass der Schreibvorgang durchgeführt wird, führt er gleichzeitig einen Overhead im System ein. Sie können diese Auswirkungen minimieren, indem Sie transaktionseffizienten Code schreiben. Transaktionseffizienter Code fällt allgemein in zwei Kategorien.

  • Verwenden sie möglichst minimale Protokollierungskonstrukte
  • Datenverarbeitung mithilfe von bereichsbezogenen Batches, um einzelne lang andauernde Transaktionen zu vermeiden
  • Verwenden eines Partitionswechselmusters für große Änderungen an einer gegebenen Partition

Minimale protokollierung im Vergleich zur vollständigen Protokollierung

Im Gegensatz zu vollständig protokollierten Vorgängen, die das Transaktionsprotokoll verwenden, um jede Zeilenänderung nachzuverfolgen, verfolgen minimal protokollierte Vorgänge nur die Umfangszuweisungen und Metadatenänderungen. Daher umfasst die minimale Protokollierung nur die Protokollierung der Informationen, die zum Zurücksetzen der Transaktion nach einem Fehler oder für eine explizite Anforderung (ROLLBACK TRAN) erforderlich sind. Da im Transaktionsprotokoll viel weniger Informationen nachverfolgt werden, führt ein minimal protokollierter Vorgang besser aus als ein vollständig protokollierter Vorgang in ähnlicher Größe. Da weniger Schreibvorgänge das Transaktionsprotokoll durchlaufen, wird eine wesentlich kleinere Menge an Logdaten generiert und ist somit weit mehr E/A-effizient.

Die Transaktionssicherheitsgrenzwerte gelten nur für vollständig protokollierte Vorgänge.

Hinweis

Minimal protokollierte Vorgänge können an expliziten Transaktionen teilnehmen. Da alle Änderungen an Zuordnungsstrukturen nachverfolgt werden, ist ein Rollback von minimal protokollierten Vorgängen möglich.

Minimal protokollierte Vorgänge

Die folgenden Vorgänge können minimal protokolliert werden:

  • CREATE TABLE AS SELECT (CTAS)
  • EINFÜGEN.. AUSWÄHLEN
  • INDEX ERSTELLEN
  • ALTER INDEX REBUILD
  • DROP INDEX
  • Tabelle kürzen
  • DROP-TABELLE
  • ALTER TABLE SWITCH PARTITION

Hinweis

Interne Datenverschiebungsvorgänge (z. B. BROADCAST und SHUFFLE) sind von der Transaktionssicherheitsgrenze nicht betroffen.

Minimale Protokollierung beim Masseneinfügen

CTAS und INSERT... SELECT sind beide Massenladevorgänge. Beide werden jedoch von der Zieltabellendefinition beeinflusst und hängen vom Ladeszenario ab. In der folgenden Tabelle wird erläutert, wann Massenvorgänge vollständig oder minimal protokolliert werden:

Primärindex Szenario Laden Protokollierungsmodus
Heap Beliebig Minimal
Gruppierter Index Leere Zieltabelle Minimal
Gruppierter Index Geladene Zeilen überlappen sich nicht mit vorhandenen Seiten im Zielsystem Minimal
Gruppierter Index Hochgeladene Zeilen überlappen sich mit vorhandenen Seiten im Zielbereich. Vollständig
Index für gruppiertes Spaltenarchiv (Clustered Columnstore Index) Batchgröße >= 102.400 pro Partitionsausrichtung Minimal
Gruppierter Columnstore-Index Batchgröße < 102.400 pro an Partitionen ausgerichteter Verteilung Vollständig

Beachten Sie, dass alle Schreibvorgänge zum Aktualisieren sekundärer oder nicht gruppierter Indizes immer vollständig protokolliert werden.

Von Bedeutung

Ein dedizierter SQL-Pool verfügt über 60 Verteilungen. Daher muss Ihr Batch 6.144.000 Zeilen oder mehr enthalten, damit sie, wenn alle Zeilen gleichmäßig verteilt sind und in einer einzigen Partition landen, beim Schreiben in einen Clustered Columnstore Index minimal protokolliert werden. Wenn die Tabelle partitioniert ist und die eingefügten Zeilen Partitionsgrenzen überschreiten, benötigen Sie 6.144.000 Zeilen pro Partitionsgrenze, vorausgesetzt, dass sie sogar Datenverteilung aufweisen. Jede Partition in jeder Verteilung muss unabhängig voneinander den Schwellenwert von 102.400 Zeilen überschreiten, damit der Einfügevorgang minimal bei der Verteilung protokolliert wird.

Das Laden von Daten in eine nicht leere Tabelle mit einem gruppierten Index kann häufig eine Mischung aus vollständig protokollierten und minimal protokollierten Zeilen enthalten. Ein gruppierter Index ist ein balancierter Baum (B-Baum), bestehend aus Seiten. Wenn die Seite, in die geschrieben wird, bereits Zeilen aus einer anderen Transaktion enthält, werden diese Schreiboperationen vollständig protokolliert. Wenn die Seite jedoch leer ist, wird der Schreibvorgang auf diese Seite minimal protokolliert.

Optimieren von Löschvorgängen

DELETE ist ein vollständig protokollierter Vorgang. Wenn Sie eine große Menge an Daten in einer Tabelle oder einer Partition löschen müssen, ist es häufig sinnvoller, SELECT der Daten auszuführen, die Sie behalten möchten. Dieser Vorgang kann als minimal protokollierte Aktion durchgeführt werden. Um die Daten auszuwählen, erstellen Sie eine neue Tabelle mit CTAS. Nachdem Sie erstellt haben, verwenden Sie RENAME , um Ihre alte Tabelle mit der neu erstellten Tabelle zu tauschen.

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

Optimieren von Updates

UPDATE ist ein vollständig protokollierter Vorgang. Wenn Sie eine große Anzahl von Zeilen in einer Tabelle oder einer Partition aktualisieren müssen, kann es oft wesentlich effizienter sein, einen minimal protokollierten Vorgang wie CTAS zu verwenden.

Im folgenden Beispiel wurde eine vollständige Tabellenaktualisierung in einen CTAS konvertiert, sodass eine minimale Protokollierung möglich ist.

In diesem Fall fügen wir rückwirkend einen Rabattbetrag zu den Verkäufen in der Tabelle hinzu:

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

Hinweis

Das Neuerstellen der großen Tabellen kann von der Verwendung dedizierter SQL-Pool-Workloadverwaltungsfeatures profitieren. Weitere Informationen finden Sie unter Ressourcenklassen für die Workloadverwaltung.

Optimieren mit Partitionswechsel

Bei großen Änderungen innerhalb einer Tabellenpartition ist ein Partitionswechselmuster sinnvoll. Wenn die Datenänderung signifikant ist und mehrere Partitionen umfasst, wird dasselbe Ergebnis durch das Iterieren über die Partitionen erzielt.

Die Schritte zum Ausführen eines Partitionswechsels lauten wie folgt:

  1. Erstellen einer leeren Partition
  2. Ausführen des "Updates" als CTAS
  3. Wechseln der vorhandenen Daten zur Out-Tabelle
  4. Zu den neuen Daten wechseln
  5. Bereinigen der Daten

Um jedoch die zu wechselnden Partitionen zu identifizieren, erstellen Sie das folgende Hilfsverfahren.

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

Dieses Verfahren maximiert die Wiederverwendung von Code und sorgt dafür, dass das Partitionswechselbeispiel kompakter bleibt.

Der folgende Code veranschaulicht die oben genannten Schritte, um eine vollständige Partitionswechselroutine zu erreichen.

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

Minimieren Sie die Protokollierung mit kleinen Batches

Bei großen Datenänderungsvorgängen kann es sinnvoll sein, den Vorgang in Blöcke oder Batches aufzuteilen, um die Arbeitseinheit zu beschränken.

Ein folgendes Codebeispiel ist ein funktionierendes Beispiel. Die Batchgröße wurde auf eine triviale Zahl festgelegt, um die Technik hervorzuheben. Tatsächlich wäre die Batchgröße erheblich größer.

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

Anleitungen für Pause und Skalierung

Mit dediziertem SQL-Pool können Sie Ihren dedizierten SQL-Pool bei Bedarf anhalten, fortsetzen und skalieren . Wenn Sie Ihren dedizierten SQL-Pool anhalten oder skalieren, ist es wichtig zu verstehen, dass alle In-Flight-Transaktionen sofort beendet werden. Dies führt dazu, dass geöffnete Transaktionen zurückgesetzt werden. Wenn Ihre Workload vor dem Unterbrechungs- oder Skalierungsvorgang eine lang andauernde und unvollständige Datenänderung durchgeführt hat, muss dieser Vorgang rückgängig gemacht werden. Diese Rückgängigmachen kann sich auf die Zeit auswirken, die zum Anhalten oder Skalieren Ihres dedizierten SQL-Pools erforderlich ist.

Von Bedeutung

Beide, UPDATE und DELETE, sind vollständig protokollierte Vorgänge, weshalb diese Rückgängig-/Wiederherstellungs-Vorgänge erheblich länger dauern können als vergleichbare Vorgänge mit minimaler Protokollierung.

Das beste Szenario besteht darin, laufende Datenänderungstransaktionen abzuschließen, bevor ein dedizierter SQL-Pool angehalten oder hochskaliert wird. Dieses Szenario ist jedoch möglicherweise nicht immer praktisch. Um das Risiko eines langen Rollbacks zu verringern, sollten Sie eine der folgenden Optionen in Betracht ziehen:

  • Erneutes Schreiben von Vorgängen mit langer Ausführung mithilfe von CTAS
  • Brechen Sie den Vorgang in Blöcke auf; arbeiten Sie mit einer Teilmenge der Zeilen.

Nächste Schritte

Weitere Informationen zu Isolationsstufen und Transaktionsgrenzwerten finden Sie unter "Transaktionen" im dedizierten SQL-Pool . Eine Übersicht über andere bewährte Methoden finden Sie unter Bewährte Methoden für dedizierte SQL-Pool.

  • Last updated on