Azure Synapse Analytics の専用 SQL プールでのトランザクションの最適化

長いロールバックのリスクを最小限に抑えながら、専用 SQL プールでトランザクションコードのパフォーマンスを最適化する方法について説明します。

トランザクションとログ記録

トランザクションは、リレーショナル SQL プールエンジンの重要なコンポーネントです。トランザクションは、データの変更中に使用されます。これらのトランザクションは、明示的または暗黙的にすることができます。単一の INSERT、UPDATE、DELETE ステートメントはすべて、暗黙的なトランザクションの例です。明示的なトランザクションでは、BEGIN TRAN、COMMIT TRAN、または ROLLBACK TRAN が使用されます。明示的なトランザクションは、通常、複数の変更ステートメントを 1 つのアトミック単位で結び付ける必要がある場合に使用されます。

SQL プールへの変更は、トランザクションログを使用して追跡されます。各ディストリビューションには、独自のトランザクションログがあります。トランザクションログの書き込みは自動的に行われます。構成は必要ありません。ただし、このプロセスでは書き込みが保証されますが、システムにオーバーヘッドが発生します。トランザクション効率の高いコードを記述することで、この影響を最小限に抑えることができます。トランザクション効率の高いコードは、大きく分けて 2 つのカテゴリに分類されます。

可能な限り最小限のログ記録コンストラクトを使用する
単一の実行時間の長いトランザクションを回避するために、スコープ付きバッチを使用してデータを処理する
特定のパーティションに大きな変更を加えるためにパーティション切り替えパターンを採用する

最小ログ記録と完全ログ記録

トランザクションログを使用してすべての行の変更を追跡する完全にログに記録される操作とは異なり、最小ログ記録操作ではエクステントの割り当てとメタデータの変更のみが追跡されます。したがって、最小ログ記録では、障害発生後にトランザクションをロールバックするために必要な情報、または明示的な要求 (ROLLBACK TRAN) に必要な情報のみをログに記録します。トランザクションログで追跡される情報がはるかに少ないほど、最小ログ記録操作は、同様のサイズの完全にログに記録された操作よりも優れたパフォーマンスを発揮します。さらに、トランザクションログに対する書き込みが少なくなるため、生成されるログデータの量がはるかに少なくなるため、I/O の効率が向上します。

トランザクションの安全性の制限は、完全にログに記録された操作にのみ適用されます。

注

最小ログ記録操作は、明示的なトランザクションに参加できます。割り当て構造のすべての変更が追跡されるため、最小ログ記録操作をロールバックできます。

最小ログ記録操作

次の操作は、最小限のログ記録が可能です。

CREATE TABLE AS SELECT (CTAS)
挿入。。選択
インデックスを作成
ALTER INDEX REBUILD
DROP INDEX
TRUNCATE TABLE
DROP TABLE
テーブルを変更してパーティションを切り替える (ALTER TABLE SWITCH PARTITION)

注

内部データ移動操作 (BROADCAST や SHUFFLE など) は、トランザクションの安全性制限の影響を受けません。

一括読み込みによる最小ログ記録

CTAS と INSERT...SELECT はどちらも一括読み込み操作です。ただし、どちらもターゲットテーブル定義の影響を受け、読み込みシナリオによって異なります。次の表では、一括操作が完全にログに記録されるか、最小ログに記録されるかについて説明します。

プライマリインデックス	読み込みシナリオ	ログモード
ヒープ	[任意]	最小限
クラスター化インデックス	空のターゲット・テーブル	最小限
クラスター化インデックス	読み込まれた行がターゲット内の既存のページと重複しない	最小限
クラスター化インデックス	読み込まれた行がターゲット内の既存のページと重複している	完全
クラスター化列ストアインデックス	パーティションに合わせて整列されたディストリビューションあたりのバッチサイズが 102,400 以上>	最小限
クラスター化列ストアインデックス	パーティションに合わせて整列されたディストリビューションあたりのバッチサイズが 102,400 以上<	完全

セカンダリインデックスまたは非クラスター化インデックスを更新するための書き込みは、常に完全にログに記録される操作になることに注意してください。

Important

