パッケージのパフォーマンスのトラブルシューティング

更新 : 2007 年 9 月 15 日

Integration Services には、パッケージのパフォーマンスのトラブルシューティングに使用できる機能やツールがあります。たとえば、パッケージに関する実行時の情報をログに記録し、パフォーマンス カウンタを使用してデータ フロー エンジンを監視できます。このトピックでは、このような機能について説明し、パフォーマンスに関する一般的な問題をパッケージのデザイン時に回避するための考え方を示します。

パフォーマンスを向上するデータ フローのデザイン

パッケージ内で次の提案をテストすることで、パッケージ内のデータフローのパフォーマンスのトラブルシューティングを行うことができます。

クエリの最適化

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

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

データ フロー タスクのプロパティの構成

データ フロー タスクの次のプロパティを構成できます。これらのプロパティはすべてパフォーマンスに影響します。

• バッファ データの一時ストレージの場所 (BufferTempStoragePath プロパティ) とバイナリ ラージ オブジェクト (BLOB) データを含む列の一時ストレージの場所 (BLOBTempStoragePath プロパティ) を指定します。既定では、このプロパティの値は TEMP 環境変数の値です。一時ファイルを別のハード ディスク ドライブに配置したり、複数のドライブに分散するために、他のフォルダを指定することがあります。複数のディレクトリを指定する場合は、各ディレクトリ名をセミコロンで区切ります。
• タスクが使用するバッファの既定のサイズを定義するには、DefaultBufferSize プロパティを設定します。各バッファの最大行数を定義するには、DefaultBufferMaxRows プロパティを設定します。既定のバッファ サイズは 10 MB、最大のバッファ サイズは 100 MB です。既定の最大行数は 10,000 行です。
• EngineThreads プロパティを設定することによって、実行中にタスクが使用できるスレッド数を設定します。このプロパティは、使用するスレッド数をデータ フロー エンジンに提案します。既定値は 5 ですが、最小値は 2 です。ただし、エンジンはこのプロパティの値に関係なく、必要以上のスレッドは使用しません。同時実行の問題を回避する必要がある場合、エンジンは、このプロパティに指定された数よりも多くスレッドを使用する場合があります。
• データ フロー タスクを最適化モードで実行するかどうかを示します (RunInOptimizedMode プロパティ)。最適化モードでは、未使用の列、出力、およびコンポーネントをデータ フローから削除することによって、パフォーマンスが向上します。

  メモ :
  同じ名前のプロパティ RunInOptimizedMode を Business Intelligence Development Studio のプロジェクト レベルで設定すると、デバッグ中にデータ フロー タスクを最適化モードで実行するように指定できます。このプロジェクト レベルのプロパティは、デザイン時のデータ フロー タスクの RunInOptimizedMode プロパティをオーバーライドします。

データ フロー タスクのバッファ サイズについて

バッファ サイズを決定するため、データ フロー エンジンはまず、1 つのデータ行の推定サイズを計算します。次に、推定行サイズに DefaultBufferMaxRows の値を乗算し、バッファ サイズの暫定値を求めます。

• 計算結果が DefaultBufferSize の値を超える場合は、行数を減らします。
• 内部で計算された最小バッファ サイズより計算結果が小さい場合は、行数を増やします。
• 計算結果が最小バッファ サイズと DefaultBufferSize の値になる場合は、推定行サイズに DefaultBufferMaxRows の値を乗算した値にできるだけ近いバッファ サイズを設定します。

バッファ サイズの調整

データ フロー タスクのパフォーマンスのテストを開始するときは、DefaultBufferSize と DefaultBufferMaxRows に既定値を使用します。データ フロー タスクのログ記録を有効にし、BufferSizeTuning イベントを選択して、各バッファに含まれる行数を監視します。

パフォーマンスを向上する場合は、バッファ サイズの調整を始める前に、不要な列を削除し、データ型を適切に構成して、データの各行のサイズを減らすことが最も重要です。

