列ストア インデックス - データ読み込みガイダンス

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric SQL Database

標準的な SQL 一括読み込みとトリクル挿入メソッドを使用して、列ストア インデックスにデータを読み込むためのオプションと推奨事項です。 列ストア インデックスへのデータの読み込みは、分析に備えてデータをインデックスに移動するため、すべてのデータ ウェアハウスのプロセスにおいて不可欠な要素です。

列ストア インデックスを初めて使用する場合は、 「列ストア インデックス - 概要」と「列ストア インデックスのアーキテクチャ」を参照してください。

一括読み込みとは

一括読み込みは、大量の行がデータ ストアに追加される方法を表します。 これは、行のバッチを対象とするため、データを列ストア インデックスに移動する最もパフォーマンスに優れた方法です。 一括読み込みでは、行グループを最大容量まで入れ、列ストアに直接圧縮します。 行グループごとに最小値の 102,400 行に一致しない読み込みの最後の行のみが、デルタストアに移動されます。

一括読み込みを実行するには、bcp ユーティリティ、Integration Services を使用したり、ステージング テーブルから行を選択したりすることができます。

上記の図に示すように、一括読み込みでは、

• データを事前に並べ替えません。 データは受信順に行グループに挿入されます。
• バッチ サイズが 102,400 以上の場合、行は圧縮された行グループに直接読み込まれます。 効率的な一括インポートのためにバッチ サイズ >=102400 を選択する必要があります。これは、行が最終的にバックグラウンド スレッドタプルムーバー (TM) によって圧縮された行グループに移動される前に、データ行をデルタ行グループに移動することを回避できるためです。
• バッチ サイズが < 102,400 未満の場合、または残りの行が < 102,400 の場合、行はデルタ行グループに読み込まれます。

一括読み込みには、次の内蔵されたパフォーマンス最適化があります。

• 並列読み込み: それぞれ別のデータ ファイルを読み込む、複数の同時一括読み込み (bcp または一括挿入) を行うことができます。 SQL Server への行ストア一括読み込みとは異なり、各一括インポート スレッドでは排他的ロックを使用して排他的にデータを別の行グループ (圧縮された行グループやデルタ行グループ) に読み込むため、TABLOCK を指定する必要はありません。

• ログ記録の削減: 圧縮された行グループにデータを直接読み込むと、ログのサイズが大幅に削減されます。 たとえば、データが 10 倍圧縮された場合、対応するトランザクション ログは、 TABLOCK または一括ログ/単純復旧モデルを必要とせずに約 10 倍小さくなります。 デルタ行グループに移動するデータは、完全に記録されます。 これには、102,400 行未満のバッチ サイズがすべて含まれます。 ベスト プラクティスは、102,400 以上のバッチ サイズを使用することです。 TABLOCKは必要ないため、データを並列で読み込むことができます。

• 最小ログ記録:最小ログ記録の前提条件に従うと、ログ記録をさらに削減できます。 ただし、行ストアへのデータの読み込みとは異なり、TABLOCK はテーブルに対して X (排他) ロックをかけるため、BU (一括更新) ロックではなくなり、結果としてデータの並列読み込みを行うことができません。 ロックについて詳しくは、「ロックおよび行のバージョン管理」をご覧ください。

• ロックの最適化: 圧縮された行グループにデータを読み込むと、行グループの X ロックが自動的に取得されます。 ただし、デルタ行グループへの一括読み込みでは、行グループの X ロックが取得されますが、 X 行グループ ロックはロック階層の一部ではないので、データベース エンジンは引き続きページ ロックとエクステント ロックを取得します。

列ストア インデックスに非クラスター化 B ツリー インデックスがある場合、インデックス自体のロックやログの最適化は行われませんが、前述のとおり、クラスター化列ストア インデックスの最適化は適用できます。

デルタ行グループを最小限にする一括読み込みサイズを計画する

ほとんどの行が列ストアに圧縮され、デルタ行グループに配置されていないときに、列ストア インデックスは最高のパフォーマンスを発揮します。 できるだけデルタストアを避けて、行が直接列ストアに移動するように読み込みのサイズを調整するのが最適です。

次のシナリオでは、読み込まれた行が列ストアに直接移動する場合やデルタストアに移動する場合について説明します。 この例では、行グループはそれぞれ 102,400 ～ 1,048,576 個の行を持つことができます。 実際には、行グループの最大サイズは、メモリが不足している場合、1,048,576 行より小さくなることがあります。