専用 SQL プールには 60 個のディストリビューションがあります。そのため、すべての行が均等に分散され、1 つのパーティションに着陸すると仮定すると、クラスター化列ストアインデックスに書き込むときに最小ログに記録されるように、バッチには 6,144,000 行以上を含める必要があります。テーブルがパーティション分割されていて、挿入される行がパーティション境界をまたがる場合は、データ分散であってもパーティション境界あたり 6,144,000 行が必要です。各ディストリビューション内の各パーティションに含める行は、ディストリビューションへの挿入に対する最小ログ記録のしきい値である 102,400 行を超える必要があります。

クラスター化インデックスを使用して空でないテーブルにデータを読み込むには、多くの場合、完全にログに記録された行と最小ログ記録の行が混在している場合があります。クラスター化インデックスは、ページのバランスツリー (b ツリー) です。書き込み対象のページに別のトランザクションの行が既に含まれている場合、これらの書き込みが完全にログに記録されます。ただし、ページが空の場合、そのページへの書き込みは最小限に記録されます。

削除の最適化

DELETE は、完全にログに記録された操作です。テーブルまたはパーティション内の大量のデータを削除する必要がある場合は、保持するデータを SELECT する方が理にかなっています。これは、最小限のログ記録操作として実行できます。データを選択するには、 CTAS を使用して新しいテーブルを作成します。作成したら、 RENAME を使用して、古いテーブルと新しく作成されたテーブルを交換します。

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

更新プログラムの最適化

UPDATE は、完全にログに記録された操作です。テーブルまたはパーティション内の多数の行を更新する必要がある場合は、 CTAS などの最小限のログ記録操作を使用する方がはるかに効率的な場合があります。

次の例では、最小ログ記録が可能になるように、完全なテーブル更新が CTAS に変換されています。

この例では、テーブル内の売上に割引金額をさかのぼって追加しています。

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

注

大きなテーブルを再作成すると、専用の SQL プールワークロード管理機能を使用するとメリットが得られます。詳細については、ワークロード管理のリソースクラスを参照してください。

パーティション切り替えによる最適化

テーブルパーティション内で大規模な変更に直面した場合は、パーティション切り替えパターンが理にかなっています。データの変更が重要であり、複数のパーティションにまたがる場合、パーティションを反復処理すると同じ結果が得られます。

パーティション切り替えを実行する手順は次のとおりです。

空のパーティションを作成する
CTAS として 'update' を実行する
既存のデータを out テーブルに切り替える
新しいデータを切り替える
データをクリーンアップする

ただし、切り替えるパーティションを特定するには、次のヘルパープロシージャを作成します。

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

この手順では、コードの再利用を最大化し、パーティション切り替えの例をよりコンパクトに保ちます。

次のコードは、完全なパーティション切り替えルーチンを実現するために前述した手順を示しています。

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

小さなバッチでログ記録を削減する

大規模なデータ変更操作の場合、作業単位のスコープを設定するために、操作をチャンクまたはバッチに分割することが理にかなっている場合があります。

次のコードは、有効な例です。バッチサイズは、手法を強調するために単純な数に設定されています。実際には、バッチサイズは大幅に大きくなります。

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

一時停止とスケーリングのガイダンス

専用 SQL プールを使用すると、必要に応じて専用 SQL プールを一時停止、再開、スケーリングできます。専用 SQL プールを一時停止またはスケーリングするときは、実行中のトランザクションが直ちに終了することを理解することが重要です。開いているトランザクションがロールバックされる原因となります。一時停止操作やスケール操作の前にワークロードによって時間のかかるデータ変更が発行されており、完了していない場合は、この作業を元に戻す必要があります。この元に戻す操作によって、専用 SQL プールの一時停止またはスケーリングの実行時間に影響が出る場合があります。

Important

UPDATEとDELETEの両方が完全にログに記録されるため、これらの元に戻す/やり直す操作は、同等の最小ログ記録操作よりも大幅に時間がかかる場合があります。

最適なシナリオは、専用 SQL プールを一時停止またはスケーリングする前に、フライトデータ変更トランザクションを完了することです。ただし、このシナリオは必ずしも実用的であるとは限りません。長いロールバックのリスクを軽減するには、次のいずれかのオプションを検討してください。

CTAS を使用して実行時間の長い操作を書き換える
操作を部分に分割し、行のサブセットを処理する

次のステップ

分離レベルとトランザクション制限の詳細については、専用 SQL プールのトランザクションを参照してください。その他のベストプラクティスの概要については、専用 SQL プールのベストプラクティスに関するページを参照してください。

フィードバック

このページはお役に立ちましたか?

Last updated on 2026-03-26