使用可能なメモリが十分にある場合は、小さなバッファを数多く使用するよりも、大きなバッファを少数使用することをお勧めします。つまり、データを保持するのに必要なバッファの総数を減らし、できる限り多くのデータ行をバッファに収めることによって、パフォーマンスを向上することができます。最適なバッファの数とサイズを決定するには、BufferSizeTuning イベントで報告されるパフォーマンスや情報を監視しながら、DefaultBufferSize と DefaultBufferMaxRows の値を変更していきます。

メモ :
ここで説明したデータ フロー タスクのプロパティは、パッケージ内のデータ フロー タスクごとに個別に設定する必要があります。

不必要な並べ替えの回避

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

SELECT クエリでの ORDER BY 句の使用、または並べ替え順でのソースへのデータの挿入などを理由に、ソース データが並べ替えられる場合、データが並べ替え済みである旨のヒントを示すことで、並べ替え変換の使用を回避し、特定の下流変換の並べ替え要求を満たすことができます。たとえば、マージ変換およびマージ結合変換では、並べ替え済みの入力が必要です。ヒントを与えるには、上流データ フロー コンポーネントの出力の IsSorted プロパティを True に設定し、並べ替えられるデータに並べ替えキー列を指定します。詳細については、「出力に並べ替え属性を設定する方法」を参照してください。

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

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

緩やかに変化するディメンション変換の最適化

緩やかに変化するディメンション ウィザードおよび緩やかに変化するディメンション変換は、ほとんどのユーザーのニーズに応える汎用的なツールです。ただし、ウィザードによって生成されるデータ フローは、パフォーマンスの点で最適化されているとは言えません。

通常、緩やかに変化するディメンション変換において最も低速なコンポーネントは、一度に 1 つの行に対して UPDATE を実行する OLE DB コマンド変換です。ほとんどの場合、こうした OLE DB コマンド変換を変換先コンポーネントに置き換えることで、緩やかに変化するディメンション変換のパフォーマンスを高めることができます。これらの変換先コンポーネントによって、更新対象のすべての行が段階テーブルに保存されます。その後、一度にすべての行に対して単一のセットベースの Transact-SQL UPDATE を実行する SQL 実行タスクを追加します。

上級ユーザーであれば、大きなディメンション用に最適化された、緩やかに変化するディメンション処理のカスタム データ フローをデザインできます。このアプローチの詳細と例については、Microsoft のホワイト ペーパー『プロジェクト REAL: ビジネス インテリジェンス ETL のデザイン方法 (Project REAL: Business Intelligence ETL Design Practices)』の「一意のディメンションのシナリオ (Unique dimension scenario)」を参照してください。

集計変換の集計の最適化

集計変換には、パフォーマンスを向上させるために使用できるいくつかのプロパティが用意されています。データセット内のキー値の正確な数または概数がわかっている場合は、Keys プロパティおよび KeysScale プロパティを設定できます。さらに、CountDistinctKeys プロパティおよび CountDistinctScale プロパティを設定すると、変換において COUNT DISTINCT 操作での処理が想定されるキーの正確な数または概数を指定できます。これらのプロパティを使用することで、キャッシュに保存された合計が変換で再編成されるのを防ぎ、パフォーマンスを向上させることができます。

データ フロー内に複数の集計を作成する必要がある場合は、複数の変換を作成する代わりに、1 つの集計変換を使用した複数の集計を作成することを検討してください。この方法は、集計が他の集計のサブセットである場合に特にパフォーマンスを向上させます。それは、変換は内部記憶域を最適化でき、入力データのスキャンを一度だけ行えば済むからです。たとえば、集計で GROUP BY 句と AVG 集計を使用する場合は、それらを 1 つの変換に結合することでパフォーマンスを向上させることができます。ただし、1 つの集計変換内で複数の集計を実行すると集計操作がシリアル化されるので、メモリ上の制約がある場合のみ、この方法を使用してください。

詳細については、「集計変換」を参照してください。

マージ結合変換のバッファ スロットルの構成

