細かな動作まで見れていないですが、非クラスター化列ストアインデックス付きのテーブルに対して、SSIS で一括ロードを実施していた際に、意識しておいた方がよいかなと思ったポイントをいくつか。
今回は、クラスター化インデックス + 非クラスター化インデックスのテーブルに対して、「フラット ファイル ソース」で読み込んで、「ADO.NET 変換先」でインポートする単純な ETL を使っています。
なお、列ストアインデックスについて困ったら Niko Neugebauer を見るのが鉄板かと。
列ストアインデックスですが、行ベースのデータと列ベースのデータを組み合わせることで実装されているため、「インポートが終わった後の状態」が
- 行データ (デルタストア) として保持されているのか
- 列ストアとして圧縮されているのか
を意識しておく必要があります。
これは、クラスター化列ストアインデックスで 10 万行 (102,400) 以上のバッチサイズでデータを一括ロードしたかや、パーティションの状態によっても変わってくるのですが、想定されているデータのインポートが終わったタイミングで以下のようなクエリを実行して、各セグメントがどれくらいの粒度で圧縮されているかを確認します。
-- 行グループの状態の取得 SELECT OBJECT_NAME(csps.object_id) AS object_name, si.name, csps.partition_number, csps.row_group_id, si.type_desc, csps.state_desc, csps.total_rows, csps.size_in_bytes, csps.deleted_rows, csps.trim_reason_desc, csps.transition_to_compressed_state_desc, csps.generation, csps.created_time, csps.closed_time FROM sys.dm_db_column_store_row_group_physical_stats csps LEFT JOIN sys.indexes si ON si.object_id = csps.object_id AND si.index_id = csps.index_id ORDER BY object_name ASC, name ASC, partition_number ASC, type_desc ASC, state_desc ASC, generation ASC OPTION (RECOMPILE)
以下はクエリの実行結果になるのですが、「9,709 行」単位圧縮されていることが確認できます。
細かな行グループが作られている場合は、
ALTER INDEX NCCIX_LINEITEM on LINEITEM REORGANIZE
を実行して、行グループのマージも検討した方がよいかと。
今回は SSIS でインポートしているのですが、SSIS でインポートする場合「DefaultBufferMaxRows」「DefaultBufferSize」の設定値も意識しておく必要がありそうです。
データ フロー パフォーマンス機能
デフォルトの設定では以下の設定が行われていますが、この値もインポート時の行数に影響してくるようです。
この設定を変更することで先ほどは「9,709 行」単位で圧縮されているデータが変化していることが確認できます。
ここまでの結果はパーティショニングしていない状態ですが、パーティショニングの有無によっても状況が変わってくるようです。
先ほどと同一のテーブル構成で、パーティショニングした非クラスター化列インデックスの状態が以下になります。
大量のデータをインポートした後に、オープン状態になっている場合は、
ALTER INDEX NCCIX_LINEITEM on LINEITEM REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)
のような形で「COMPRESS_ALL_ROW_GROUPS」を指定した再構築を実行することで、圧縮の明示的な実行も検討しておいた方がよいかのではないかと。
列ストアインデックスを使用する場合、行グループの状態がどのようになっているかを意識するのがよいのかなと。