チューニング時間とイベントの制限
データベース エンジン チューニング アドバイザによるセッションのチューニングは、チューニングする時間またはチューニングするイベント数を制限することにより制御できます。チューニング分析時間の制限は、メンテナンス ウィンドウで実稼働サーバーをチューニングする必要がある場合に有益です。チューニングするイベント数の制限は、想定される 2 つの構成で、それぞれ同一のワークロードをチューニングすることにより構成を比較する場合に有益です。
データベース エンジン チューニング アドバイザでは、指定された構成に対するワークロードのイベントの一部がチューニングされない場合があります。チューニングするイベント数を指定する場合は、結果を比較しやすいように、各構成のチューニング セッションで対象にするチューニング可能なイベント数を同数にします。
チューニング時間の制御
データベース エンジン チューニング アドバイザは、指定されたデータベースに対して可能である最適な物理パフォーマンス構造が見つかるまでワークロードを分析します。分析にかかる時間は、ワークロードの長さによって異なります。数百ものイベントが含まれているワークロードを完全に分析するには、何日もかかる場合があります。データベースは分析中も使用できますが、パフォーマンスに影響する場合があります。
データベース エンジン チューニング アドバイザによるワークロードの分析時間を制御するには、最長時間を設定します。これには、データベース エンジン チューニング アドバイザでワークロードの分析にかける時間 (時間数および分数) を設定します。最長時間を設定して得られる結果が推奨設定になります。これは、設定した時間内で データベース エンジン チューニング アドバイザが生成できた最適なソリューションに基づくものです。
データベース エンジン チューニング アドバイザのグラフィカル ユーザー インターフェイス (GUI) では、このパラメータは既定で 1 時間に設定されています。dta コマンド ライン ユーティリティでは、既定の最長時間は 8 時間に設定されています。完全な分析の実行に必要な時間が、設定されている最長時間よりも短い場合は、分析の完了時にチューニングが終了します。チューニング時間を無制限に指定すると、このツールの両方のインターフェイスで可能な最高の推奨設定を生成できます。
次のサブセクションでは、このチューニング オプションを GUI とコマンド プロンプトで設定する方法について説明します。
グラフィカル ユーザー インターフェイスによるチューニング時間の設定
最長チューニング時間を設定するには、データベース エンジン チューニング アドバイザの GUI で [チューニング オプション] タブの [チューニング時間を制限する] チェック ボックスをオンにし、[停止時刻] ボックスで日付と時間を設定します。
次の詳細事項が適用されます。
- 一般的に、チューニング時間が長いほど、多くのワークロードを分析できるので、全体的により適切な結果を得ることができます。推奨設定の生成基準となる情報量が多くなります。
- 既定では、最長チューニング時間は 1 時間に設定されます。
- チューニング時間を無制限に指定するには、[チューニング時間を制限する] チェック ボックスをオフにします。
dta コマンド ライン ユーティリティによるチューニング時間の設定
dta コマンド ライン ユーティリティを使用して -A 引数に値を指定することにより、最長チューニング時間を設定できます。例として、次の dta コマンドを調べてみましょう。
dta -E -s MySession -D AdventureWorks -if MyWorkloadScript.sql -fa IDX -A 120
ここでは、-E で信頼関係接続を指定し、-D でチューニングするデータベースを指定しています。また、-if でワークロード ファイルを指定し、-fa IDX で、データベース エンジン チューニング アドバイザによる推奨設定にインデックスのみを追加することを指定しています。-A 120 は、チューニングを 120 分以内で完了する必要があることを表します。
dta ユーティリティをコマンド プロンプトで実行している場合 (自動チューニングのスクリプトで呼び出している場合ではありません)、チューニングを停止するには Ctrl + C キーを押します。すると、データベース エンジン チューニング アドバイザから、その時点で処理が終了しているワークロード数に基づいた、使用可能な推奨設定が返されます。
次の詳細事項が適用されます。
- 一般的に、チューニング時間が長いほど、多くのワークロードを分析できるので、全体的により適切な結果を得ることができます。推奨設定の生成基準となる情報量が多くなります。
- チューニング時間を無制限に指定するには、–A 0 コマンド ライン引数を使用します。ワークロード全体を対象にした最適な推奨設定を得るには、この引数を使用します。
- –A オプションを指定しなければ、既定で –A 480 (8 時間) が設定されます。
- Ctrl + C キーを押した場合も、分析レポートを作成するために、dta ユーティリティが終了するまでに時間がかかることがあります。
XML 入力ファイルによるチューニング時間の設定
最長チューニング時間を XML 入力ファイルで設定し、dta コマンド ライン ユーティリティで使用することができます。最長チューニング時間を分単位で XML 入力ファイルで指定するには、TuningOptions 親要素の下の TuningTimeInMin 副要素を使用します。XML 入力ファイルの詳細については、「XML 入力ファイル リファレンス (DTA)」を参照してください。
チューニングするイベント数の制御
データベース エンジン チューニング アドバイザによってチューニングするワークロード内のイベント数は制御できます。ただし、このオプションを使用できるのは、dta コマンド ライン ユーティリティで –n 引数を使用した場合のみです。-n 引数を使用した場合の dta ユーティリティの動作は、-A 引数に指定されている値によって異なります。次に示すように、この引数では最長チューニング時間が指定されます。
- dta は、指定されたイベント数のチューニングを完了した場合か、指定されたチューニング時間が経過した場合の、いずれか早い方の条件が満たされると停止します。-n 引数を指定しなかった場合は、既定でワークロード内のすべてのイベントが dta によりチューニングされます。-A 引数を指定しなかった場合は、既定で dta の最大チューニング時間が 8 時間として設定されます。
- -n 引数を使用して指定したイベント数を確実にチューニングするには、同時に -A 引数に 0 を指定します。この値は、チューニング時間を制限しないことを指定しています。たとえば、-n 250 -A 0 と指定します。
XML 入力ファイルによるチューニング対象イベント数の指定
チューニングするイベント数を XML 入力ファイルで設定し、dta コマンド ライン ユーティリティで使用することができます。チューニングするイベント数を XML 入力ファイルで指定するには、TuningOptions 親要素の下の NumberOfEvents 副要素を使用します。XML 入力ファイルの詳細については、「XML 入力ファイル リファレンス (DTA)」を参照してください。
チューニングする時間とイベント数を制限するためのベスト プラクティス
チューニングする時間とイベント数の制限についての推奨事項について次に示します。
- 単一のクエリの場合、またはワークロードが小さい場合 (100 イベント未満の場合) は、チューニング時間を無制限に指定します。チューニング時間を無制限に指定すると、データベース エンジン チューニング アドバイザから最適な推奨設定が示されます。また、多くの場合、チューニングは比較的短時間で完了します。
- ワークロードが大きい場合 (100 イベントを超える場合) は、次に示したオプションを上から順に検討します。最後の項目 (4) は、(1) から (3) までの項目をすべて検討した上で使用してください。
- 時間に制約がある場合は、チューニング時間を制限します。
- 一定のイベント数をチューニングするだけで十分な場合 (最初の 10,000 イベントで残りのワークロードも表せると考えられる場合など) は、dta コマンド ライン ユーティリティを使用して、–n 引数でイベント数を指定します。
- dta コマンド ライン ユーティリティを使用しておりチューニング時間を詳細に制限する場合は、–A 引数と –n 引数の両方を使用できます。たとえば、-A 240 と –n 1000 を指定すると、1,000 イベントのチューニングを完了するか 4 時間が経過したとき、つまり、いずれかの条件が満たされた時点で、データベース エンジン チューニング アドバイザのチューニングが停止します。
- チューニングにかかる時間は、クエリの複雑さ (参照されているテーブル数)、選択されている機能セット (インデックス付きビューのチューニングはインデックスのチューニングよりも時間がかかります)、およびデータのサイズ (統計の作成) によって異なります。多くの場合、データベース エンジン チューニング アドバイザのチューニングでは、クエリ オプティマイザの呼び出しに大部分の時間が費やされます。データベース エンジン チューニング アドバイザに指定するチューニング時間として、適切な長さの目安を次に示します。
1 個から 3 個までのテーブルを参照する簡単なクエリでは、インデックスのチューニングだけの場合は 1 クエリにつき約 1 秒で指定し、インデックスとインデックス付きビューのチューニングの場合は 1 クエリにつき約 10 秒で指定します。4 個以上のテーブルを参照する複雑なクエリでは、インデックスのチューニングだけの場合は 1 クエリにつき約 10 秒で指定し、インデックスとインデックス付きビューのチューニングの場合は 1 クエリにつき約 100 秒で指定します。
- データベース エンジン チューニング アドバイザで、ワークロードの 100% を使用したことが示された場合、これはすべてのワークロードを解析したことを意味するもので、必ずしもすべてのワークロードに対してチューニングを行ったことを意味するものではありません。すべてのワークロードがチューニングされたかどうかを判断するには、チューニング ログの終わりに次のメッセージがあるかどうかを確認します。
"ワークロードのすべてのイベントが評価されませんでした。次のいずれかの方法を使用して期間を長くすることを検討してください。(1) DTAShell のチューニング オプション画面でチューニング時間を制限します。(2) DTA コマンド ラインで -A オプションを使用します。(3) DTA への入力 XML で <TuningOptions> の下の <TuningTimeInMin> を使用します。"
チューニング ログにこのようなメッセージが含まれている場合は、データベース エンジン チューニング アドバイザですべてのワークロードをチューニングできなかったことを表しています。この問題を解決するには、さらに長いチューニング時間を指定します。ワークロード内のすべてのイベントをチューニングするには、チューニング時間を無制限に指定することを指定できます。チューニング時間を無制限に指定しなかった場合、データベース エンジン チューニング アドバイザでは、指定されたチューニング時間内においてできる限り多くのイベントに対してチューニングが試行されます。
- 時間に制約がある場合は、チューニング時間を制限します。
- 一定のイベント数をチューニングするだけで十分な場合 (最初の 10,000 イベントで残りのワークロードも表せると考えられる場合など) は、dta コマンド ライン ユーティリティを使用して、–n 引数でイベント数を指定します。
- dta コマンド ライン ユーティリティを使用しておりチューニング時間を詳細に制限する場合は、–A 引数と –n 引数の両方を使用できます。たとえば、-A 240 と –n 1000 を指定すると、1,000 イベントのチューニングを完了するか 4 時間が経過したとき、つまり、いずれかの条件が満たされた時点で、データベース エンジン チューニング アドバイザのチューニングが停止します。
- チューニングにかかる時間は、クエリの複雑さ (参照されているテーブル数)、選択されている機能セット (インデックス付きビューのチューニングはインデックスのチューニングよりも時間がかかります)、およびデータのサイズ (統計の作成) によって異なります。多くの場合、データベース エンジン チューニング アドバイザのチューニングでは、クエリ オプティマイザの呼び出しに大部分の時間が費やされます。データベース エンジン チューニング アドバイザに指定するチューニング時間として、適切な長さの目安を次に示します。
1 個から 3 個までのテーブルを参照する簡単なクエリでは、インデックスのチューニングだけの場合は 1 クエリにつき約 1 秒で指定し、インデックスとインデックス付きビューのチューニングの場合は 1 クエリにつき約 10 秒で指定します。4 個以上のテーブルを参照する複雑なクエリでは、インデックスのチューニングだけの場合は 1 クエリにつき約 10 秒で指定し、インデックスとインデックス付きビューのチューニングの場合は 1 クエリにつき約 100 秒で指定します。
注 Microsoft SQL Server 2000 インデックス チューニング ウィザードの高速モード、中間モード、または完全モードと、データベース エンジン チューニング アドバイザの –A 引数と –n 引数との間に直接の対応はありません。一般に、SQL Server 2000 の特定のモード (高速、中間、または完全) でのチューニングにかかった時間と同程度の時間をかければ、データベース エンジン チューニング アドバイザでは同程度以上の推奨設定を得ることができます。完全モードを使用していた場合は、チューニング時間とワークロード内のチューニング対象イベント数を無制限にして データベース エンジン チューニング アドバイザを使用することをお勧めします。
チューニング時間を制限するその他の方法
データベース エンジン チューニング アドバイザの実行時間を制限する以外に、データベース内のテーブルのサブセットだけをチューニングすることもできます。
次に示すオプションも使用できます。
- 既存の物理デザイン構造 (PDS) をすべて保持する。
- インデックスのみを保持する。
- クラスタ化インデックスのみを保持する。
データベース エンジン チューニング アドバイザでは、インデックスがテーブルに対して最適でない場合でも、最終推奨設定でインデックス ヒントまたはクエリ ヒントが示されます。クエリで参照されている他のテーブルのインデックスが推奨されることがあります。ただし、ヒントとして指定されているすべてのインデックスは、必ず最終推奨設定に含まれます。ヒントを使用した場合、データベース エンジン チューニング アドバイザで最適な実行プランが選択されない場合があります。ワークロードを分析する前に、クエリからインデックス ヒントを削除することを検討してください。
参照
概念
物理デザイン構造の指定
推奨設定のディスク領域の制限
チューニング オプション選択のシナリオ
サポートされていないチューニング オプション