列ストア インデックス - データ読み込みガイダンスColumnstore indexes - Data loading guidance

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

標準的な SQL 一括読み込みとトリクル挿入メソッドを使用して、列ストア インデックスにデータを読み込むためのオプションと推奨事項です。Options and recommendations for loading data into a columnstore index by using the standard SQL bulk loading and trickle insert methods. 列ストア インデックスへのデータの読み込みは、分析に備えてデータをインデックスに移動するため、すべてのデータ ウェアハウスのプロセスにおいて不可欠な要素です。Loading data into a columnstore index is an essential part of any data warehousing process because it moves data into the index in preparation for analytics.

列ストア インデックスを初めて使用する場合は、New to columnstore indexes? 列ストア インデックス - 概要」と「列ストア インデックスのアーキテクチャ」を参照してください。See Columnstore indexes - overview and Columnstore Index Architecture.

一括読み込みとはWhat is bulk loading?

一括読み込みは、大量の行がデータ ストアに追加される方法を表します。Bulk loading refers to the way large numbers of rows are added to a data store. これは、バッチの行を対象とするため、データを列ストア インデックスに移動するために、最もパフォーマンスが良い方法です。It is the most performant way to move data into a columnstore index because it operates on batches of rows. 一括読み込みでは、行グループを最大容量まで入れ、直接列ストアに圧縮します。Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. 行グループごとに最小値の 102,400 行に一致しない読み込みの最後の行のみが、デルタストアに移動されます。Only rows at the end of a load that don't meet the minimum of 102,400 rows per rowgroup go to the deltastore.

一括読み込みを実行するには、bcp ユーティリティIntegration Services を使用したり、ステージング テーブルから行を選択したりすることができます。To perform a bulk load, you can use bcp Utility, Integration Services, or select rows from a staging table.

クラスター化列ストア インデックスへの読み込みLoading into a clustered columnstore index

上記の図に示すように、一括読み込みでは、As the diagram suggests, a bulk load:

  • データは事前に並べ替えられません。Does not pre-sort the data. データは受信順に行グループに挿入されます。Data is inserted into rowgroups in the order it is received.
  • バッチ サイズが 102400 以上の場合、行は圧縮された行グループに直接移動されます。If the batch size is >= 102400, the rows are directly into the compressed rowgroups. 効率的に一括でインポートするために 102,400 以上のバッチ サイズを選択することをお勧めします。これにより、バックグラウンド スレッドの組ムーバー (TM) により圧縮された行グループに最終的に行が移動される前に、データ行がデルタ行グループに移動されるのを回避できます。It is recommended that you choose a batch size >=102400 for efficient bulk import because you can avoid moving data rows to a delta rowgroups before the rows are eventually moved to compressed rowgroups by a background thread, Tuple mover (TM).
  • バッチ サイズが 102,400 未満の場合、または残りの行が 102,400 未満の場合、行はデルタ行グループに読み込まれます。If the batch size < 102,400 or if the remaining rows are < 102,400, the rows are loaded into delta rowgroups.

注意

非クラスター化列ストア インデックス データを含む行ストア テーブルでは、 SQL ServerSQL Server は常にベース テーブルにデータを挿入します。On a rowstore table with a nonclustered columnstore index data, SQL ServerSQL Server always inserts data into the base table. データが列ストア インデックスに直接挿入されることはありません。The data is never inserted directly into the columnstore index.

