データ フローのパフォーマンスの向上

MaxConcurrentExecutables プロパティは、パッケージ自体のプロパティです。このプロパティによって、同時に実行できるタスクの数が定義されます。既定値は -1 です。これは、物理プロセッサまたは論理プロセッサの数に 2 を加えた数を示します。

このプロパティの動作を理解するために、3 つのデータ フロー タスクを含むサンプル パッケージについて考えてみます。MaxConcurrentExecutables を 3 に設定すると、3 つすべてのデータ フロー タスクを同時に実行できます。ただし、各データ フロー タスクに 10 個の変換元から変換先への実行ツリーが含まれているとすると、MaxConcurrentExecutables を 3 に設定しても、各データ フロー タスク内の実行ツリーが並列実行されるかどうかは保証されません。

EngineThreads プロパティは、各データ フロー タスクのプロパティです。このプロパティによって、データ フロー エンジンが作成および並列実行できるスレッドの数が定義されます。EngineThreads プロパティは、データ フロー エンジンが変換元用に作成するソース スレッドと、変換および変換先用に作成するワーカー スレッドの両方に適用されます。したがって、EngineThreads を 10 に設定すると、エンジンはソース スレッドとワーカー スレッドをそれぞれ 10 個まで作成できます。

このプロパティの動作を理解するために、3 つのデータ フロー タスクを含むサンプル パッケージについて考えてみます。各データ フロー タスクには、10 個の変換元から変換先への実行ツリーが含まれています。各データ フロー タスクで EngineThreads を 10 に設定すると、30 個すべての実行ツリーを同時に実行できます。

注意
スレッド処理については、このトピックでは説明しません。ただし、一般的な規則としては、並列実行するスレッドの数が使用可能なプロセッサ数を超えないようにします。使用可能なプロセッサ数より多いスレッドを実行すると、スレッド間でコンテキストの切り替えが頻繁に行われるので、パフォーマンスが低下する可能性があります。

データ フロー コンポーネントの多くは、ソースからデータを抽出したり、参照テーブルを作成するための参照操作を行うときにクエリを使用します。既定のクエリでは、SELECT * FROM <tableName> 構文が使用されます。この種類のクエリは、ソース テーブル内のすべての列を返します。デザイン時にすべての列を使用可能にしておくことで、参照列、パススルー列、またはソース列として、任意の列を選択できます。ただし、目的の列を選択した後は、選択した列のみを含むようにクエリを修正します。不必要な列を削除することで、パッケージ内のデータ フローが効率化されます。列数が少なくなると、行が小さくなるためです。行が小さいほど 1 つのバッファに収まる行が増え、データセット内のすべての行を処理する作業が減ります。

クエリを作成するには、手動で入力するか、クエリ ビルダを使用することができます。

注意
Business Intelligence Development Studio でパッケージを実行すると、SSIS デザイナの [進行状況] タブに警告が表示されます。これには、データ フローで利用できるが、それに続く下流のデータ フロー コンポーネントでは使用されないデータ列を示す警告も含まれます。RunInOptimizedMode プロパティを使ってこのような列を自動的に削除できます。

並べ替えは本質的に低速な処理であり、不必要な並べ替えを回避することで、パッケージのデータ フローのパフォーマンスを向上させることができます。

ソース データは、下流コンポーネントで使用される前に既に並べ替えられている場合があります。このような状況は、SELECT クエリで ORDER BY 句を使用した場合、またはデータが並べ替え順でソースに挿入された場合に発生することがあります。このようにソース データが事前に並べ替えられている場合、データが並べ替え済みである旨のヒントを示すことで、並べ替え変換の使用を回避し、特定の下流変換の並べ替え要件を満たすことができます (たとえば、マージ変換およびマージ結合変換では、並べ替え済みの入力が必要です)。データが並べ替え済みである旨のヒントを示すには、次の作業を行う必要があります。

• 上流データ フロー コンポーネントの出力の IsSorted プロパティを True に設定します。

• データを並べ替える並べ替えキー列を指定します。

詳細については、「マージ変換およびマージ結合変換用にデータを並べ替える方法」を参照してください。

データ フロー内でデータを並べ替える必要がある場合は、並べ替え処理の回数を可能な限り少なくしたデータ フローをデザインすることで、パフォーマンスを向上させることができます。たとえば、データ フローでマルチキャスト変換を使用してデータセットをコピーするとします。この場合、変換後の複数の出力に対して並べ替えを行うのではなく、マルチキャスト変換の実行前に一度だけデータセットの並べ替えを行います。

詳細については、「並べ替え変換」、「マージ変換」、「マージ結合変換」、および「マルチキャスト変換」を参照してください。

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 変換先を使用します。この変換先は、高速な一括読み込みのために最適化されています。

変換先でのデータの保存には予想以上の時間がかかります。変換先でデータを迅速に処理できないことが原因で時間がかかっているかどうかを判断するには、変換先を行数変換と置き換えます。スループットが大幅に改善する場合は、データを読み込んでいる変換先がスローダウンを引き起こしている可能性があります。

カスタム ログ エントリの中で最も役に立つのは、PipelineComponentTime イベントです。このログ エントリは、データ フローの各コンポーネントが主要な 5 つの処理手順それぞれで要するミリ秒数を報告します。次の表で、これらの処理手順について説明します。Integration Services の開発者は、これらの手順を PipelineComponent の主要メソッドと見なすことができます。

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 秒) 費やしました。