SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

非クラスター化列ストアインデックスが設定されているテーブルに対して、ETL でデータ挿入をする際に意識しておきたいと思ったポイント

leave a comment

細かな動作まで見れていないですが、非クラスター化列ストアインデックス付きのテーブルに対して、SSIS で一括ロードを実施していた際に、意識しておいた方がよいかなと思ったポイントをいくつか。

今回は、クラスター化インデックス + 非クラスター化インデックスのテーブルに対して、「フラット ファイル ソース」で読み込んで、「ADO.NET 変換先」でインポートする単純な ETL を使っています。
image

なお、列ストアインデックスについて困ったら 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 行」単位圧縮されていることが確認できます。

image

細かな行グループが作られている場合は、

ALTER INDEX NCCIX_LINEITEM on LINEITEM REORGANIZE

を実行して、行グループのマージも検討した方がよいかと。

今回は SSIS でインポートしているのですが、SSIS でインポートする場合「DefaultBufferMaxRows」「DefaultBufferSize」の設定値も意識しておく必要がありそうです。

データ フロー パフォーマンス機能

デフォルトの設定では以下の設定が行われていますが、この値もインポート時の行数に影響してくるようです。

image

この設定を変更することで先ほどは「9,709 行」単位で圧縮されているデータが変化していることが確認できます。

image

ここまでの結果はパーティショニングしていない状態ですが、パーティショニングの有無によっても状況が変わってくるようです。

先ほどと同一のテーブル構成で、パーティショニングした非クラスター化列インデックスの状態が以下になります。

image

大量のデータをインポートした後に、オープン状態になっている場合は、

ALTER INDEX NCCIX_LINEITEM on LINEITEM REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

のような形で「COMPRESS_ALL_ROW_GROUPS」を指定した再構築を実行することで、圧縮の明示的な実行も検討しておいた方がよいかのではないかと。

image

 

列ストアインデックスを使用する場合、行グループの状態がどのようになっているかを意識するのがよいのかなと。

Written by masayuki.ozawa

3月 20th, 2017 at 10:51 pm

Posted in SQL Server

Tagged with

Leave a Reply

*