SE の雑記

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

SSIS の動作の基本を勉強中

one comment

以下のようなデータフローを実行した場合の SSIS の動作を少しまとめてみたいと思います。

image

処理としては以下のようなフローになっています。

OLE DB ソース SQL Server #1 からデータを取得
派生列 取得したデータの特定の列を派生列で置き換え
参照 フルキャッシュの設定で参照対象のテーブルと比較
OLE DB 変換先 SQL Server #2 にデータを挿入

 

シンプルな構成のフローとなっています。

このフローを実行した場合にどのような処理が行われるのか確認してみました。

  1. データフロータスクが実行されたタイミングで参照設定のクエリが SQL Server で実行される
  2. フルキャッシュで設定しているため、DTS のプロセス上に参照設定で実行したクエリのデータがキャッシュされる
  3. OLEDB ソースで設定したクエリが SQL Server で実行される
  4. SSIS のプロセスが取得したデータを行単位に下流のタスクに流す
  5. フルキャッシュしている SSIS のプロセス内のデータと参照一致の比較を実施
  6. OLE DB 変換先のソースにデータを挿入

このような流れで処理が行われていくようです。

この際の SQL Server のメモリの使用状況を確認したものが以下のグラフになります。

image

赤い線が 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 で確認することができますので、どのようなクエリが実行中なのかはこのあたりで確認してもよいかもしれないですね。

image

取得したデータは一つの BULK INSERT ステートメントで処理がされるようですので、どこまでデータが INSERT されているかを確認するのはなかなか難しそうですね。
# データの並び順がわかるのであれば、DBCC PAGE で何とかなるかもしれませんが

SSIS を使用した ETL はあまり知識がなかったので日夜手探りで勉強中です。
なにか面白い本があるか探してみたいと思います。
# 洋書だと SSIS の本がかなりあるので少し探してみようかと。

Share

Written by Masayuki.Ozawa

11月 5th, 2012 at 12:53 am

Posted in SQL Server

Tagged with

One Response to 'SSIS の動作の基本を勉強中'

Subscribe to comments with RSS or TrackBack to 'SSIS の動作の基本を勉強中'.

  1. SSIS の動作の基本を勉強中 ≪ SE の雑記…
    素敵なエントリーの登録ありがとうございます – .NET Clipsからのトラックバック…

    .NET Clips

    10 11月 12 at 20:25

Leave a Reply