以下のようなデータフローを実行した場合の SSIS の動作を少しまとめてみたいと思います。
処理としては以下のようなフローになっています。
OLE DB ソース | SQL Server #1 からデータを取得 |
派生列 | 取得したデータの特定の列を派生列で置き換え |
参照 | フルキャッシュの設定で参照対象のテーブルと比較 |
OLE DB 変換先 | SQL Server #2 にデータを挿入 |
シンプルな構成のフローとなっています。
このフローを実行した場合にどのような処理が行われるのか確認してみました。
- データフロータスクが実行されたタイミングで参照設定のクエリが SQL Server で実行される
- フルキャッシュで設定しているため、DTS のプロセス上に参照設定で実行したクエリのデータがキャッシュされる
- OLEDB ソースで設定したクエリが SQL Server で実行される
- SSIS のプロセスが取得したデータを行単位に下流のタスクに流す
- フルキャッシュしている SSIS のプロセス内のデータと参照一致の比較を実施
- OLE DB 変換先のソースにデータを挿入
このような流れで処理が行われていくようです。
この際の SQL Server のメモリの使用状況を確認したものが以下のグラフになります。
赤い線が SQL Server #1 (データ変換元)、緑の線が SQL Server #2 (データ変換先) となります。
実行の初期処理として、DBCC DROPCLEANBUFFERS を実行しているのでメモリが減少した状態から開始されています。
データフローが実行されると参照コンポーネントのキャッシュが実行されるため、フローが参照に差し掛かる前に SQL Server にクエリが実行されデータがキャッシュされます。
この際に注意しておきたいのが、SQL Server と SSIS の実行環境が同居している場合です。
参照のクエリの実行は SQL Server に対してクエリが実行されます。
そのタイミングで SQL Server のバッファキャッシュに対してデータのキャッシュが行われます。
参照でフルキャッシュを使用している場合は、SSIS のプロセスに参照データがキャッシュされます。
そのため、同様のデータが 2 つのことなるプロセスでキャッシュされていることになり、処理中のメモリの使用量はそれなりに大きくなる可能性があります。
また、SQL Server のデータ圧縮をしている場合、SQL Server 上のバッファキャッシュではデータは圧縮された状態のため、メモリの使用量を抑制することが可能ですが、データ圧縮によるメモリ内でデータ圧縮は SQL Server 上で有効であるため、参照データをキャッシュしている SSIS のプロセス内ではデータは圧縮が解除された状態となっているため取得するデータ量によってはこのあたりも意識する必要があります。
参照コンポーネントが終了した後は OLE DB ソースに対して SQL を実行し、取得したデータを下流のフローに流していきます。
この際、実行した SQL の全結果を取得し終わってから下流の処理に流すのではなく、取得できた行から逐次下流に流し処理を実行していくようです。
# 使用しているコンポーネントによっては全データが流れてから処理をする必要があることもありますが。
今回の場合、SELECT した結果を他の OLE DB 変換先に INSERT しているのですが、この時のクエリの実行状況としては、
- SQL Server #1 で SELECT が RUNNING
- SQL Server #2 で BULK INSERT が RUNNING
の状態となります。
# What is INSERT BULK? も読んでおくとよさそうですね。
SELECT したデータが随時 INSERT されているので状態としてはわかりやすいですね。
SSIS で実行されているクエリは SQL Server Profiler で SQL:StmtCompleted / SQL:BatchCompleted / RPC:Completed で確認することができますので、どのようなクエリが実行中なのかはこのあたりで確認してもよいかもしれないですね。
取得したデータは一つの BULK INSERT ステートメントで処理がされるようですので、どこまでデータが INSERT されているかを確認するのはなかなか難しそうですね。
# データの並び順がわかるのであれば、DBCC PAGE で何とかなるかもしれませんが
SSIS を使用した ETL はあまり知識がなかったので日夜手探りで勉強中です。
なにか面白い本があるか探してみたいと思います。
# 洋書だと SSIS の本がかなりあるので少し探してみようかと。
SSIS の動作の基本を勉強中 ≪ SE の雑記…
素敵なエントリーの登録ありがとうございます – .NET Clipsからのトラックバック…
.NET Clips
10 11月 12 at 20:25