マージ結合変換には、MaxBuffersPerInput プロパティがあります。このプロパティでは、入力ごとに一度にアクティブにできるバッファの最大数を指定します。このプロパティを使用して、バッファで使用されるメモリの量や変換のパフォーマンスを調整できます。バッファの数が増えると、変換で使用されるメモリの量も増え、パフォーマンスが向上します。MaxBuffersPerInput の既定値は 5 で、この数はほとんどの場合に適したバッファ数です。パフォーマンスを調整するには、4 または 6 など、多少異なるバッファ数を使用します。可能な場合は、バッファ数を極端に少なくすることは避けてください。たとえば、MaxBuffersPerInput を 5 ではなく 1 に設定すると、パフォーマンスに大きな影響を及ぼします。また、MaxBuffersPerInput は 0 に設定しないでください。この値にするとスロットルが行われず、データの負荷や使用可能なメモリの量によっては、パッケージが完了しないことがあります。

マージ結合変換では、デッドロックを回避するために、使用するバッファの数が MaxBuffersPerInput の値を超えて一時的に増やされることがあります。デッドロックの状態が解決された後、MaxBuffersPerInput は構成した値に戻ります。

詳細については、「マージ結合変換」を参照してください。

変換先のパフォーマンスのテスト

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

パッケージのパフォーマンスの監視

Integration Services には、パッケージのパフォーマンスの監視に使用できるツールや機能があります。パッケージで最もパフォーマンスに影響する部分を判断するには、次の考え方を採用します。

[進行状況] タブでの情報のレビュー

Business Intelligence Development Studio でパッケージを実行すると、SSIS デザイナで制御フローとデータ フローの両方に関する情報を得られます。[進行状況] タブにはタスクとコンテナが実行順に表示され、パッケージ自体を含め、タスクやコンテナごとに開始時刻、終了時刻、警告、エラー メッセージが表示されます。一覧にはデータ フロー コンポーネントも実行順に表示され、進捗についての情報、完了の割合、処理された行数も表示されます。

パッケージでのログ記録の構成

Integration Services には各種のログ プロバイダが用意されています。パッケージでは、これらのログ プロバイダを使用して、実行時に情報を各種のファイルに記録したり、SQL Server に記録したりできます。ログ エントリは、パッケージや、タスクおよびコンテナなどの個々のパッケージ オブジェクトに対して有効にできます。Integration Services にはさまざまなタスクおよびコンテナが含まれており、それぞれのタスクおよびコンテナは、独自の説明的なログ エントリのセットを持っています。たとえば、SQL 実行タスクを含むパッケージでは、ステートメントのパラメータ値を含めてタスクによって実行された SQL ステートメントの一覧を示すログ エントリを記述できます。

ログ エントリには、パッケージおよびパッケージ オブジェクトの開始時間や終了時間などの情報が含まれるので、処理に時間がかかっているタスクやコンテナを識別することが可能です。詳細については、「パッケージ実行のログ記録」、「パッケージへのログ機能の実装」、および「ログ記録用のカスタム メッセージ」を参照してください。

データ フロー タスクのログ機能の構成

データ フロー タスクには、パフォーマンスの監視と調整に使用できるカスタム ログ エントリが数多くあります。たとえば、メモリ リークを起こす可能性のあるコンポーネントを監視したり、特定のコンポーネントの実行所要時間を追跡できます。カスタム ログ エントリの一覧とサンプルのログ出力については、「データ フロー タスク」を参照してください。

データ フロー エンジンのパフォーマンスの監視

Integration Services には、データ フロー エンジンのパフォーマンスを監視するためのパフォーマンス カウンタのセットが用意されています。たとえば、すべてのバッファで使用されるメモリの合計サイズ (バイト単位) を追跡し、コンポーネントがメモリ不足かどうかを調べることができます。バッファとは、データを格納するためにコンポーネントが使用するメモリ ブロックです。詳細については、「データ フロー エンジンのパフォーマンスの監視」を参照してください。

参照

処理手順

パッケージ開発のトラブルシューティング

概念

パッケージ実行のトラブルシューティング
Integration Services サービスのトラブルシューティング

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