データ フローのパフォーマンスの向上
このトピックでは、パフォーマンスに関する一般的な問題を Integration Services パッケージのデザイン時に回避するための考え方を示します。また、パッケージのパフォーマンスのトラブルシューティングに使用できる機能やツールについての情報も提供します。
データ フローの構成
パフォーマンスが向上するようにデータ フロー タスクを構成するには、タスクのプロパティの構成、バッファ サイズの調整、および並列実行用のパッケージの構成を行います。
データ フロー タスクのプロパティの構成
注意 |
---|
ここで説明するプロパティは、パッケージ内のデータ フロー タスクごとに個別に設定する必要があります。 |
データ フロー タスクの次のプロパティを構成できます。これらのプロパティはすべてパフォーマンスに影響します。
バッファ データの一時ストレージの場所 (BufferTempStoragePath プロパティ) とバイナリ ラージ オブジェクト (BLOB) データを含む列の一時ストレージの場所 (BLOBTempStoragePath プロパティ) を指定します。既定では、これらのプロパティには TEMP および TMP 環境変数の値が含まれます。一時ファイルを別のハード ディスク ドライブまたはより高速なハード ディスク ドライブに配置したり、複数のドライブに分散するために、他のフォルダを指定する必要が生じることがあります。複数のディレクトリを指定する場合は、各ディレクトリ名をセミコロンで区切ります。
タスクが使用するバッファの既定のサイズを定義するには、DefaultBufferSize プロパティを設定します。各バッファの最大行数を定義するには、DefaultBufferMaxRows プロパティを設定します。既定のバッファ サイズは 10 MB、最大のバッファ サイズは 100 MB です。既定の最大行数は 10,000 行です。
EngineThreads プロパティを設定することによって、実行中にタスクが使用できるスレッド数を設定します。このプロパティは、使用するスレッド数をデータ フロー エンジンに提案します。既定値は 5 ですが、最小値は 3 です。ただし、エンジンはこのプロパティの値に関係なく、必要以上のスレッドは使用しません。同時実行の問題を回避する必要がある場合、エンジンは、このプロパティに指定された数よりも多くスレッドを使用する場合があります。
データ フロー タスクを最適化モードで実行するかどうかを示します (RunInOptimizedMode プロパティ)。最適化モードでは、未使用の列、出力、およびコンポーネントをデータ フローから削除することによって、パフォーマンスが向上します。
注意 同じ名前のプロパティ RunInOptimizedMode を Business Intelligence Development Studio のプロジェクト レベルで設定すると、デバッグ中にデータ フロー タスクを最適化モードで実行するように指定できます。このプロジェクト レベルのプロパティは、デザイン時のデータ フロー タスクの RunInOptimizedMode プロパティをオーバーライドします。
バッファ サイズの調整
バッファ サイズを決定するため、データ フロー エンジンはまず、1 つのデータ行の推定サイズを計算します。次に、推定行サイズに DefaultBufferMaxRows の値を乗算し、バッファ サイズの暫定値を求めます。
計算結果が DefaultBufferSize の値を超える場合は、行数を減らします。
内部で計算された最小バッファ サイズより計算結果が小さい場合は、行数を増やします。
計算結果が最小バッファ サイズと DefaultBufferSize の値になる場合は、推定行サイズに DefaultBufferMaxRows の値を乗算した値にできるだけ近いバッファ サイズを設定します。
データ フロー タスクのパフォーマンスのテストを開始するときは、DefaultBufferSize と DefaultBufferMaxRows に既定値を使用します。データ フロー タスクのログ記録を有効にし、BufferSizeTuning イベントを選択して、各バッファに含まれる行数を監視します。
パフォーマンスを向上する場合は、バッファ サイズの調整を始める前に、不要な列を削除し、データ型を適切に構成して、データの各行のサイズを減らすことが最も重要です。
使用可能なメモリが十分にある場合は、小さなバッファを数多く使用するよりも、大きなバッファを少数使用することをお勧めします。つまり、データを保持するのに必要なバッファの総数を減らし、できる限り多くのデータ行をバッファに収めることによって、パフォーマンスを向上することができます。最適なバッファの数とサイズを決定するには、BufferSizeTuning イベントで報告されるパフォーマンスや情報を監視しながら、DefaultBufferSize と DefaultBufferMaxRows の値を変更していきます。
ディスクへのページングが開始される大きさにまでバッファ サイズを増やさないでください。ディスクへのページングが行われると、バッファ サイズが最適化されていない場合よりもパフォーマンスが低下します。ページングが行われているかどうかを判断するには、Microsoft 管理コンソール (MMC) のパフォーマンス スナップインで "Buffers spooled" パフォーマンス カウンタを監視します。
並列実行用のパッケージの構成
並列実行を行うと、複数の物理プロセッサまたは論理プロセッサが搭載されているコンピュータのパフォーマンスが向上します。パッケージ内で各種タスクの並列実行をサポートするには、Integration Services で MaxConcurrentExecutables と EngineThreads の 2 つのプロパティを使用します。
MaxConcurrentExcecutables プロパティ
MaxConcurrentExecutables プロパティは、パッケージ自体のプロパティです。このプロパティによって、同時に実行できるタスクの数が定義されます。既定値は -1 です。これは、物理プロセッサまたは論理プロセッサの数に 2 を加えた数を示します。
このプロパティの動作を理解するために、3 つのデータ フロー タスクを含むサンプル パッケージについて考えてみます。MaxConcurrentExecutables を 3 に設定すると、3 つすべてのデータ フロー タスクを同時に実行できます。ただし、各データ フロー タスクに 10 個の変換元から変換先への実行ツリーが含まれているとすると、MaxConcurrentExecutables を 3 に設定しても、各データ フロー タスク内の実行ツリーが並列実行されるかどうかは保証されません。
EngineThreads プロパティ
EngineThreads プロパティは、各データ フロー タスクのプロパティです。このプロパティによって、データ フロー エンジンが作成および並列実行できるスレッドの数が定義されます。EngineThreads プロパティは、データ フロー エンジンが変換元用に作成するソース スレッドと、変換および変換先用に作成するワーカー スレッドの両方に適用されます。したがって、EngineThreads を 10 に設定すると、エンジンはソース スレッドとワーカー スレッドをそれぞれ 10 個まで作成できます。
このプロパティの動作を理解するために、3 つのデータ フロー タスクを含むサンプル パッケージについて考えてみます。各データ フロー タスクには、10 個の変換元から変換先への実行ツリーが含まれています。各データ フロー タスクで EngineThreads を 10 に設定すると、30 個すべての実行ツリーを同時に実行できます。
注意 |
---|
スレッド処理については、このトピックでは説明しません。ただし、一般的な規則としては、並列実行するスレッドの数が使用可能なプロセッサ数を超えないようにします。使用可能なプロセッサ数より多いスレッドを実行すると、スレッド間でコンテキストの切り替えが頻繁に行われるので、パフォーマンスが低下する可能性があります。 |
個々のデータ フロー コンポーネントの構成
パフォーマンスが向上するように個々のデータ フロー コンポーネントを構成するには、いくつかの一般的なガイドラインに従います。また、データ フロー コンポーネントの種類 (変換元、変換、および変換先) ごとに固有のガイドラインもあります。
一般的なガイドライン
データ フロー コンポーネントの種類に関係なく、パフォーマンスを向上させるには、クエリの最適化と不必要な並べ替えの回避の 2 つの一般的なガイドラインに従う必要があります。
クエリの最適化
データ フロー コンポーネントの多くは、ソースからデータを抽出したり、参照テーブルを作成するための参照操作を行うときにクエリを使用します。既定のクエリでは、SELECT * FROM <tableName> 構文が使用されます。この種類のクエリは、ソース テーブル内のすべての列を返します。デザイン時にすべての列を使用可能にしておくことで、参照列、パススルー列、またはソース列として、任意の列を選択できます。ただし、目的の列を選択した後は、選択した列のみを含むようにクエリを修正します。不必要な列を削除することで、パッケージ内のデータ フローが効率化されます。列数が少なくなると、行が小さくなるためです。行が小さいほど 1 つのバッファに収まる行が増え、データセット内のすべての行を処理する作業が減ります。
クエリを作成するには、手動で入力するか、クエリ ビルダを使用することができます。
注意 |
---|
Business Intelligence Development Studio でパッケージを実行すると、SSIS デザイナの [進行状況] タブに警告が表示されます。これには、データ フローで利用できるが、それに続く下流のデータ フロー コンポーネントでは使用されないデータ列を示す警告も含まれます。RunInOptimizedMode プロパティを使ってこのような列を自動的に削除できます。 |
不必要な並べ替えの回避
並べ替えは本質的に低速な処理であり、不必要な並べ替えを回避することで、パッケージのデータ フローのパフォーマンスを向上させることができます。
ソース データは、下流コンポーネントで使用される前に既に並べ替えられている場合があります。このような状況は、SELECT クエリで ORDER BY 句を使用した場合、またはデータが並べ替え順でソースに挿入された場合に発生することがあります。このようにソース データが事前に並べ替えられている場合、データが並べ替え済みである旨のヒントを示すことで、並べ替え変換の使用を回避し、特定の下流変換の並べ替え要件を満たすことができます (たとえば、マージ変換およびマージ結合変換では、並べ替え済みの入力が必要です)。データが並べ替え済みである旨のヒントを示すには、次の作業を行う必要があります。
上流データ フロー コンポーネントの出力の IsSorted プロパティを True に設定します。
データを並べ替える並べ替えキー列を指定します。
詳細については、「マージ変換およびマージ結合変換用にデータを並べ替える方法」を参照してください。
データ フロー内でデータを並べ替える必要がある場合は、並べ替え処理の回数を可能な限り少なくしたデータ フローをデザインすることで、パフォーマンスを向上させることができます。たとえば、データ フローでマルチキャスト変換を使用してデータセットをコピーするとします。この場合、変換後の複数の出力に対して並べ替えを行うのではなく、マルチキャスト変換の実行前に一度だけデータセットの並べ替えを行います。
変換元
OLE DB ソース
OLE DB ソースを使用してビューからデータを取得する場合は、データ アクセス モードとして [SQL コマンド] を選択し、SELECT ステートメントを入力します。SELECT ステートメントを使用してデータにアクセスすると、データ アクセス モードとして [テーブルまたはビュー] を選択する場合よりもパフォーマンスが向上します。
変換
このセクションの推奨事項に従うと、集計変換、あいまい参照変換、あいまいグループ化変換、参照変換、マージ結合変換、および緩やかに変化するディメンション変換のパフォーマンスが向上します。
集計変換
集計変換には、Keys、KeysScale、CountDistinctKeys、および CountDistinctScale プロパティがあります。これらのプロパティを使用すると、変換時にキャッシュされるデータに必要な量のメモリが事前に割り当てられるようになるため、パフォーマンスが向上します。グループ化操作の結果として予想されるグループの正確な数または概数がわかっている場合は、Keys プロパティと KeysScale プロパティをそれぞれ設定します。個別のカウント操作の結果として予想される個別の値の正確な数または概数がわかっている場合は、CountDistinctKeys プロパティと CountDistinctScale プロパティをそれぞれ設定します。
データ フロー内に複数の集計を作成する必要がある場合は、複数の変換を作成する代わりに、1 つの集計変換を使用した複数の集計を作成することを検討してください。この方法は、集計が別の集計のサブセットである場合にパフォーマンスを向上させます。変換により内部ストレージを最適化でき、入力データのスキャンを一度だけ行えば済むためです。たとえば、集計で GROUP BY 句と AVG 集計を使用する場合は、それらを 1 つの変換に結合することでパフォーマンスを向上させることができます。ただし、1 つの集計変換内で複数の集計を実行すると集計操作がシリアル化されるので、複数の集計を個別に計算する必要がある場合は、パフォーマンスが向上しない可能性があります。
詳細については、「集計変換」を参照してください。
あいまい参照変換とあいまいグループ化変換
あいまい参照変換とあいまいグループ化変換のパフォーマンスの最適化については、ホワイト ペーパー「SQL Server Integration Services 2005 のあいまい参照とあいまいグループ化」を参照してください。
参照変換
必要な列のみを参照する SELECT ステートメントを入力することによって、メモリ内の参照データのサイズを最小限に抑えます。この方法は、テーブルまたはビュー全体を選択して大量の不要なデータを返す場合に比べパフォーマンスに優れています。
マージ結合変換
マージ結合変換には、MaxBuffersPerInput プロパティがあります。このプロパティでは、入力ごとに同時にアクティブにするバッファの最大数を提示します。ただし、MaxBuffersPerInput プロパティに設定した値はあくまでも提案として扱われます。マージ結合変換時には、ほとんどの場合、バッファ数がこの提示値よりも大きい値に増やされます。この増加は、通常、次の理由により必要となります。
結合するのに十分なデータを各入力から取得するため
データを処理しているスレッド間でデッドロックが発生しないようにするため
注意 |
---|
マージ結合変換で使用されるバッファ数は非常に大きくなる場合があるため、コンピュータ上の大量のメモリが Integration Services によって消費されることがあります。 |
変換で各入力から十分なデータが取得され、スレッドのデッドロックが発生するおそれがない場合は、MaxBuffersPerInput プロパティで提示された値の使用が再開されます。
MaxBuffersPerInput プロパティの既定値は 5 で、この数はほとんどの場合に適したバッファ数です。パフォーマンスを向上するためには、バッファ数を増やすこともできます。メモリの負荷を軽減するためには、バッファ数を減らすこともできます。ただし、バッファ数が非常に小さいと逆にパフォーマンスに影響する可能性があり、値が 0 (ゼロ) の場合はすべてのスロットルが無効になるので、ゼロにはしないでください。
緩やかに変化するディメンション変換
緩やかに変化するディメンション ウィザードおよび緩やかに変化するディメンション変換は、ほとんどのユーザーのニーズを満たす汎用ツールです。ただし、ウィザードで生成されるデータ フローは、パフォーマンスのために最適化されていません。
通常、緩やかに変化するディメンション変換の中で最も低速なコンポーネントは、一度に 1 行に対して UPDATE を実行する OLE DB コマンド変換です。したがって、緩やかに変化するディメンション変換のパフォーマンスを向上させる最も効果的な方法は、OLE DB コマンド変換を置き換えることです。この変換は、更新するすべての行をステージング テーブルに保存する変換先コンポーネントに置き換えることができます。その後、同時にすべての行に対して単一セット ベースの Transact-SQL UPDATE を実行する SQL 実行タスクを追加できます。
上級ユーザーは、大きなディメンションのために最適化された、緩やかに変化するディメンション処理用のカスタム データ フローをデザインできます。この方法の説明と例については、ホワイト ペーパー「プロジェクト REAL: ビジネス インテリジェンス ETL のデザイン方法」の「特有のディメンション シナリオ」を参照してください。
変換先
変換先のパフォーマンスを向上させるには、SQL Server 変換先の使用と、変換先のパフォーマンスのテストを検討してください。
SQL Server 変換先
パッケージで同じコンピュータ上の SQL Server のインスタンスにデータを読み込む場合は、SQL Server 変換先を使用します。この変換先は、高速な一括読み込みのために最適化されています。
変換先のパフォーマンスのテスト
変換先でのデータの保存には予想以上の時間がかかります。変換先でデータを迅速に処理できないことが原因で時間がかかっているかどうかを判断するには、変換先を行数変換と置き換えます。スループットが大幅に改善する場合は、データを読み込んでいる変換先がスローダウンを引き起こしている可能性があります。
パッケージのパフォーマンスの監視
Integration Services には、パッケージのパフォーマンスの監視に使用できるツールや機能があります。たとえば、パッケージに関する実行時の情報をログに記録し、パフォーマンス カウンタを使用してデータ フロー エンジンを監視できます。パッケージで最もパフォーマンスに影響する部分を判断するには、次の考え方を採用します。
[進行状況] タブでの情報のレビュー
Business Intelligence Development Studio でパッケージを実行すると、SSIS デザイナで制御フローとデータ フローの両方に関する情報を得られます。[進行状況] タブにはタスクとコンテナが実行順に表示され、パッケージ自体を含め、タスクやコンテナごとに開始時刻、終了時刻、警告、エラー メッセージが表示されます。一覧にはデータ フロー コンポーネントも実行順に表示され、進捗についての情報、完了の割合、処理された行数も表示されます。
[進行状況] タブでのメッセージの表示を有効または無効にするには、[SSIS] メニューの [進行状況レポートのデバッグ] オプションを切り替えます。進行状況レポートを無効にすると、BI Development Studio で複雑なパッケージを実行する際のパフォーマンスを向上できます。
パッケージでのログ記録の構成
Integration Services には各種のログ プロバイダが用意されています。パッケージでは、これらのログ プロバイダを使用して、実行時に情報を各種のファイルに記録したり、SQL Server に記録したりできます。ログ エントリは、パッケージや、タスクおよびコンテナなどの個々のパッケージ オブジェクトに対して有効にできます。Integration Services にはさまざまなタスクおよびコンテナが含まれており、それぞれのタスクおよびコンテナは、独自の説明的なログ エントリのセットを持っています。たとえば、SQL 実行タスクを含むパッケージでは、ステートメントのパラメータ値を含めてタスクによって実行された SQL ステートメントの一覧を示すログ エントリを記述できます。
ログ エントリには、パッケージおよびパッケージ オブジェクトの開始時間や終了時間などの情報が含まれるので、処理に時間がかかっているタスクやコンテナを識別することが可能です。詳細については、「パッケージ実行のログ記録」、「パッケージへのログ機能の実装」、および「ログ記録用のカスタム メッセージ」を参照してください。
データ フロー タスクのログ機能の構成
データ フロー タスクには、パフォーマンスの監視と調整に使用できるカスタム ログ エントリが数多くあります。たとえば、メモリ リークを起こす可能性のあるコンポーネントを監視したり、特定のコンポーネントの実行所要時間を追跡できます。カスタム ログ エントリの一覧とサンプルのログ出力については、「データ フロー タスク」を参照してください。
PipelineComponentTime イベントの使用
カスタム ログ エントリの中で最も役に立つのは、PipelineComponentTime イベントです。このログ エントリは、データ フローの各コンポーネントが主要な 5 つの処理手順それぞれで要するミリ秒数を報告します。次の表で、これらの処理手順について説明します。Integration Services の開発者は、これらの手順を PipelineComponent の主要メソッドと見なすことができます。
手順 |
説明 |
---|---|
Validate |
コンポーネントは、有効なプロパティ値と構成設定を確認します。 |
PreExecute |
コンポーネントは、データ行の処理を開始する前に 1 回限りの処理を実行します。 |
PostExecute |
コンポーネントは、すべてのデータ行を処理した後に 1 回限りの処理を実行します。 |
ProcessInput |
変換コンポーネントまたは変換先コンポーネントは、上流変換元または変換から渡されたデータの受信行を処理します。 |
PrimeOutput |
変換元コンポーネントまたは変換コンポーネントは、下流変換コンポーネントまたは変換先コンポーネントに渡されるデータのバッファを入力します。 |
PipelineComponentTime イベントを有効にすると、Integration Services では、各コンポーネントが実行した処理手順ごとに 1 つのメッセージをログに記録します。以下のログ エントリに、Integration Services CalculatedColumns パッケージ サンプルでログに記録されるメッセージのサブセットを示します。
コンポーネント "Calculate LineItemTotalCost" (3522) は ProcessInput に 356 ミリ秒要しました。
コンポーネント "Sum Quantity and LineItemTotalCost" (3619) は ProcessInput に 79 ミリ秒要しました。
コンポーネント "Calculate Average Cost" (3662) は ProcessInput に 16 ミリ秒要しました。
コンポーネント "Sort by ProductID" (3717) は ProcessInput に 125 ミリ秒要しました。
コンポーネント "Load Data" (3773) は ProcessInput に 0 ミリ秒要しました。
コンポーネント "Extract Data" (3869) は出力 "OLE DB ソースの出力" (3879) で PrimeOutput のバッファ入力に 688 ミリ秒要しました。
コンポーネント "Sum Quantity and LineItemTotalCost" (3619) は出力 "集計の出力 1" (3621) で PrimeOutput のバッファ入力に 141 ミリ秒要しました。
コンポーネント "Sort by ProductID" (3717) は出力 "並べ替えの出力" (3719) で PrimeOutput のバッファ入力に 16 ミリ秒要しました。
これらのログ エントリは、データ フロー タスクが、次の手順にほとんどの時間を要することを示しています。ここでは、手順を降順に示します。
"Extract Data" という名前の OLE DB ソースは、データの読み込みに 688 ミリ秒費やしました。
"Calculate LineItemTotalCost" という名前の派生列変換は、受信行での計算の実行に 356 ミリ秒費やしました。
"Sum Quantity and LineItemTotalCost" という名前の集計変換は、計算の実行と次の変換へのデータの受け渡しに合計 220 ミリ秒 (PrimeOutput に 141 ミリ秒、ProcessInput に 79 秒) 費やしました。
データ フロー エンジンのパフォーマンスの監視
Integration Services には、データ フロー エンジンのパフォーマンスを監視するためのパフォーマンス カウンタのセットが用意されています。たとえば、すべてのバッファで使用されるメモリの合計サイズ (バイト単位) を追跡し、コンポーネントがメモリ不足かどうかを調べることができます。バッファとは、データを格納するためにコンポーネントが使用するメモリ ブロックです。詳細については、「データ フロー エンジンのパフォーマンスの監視」を参照してください。
外部リソース
technet.microsoft.com の技術資料「SQL Server 2005 Integration Services : パフォーマンスに関する戦略」
technet.microsoft.com の技術資料「Integration Services : パフォーマンス チューニング技法」
msdn.microsoft.com のビデオ「企業での SSIS パッケージ データ フローのチューニング」
msdn.microsoft.com のビデオ「SSIS データ フロー バッファについて」
|
変更履歴
変更内容 |
---|
|