データ フローのパフォーマンスの向上
このトピックでは、パフォーマンスに関する一般的な問題を Integration Services パッケージのデザイン時に回避するための考え方を示します。また、パッケージのパフォーマンスのトラブルシューティングに使用できる機能やツールについての情報も提供します。
データ フローの構成
パフォーマンスが向上するようにデータ フロー タスクを構成するには、タスクのプロパティの構成、バッファー サイズの調整、および並列実行用のパッケージの構成を行います。
データ フロー タスクのプロパティの構成
.gif "注意") 注 |
|---|
ここで説明するプロパティは、パッケージ内のデータ フロー タスクごとに個別に設定する必要があります。 |
データ フロー タスクの次のプロパティを構成できます。これらのプロパティはすべてパフォーマンスに影響します。
バッファー データの一時ストレージの場所 (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 の既定値は 5 で、この数はほとんどの場合に適したバッファー数です。パフォーマンスを調整するには、4 または 6 など、多少異なるバッファー数を使用します。可能な場合は、バッファー数を極端に少なくすることは避けてください。たとえば、MaxBuffersPerInput を 5 ではなく 1 に設定すると、パフォーマンスに大きな影響を及ぼします。また、MaxBuffersPerInput は 0 以下に設定しないでください。この範囲の値にするとスロットルが行われず、データの負荷や使用可能なメモリの量によっては、パッケージが完了しないことがあります。
マージ結合変換では、デッドロックを回避するために、使用するバッファーの数が MaxBuffersPerInput の値を超えて一時的に増やされることがあります。デッドロックの状態が解決された後、MaxBuffersPerInput は構成した値に戻ります。
詳細については、「マージ結合変換」を参照してください。
緩やかに変化するディメンション変換
緩やかに変化するディメンション ウィザードおよび緩やかに変化するディメンション変換は、ほとんどのユーザーのニーズを満たす汎用ツールです。ただし、ウィザードで生成されるデータ フローは、パフォーマンスのために最適化されていません。
通常、緩やかに変化するディメンション変換の中で最も低速なコンポーネントは、一度に 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 パッケージ サンプルでログに記録されるメッセージのサブセットを示します。
The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.
The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.
The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.
The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.
The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.
The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).
The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).
The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).
これらのログ エントリは、データ フロー タスクが、次の手順にほとんどの時間を要することを示しています。ここでは、手順を降順に示します。
"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: パフォーマンス チューニング技法」
technet.microsoft.com のビデオ「企業での SSIS パッケージ データ フローのチューニング (SQL Server ビデオ)」
technet.microsoft.com のビデオ「SSIS データ フロー バッファーについて (SQL Server ビデオ)」
sqlcat.com の技術資料「同期変換を複数タスクに分割してパイプラインのスループットを向上」
外部リソース
sqlcat.com のプレゼンテーション「Microsoft IT による SQL Server 2008 SSIS データフロー エンジンの機能強化の利用方法」
|
.gif "Integration Services のアイコン (小)")
Integration Services に関する最新情報の入手