一括読み込みには、次の組み込みのパフォーマンスの最適化があります。Bulk loading has these built-in performance optimizations:

  • 並列読み込み: それぞれ別のデータ ファイルを読み込む、複数の同時一括読み込み (bcp または一括インポート) を行うことができます。Parallel loads: You can have multiple concurrent bulk loads (bcp or bulk insert) that are each loading a separate data file. SQL ServerSQL Server への行ストア一括読み込みとは異なり、各一括インポート スレッドでは排他的ロックを使用して排他的にデータを別の行グループに読み込むため、TABLOCK を指定する必要はありません。Unlike rowstore bulk loads into SQL ServerSQL Server, you don't need to specify TABLOCK because each bulk import thread will load data exclusively into separate rowgroups (compressed or delta rowgroups) with exclusive lock on it.

  • ログ記録の削減: 圧縮された行グループにデータを直接読み込むと、ログのサイズの大幅な削減につながります。Reduced Logging: The data that is directly loaded into compressed row groups leads to significant reduction in the size of the log. たとえば、データが 10 倍に圧縮された場合、対応するトランザクション ログのサイズは約 10 倍小さくなり、TABLOCK や一括ログ/単純復旧モデルは必要ありません。For example, if data was compressed 10x, the corresponding transaction log will be roughly 10x smaller without requiring TABLOCK or Bulk-logged/Simple recovery model. デルタ行グループに移動するデータは、完全に記録されます。Any data that goes to a delta rowgroup is fully logged. これには、102,400 行未満のバッチ サイズがすべて含まれます。This includes any batch sizes that are less than 102,400 rows. ベスト プラクティスは、batchsize >= 102400 を使用することです。Best practice is to use batchsize >= 102400. TABLOCK は必要ないため、データを並行して読み込むことができます。Since there is no TABLOCK required, you can load the data in parallel.

  • 最小ログ記録: 最小ログ記録の前提条件に従うと、ログ記録をさらに削減できます。Minimal logging: You can get further reduction in logging if you follow the prerequisites for minimal logging. ただし、行ストアへのデータの読み込みとは異なり、TABLOCK は、BU (一括更新) ロックではなく、テーブルで X ロックされるため、並列データの読み込みは実行できません。However, unlike loading data into a rowstore, TABLOCK leads to an X lock on the table rather than a BU (Bulk Update) lock and therefore parallel data load cannot be done. ロックの詳細については、[ロックおよび行のバージョン管理ガイド[(../sql-server-transaction-locking-and-row-versioning-guide.md) をご覧ください。For more information on locking, see [Locking and row versioning[(../sql-server-transaction-locking-and-row-versioning-guide.md).

  • ロックの最適化: 行グループの X ロックは、圧縮された行グループにデータを読み込むときに自動的に取得されます。Locking Optimization: The X lock on a row group is automatically acquired when loading data into a compressed row group. ただし、デルタ行グループへの一括読み込みの場合、X ロックは行グループで獲得されますが、X 行グループ ロックはロック階層の一部ではないため、SQL ServerSQL Server は引き続き PAGE/EXTENT をロックします。However, when bulk loading into a delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

列ストア インデックスに非クラスター化 B ツリー インデックスがある場合、インデックス自体のロックやログの最適化は行われませんが、前述のとおり、クラスター化列ストア インデックスの最適化が適用できます。If you have a nonclustered B-tree index on a columnstore index, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are applicable.

デルタ行グループを最小限にする一括読み込みサイズを計画するPlan bulk load sizes to minimize delta rowgroups

ほとんどの行が列ストアに圧縮され、デルタ行グループに配置されていないときに、列ストア インデックスは最高のパフォーマンスを発揮します。Columnstore indexes perform best when most of the rows are compressed into the columnstore and not sitting in delta rowgroups. 行が列ストアに直接移動し、できる限りデルタストアを使用しない読み込みのサイズにすることが最適です。It's best to size your loads so that rows go directly to the columnstore and bypass the deltastore as much as possible.

次のシナリオでは、読み込まれた行が列ストアに直接移動する場合やデルタストアに移動する場合について説明します。These scenarios describe when loaded rows go directly to the columnstore or when they go to the deltastore. この例では、行グループはそれぞれ 102,400 ~ 1,048,576 個の行を持つことができます。In the example, each rowgroup can have 102,400-1,048,576 rows per rowgroup. 実際には、行グループの最大サイズは、メモリが不足している場合、1,048,576 行より小さくなることがあります。In practice, the maximum size of a rowgroup can be smaller than 1,048,576 rows when there is memory pressure.

一括読み込みを行う行Rows to Bulk Load 圧縮された行グループに追加される行Rows Added to the Compressed Rowgroup デルタ行グループに追加される行Rows Added to the Delta Rowgroup
102,000102,000 00 102,000102,000
145,000145,000 145,000145,000

行グループのサイズ:145,000Rowgroup size: 145,000
00
1,048,5771,048,577 1,048,5761,048,576

行グループのサイズ:1,048,576Rowgroup size: 1,048,576.
11
2,252,1522,252,152 2,252,1522,252,152

行グループのサイズ:1,048,576、1,048,576、155,000Rowgroup sizes: 1,048,576, 1,048,576, 155,000.
00
     

次の例は、1,048,577 個の行をテーブルに読み込んだ結果を示しています。The following example shows the results of loading 1,048,577 rows into a table. この結果では、列ストアに 1 つの圧縮された行グループ (圧縮された列セグメントとして)、およびデルタストアに 1 行があります。The results show that one COMPRESSED rowgroup in the columnstore (as compressed column segments), and 1 row in the deltastore.

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  

一括読み込みの行グループとデルタストアRowgroup and deltastore for a batch load

ステージング テーブルを使用してパフォーマンスを向上させるUse a staging table to improve performance

さらに変換を実行する前で、ステージングにのみデータを読み込んでいる場合は、ヒープ テーブルにデータを読み込むほうが、クラスター化列ストア テーブルにデータを読み込むよりも高速になります。If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. さらに、[一時テーブル][Temporary] へのデータの読み込みも、永続記憶域にテーブルを読み込むよりも高速になります。In addition, loading data to a [temporary table][Temporary] will also load much faster than loading a table to permanent storage.

データの読み込みの一般的なパターンでは、ステージング テーブルにデータを読み込み、変換を行ってから、以下のコマンドを使用してターゲット テーブルに読み込みます。A common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

INSERT INTO <columnstore index>  
SELECT <list of columns> FROM <Staging Table>  

このコマンドでは、BCP や一括挿入と同じように列ストア インデックスにデータを読み込みますが、データは単一のバッチにまとめられます。This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. ステージング テーブルの行数が 102400 未満の場合、行はデルタ行グループに読み込まれ、それ以外の場合、行は圧縮された列グループに直接読み込まれます。If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly loaded into compressed rowgroup. この INSERT 操作はシングル スレッドの場合に限られていました。One key limitation was that this INSERT operation was single threaded. データの並列読み込みを行う場合、複数のステージング テーブルを作成するか、ステージング テーブルの重複していない行の範囲を指定して INSERT/SELECT を実行することができます。To load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from the staging table. このような制限は SQL Server 2016 (13.x)SQL Server 2016 (13.x) にはありません。This limitation goes away with SQL Server 2016 (13.x)SQL Server 2016 (13.x). 次のコマンドではステージング テーブルからデータを並列で読み込みますが、TABLOCK を指定する必要があります。The command below loads the data from staging table in parallel but you will need to specify TABLOCK. これは、一括読み込みについて説明した内容と矛盾していることがありますが、主な違いは、ステージング テーブルからの並列データの読み込みが同じトランザクションで実行されることです。You may find this contradictory to what was said earlier with bulkload but the key difference is the parallel data load from the staging table is executed under the same transaction.

INSERT INTO <columnstore index> WITH (TABLOCK) 
SELECT <list of columns> FROM <Staging Table>  

ステージング テーブルからクラスター化列ストア インデックスへの一括読み込みで使用可能な最適化を以下に示します。There are following optimizations available when loading into clustered columnstore index from staging table:

  • ログの最適化: 圧縮された行グループにデータが読み込まれる場合、ログ記録が削減されます。Log Optimization: Reduced logging when the data is loaded into compressed rowgroup.
  • ロックの最適化: 圧縮された行グループに読み込む場合は、行グループに対する X ロックが獲得されます。Locking Optimization: When loading into compressed rowgroup, the X lock on rowgroup is acquired. ただし、デルタ行グループでは、X ロックは行グループで獲得されますが、X 行グループ ロックはロック階層の一部ではないため、SQL ServerSQL Server は引き続き PAGE/EXTENT のロックを行います。However, with delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

非クラスター化インデックスがある場合、インデックス自体のロックやログの最適化は行われませんが、前述のとおり、クラスター化列ストア インデックスの最適化は引き続き行われます。If you have or more nonclustered indexes, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are still there

トリクル挿入とはWhat is trickle insert?

トリクル挿入は、個々の行が列ストア インデックスに移動する方法を表します。Trickle insert refers to the way individual rows move into the columnstore index. トリクル挿入では、INSERT INTO ステートメントを使用します。Trickle inserts use the INSERT INTO statement. トリクル挿入を使用すると、すべての行はデルタストアに移動されます。With trickle insert, all of the rows go to the deltastore. これは行が少数のときに便利ですが、大量の読み込みは実用的ではありません。This is useful for small numbers of rows, but not practical for large loads.

INSERT INTO <table-name> VALUES (<set of values>)  

注意

クラスター化列ストア インデックスに値を挿入するために INSERT INTO を使用する並行スレッドでは、同じデルタストア行グループに行が挿入される場合があります。Concurrent threads using INSERT INTO to insert values into a clustered columnstore index can insert rows into the same deltastore rowgroup.

列グループに 1,048, 576 個の行が含まれると、デルタ行グループは閉じられたと見なされますが、クエリや更新/削除操作では引き続き使用できます。ただし、新しく挿入される行は既存のデルタストア列グループまたは新しく作成されたデルタストア列グループに移動します。Once the rowgroup contains 1,048,576 rows, the delta rowgroup us marked closed but it is still available for queries and update/delete operations but the newly inserted rows go into an existing or newly created deltastore rowgroup. 閉じられたデルタ行グループを 5 分程度ごとに定期的に圧縮する 組ムーバー (TM) というバックグラウンド スレッドがあります。There is a background thread Tuple Mover (TM) that compresses the closed delta rowgroups periodically every 5 minutes or so. 閉じられたデルタ行グループを圧縮する場合、次のコマンドを明示的に呼び出すことができます。You can explicitly invoke the following command to compress the closed delta rowgroup

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

デルタ行グループを強制的に閉じ、圧縮する場合は、以下のコマンドを実行できます。If you want force a delta rowgroup closed and compressed, you can execute the following command. 行の読み込みが終了し、新しい行は必要ない場合、このコマンドを実行できます。You may want run this command if you are done loading the rows and don't expect any new rows. デルタ行グループを明示的に閉じ、圧縮することで、より多くのストレージを確保し、分析クエリのパフォーマンスを向上させることができます。By explicitly closing and compressing the delta rowgroup, you can save storage further and improve the analytics query performance. 新しい行を挿入する必要がない場合は、このコマンドを呼び出すことをお勧めします。A best practice is to invoke this command if you don't expect new rows to be inserted.

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

パーティション テーブルへの読み込みのしくみHow loading into a partitioned table works

パーティション分割されたデータの場合、 SQL ServerSQL Server はまずパーティションに各行を割り当て、次にパーティション内のデータの columnstore 処理を実行します。For partitioned data, SQL ServerSQL Server first assigns each row to a partition, and then performs columnstore operations on the data within the partition. 各パーティションには、独自の行グループと少なくとも 1 つのデルタ行グループがあります。Each partition has its own rowgroups and at least one delta rowgroup.

次の手順Next steps

2015 年 3 月 11 日に記述されたブログが techcommunity でホストされています。Data Loading performance considerations with Clustered Columnstore indexes (クラスター化列ストア インデックスでのデータ読み込みのパフォーマンスに関する考慮事項)Blog post now hosted on techcommunity, written 2015-03-11: Data Loading performance considerations with Clustered Columnstore indexes.