次の例は、1,048,577 個の行をテーブルに読み込んだ結果を示しています。 この結果では、列ストアに 1 つの圧縮された行グループ (圧縮された列セグメントとして)、およびデルタストアに 1 行があります。

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id,
    state, state_desc, total_rows, deleted_rows, size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats;

ステージング テーブルを使用してパフォーマンスを向上させる

さまざまな変換を実行する前にデータをステージングするためにのみ読み込む場合は、ヒープ テーブルにテーブルを読み込むと、データをクラスター化列ストア テーブルに読み込む場合よりもはるかに高速に読み込まれます。 さらに、[一時テーブル][Temporary] へのデータの読み込みも、永続記憶域にテーブルを読み込むよりも高速になります。

データの読み込みの一般的なパターンでは、ステージング テーブルにデータを読み込み、変換を行ってから、以下のコマンドを使用してターゲット テーブルに読み込みます。

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

このコマンドは、bcp や一括挿入と同じように列ストア インデックスにデータを読み込みますが、データは単一のバッチにまとめられます。 ステージング テーブルの行数が 102400 未満の場合、行はデルタ行グループに読み込まれ、それ以外の場合、行は圧縮された列グループに直接読み込まれます。< この INSERT 操作はシングル スレッドの場合に限られていました。 データの並列読み込みを行う場合、複数のステージング テーブルを作成するか、ステージング テーブルの重複していない行の範囲を指定して INSERT/SELECT を実行することができます。 このような制限は SQL Server 2016 (13.x) にはありません。 次のコマンドはステージング テーブルからデータを並列で読み込みますが、TABLOCK を指定する必要があります。 これは、一括読み込みについて説明した内容と矛盾していることがありますが、主な違いは、ステージング テーブルからの並列データの読み込みが同じトランザクションで実行されることです。

INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

ステージング テーブルからクラスター化列ストア インデックスへの一括読み込みで使用可能な最適化を以下に示します。

• ログの最適化: 圧縮された行グループにデータが読み込まれるときのログ記録が減りました。
• ロックの最適化: 圧縮された行グループにデータを読み込むとき、行グループの X ロックが取得されます。 ただし、デルタ行グループへの一括読み込みでは、行グループの X ロックが取得されますが、 X 行グループ ロックはロック階層の一部ではないので、データベース エンジンは引き続きページ ロックとエクステント ロックを取得します。

非クラスター化インデックスが 1 つまたは複数ある場合、インデックス自体のロックやログの最適化は行われませんが、前述のとおり、クラスター化列ストア インデックスの最適化は引き続き行われます。

トリクル挿入とは?

"トリクル挿入" は、個々の行が列ストア インデックスに移動する方法を表します。 トリクル挿入では、INSERT INTO ステートメントを使用します。 トリクル挿入を使用すると、すべての行はデルタストアに移動されます。 これは行が少数のときに便利ですが、大量の処理には実用的ではありません。

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)

列グループに 1,048,576 個の行が含まれると、デルタ行グループは閉じられた見なされますが、クエリや更新/削除操作では引き続き使用できます。ただし、新しく挿入される行は既存のデルタストア列グループまたは新しく作成されたデルタストア列グループに移動します。 タプル ムーバー (TM) と呼ばれるバックグラウンド スレッドがあり、閉じたデルタ行グループを 5 分ごとに定期的に圧縮します。 次のコマンドを明示的に呼び出すことで、閉じられたデルタ行グループを圧縮できます。

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE

デルタ行グループを強制的に閉じ、圧縮する場合は、以下のコマンドを実行します。 行の読み込みが終了し、新しい行は必要ない場合、このコマンドを実行できます。 デルタ行グループを明示的に閉じ、圧縮することで、より多くのストレージを確保し、分析クエリのパフォーマンスを向上させることができます。 新しい行を挿入する必要がない場合は、このコマンドを呼び出すことをお勧めします。

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)

パーティション テーブルへの読み込みのしくみ

パーティション分割されたデータの場合、データベース エンジンは最初に各行をパーティションに割り当て、次にパーティション内のデータに対して列ストア操作を実行します。 各パーティションには、独自の行グループと少なくとも 1 つのデルタ行グループがあります。

関連コンテンツ

• クラスター化列ストア インデックスでのデータ読み込みのパフォーマンスに関する考慮事項