データベースエンジンチューニングアドバイザー

[アーティクル]
06/25/2013

Microsoft データベースエンジンチューニングアドバイザー (DTA) は、データベースを分析し、クエリパフォーマンスを最適化する手助けをします。データベースエンジンチューニングアドバイザーを使用すると、データベースの構造や SQL Server の内部構造に関する専門的な知識がなくても、インデックス、インデックス付きビュー、テーブルパーティション分割の最適な組み合わせを選択して作成することができます。 DTA を使用して、次の作業を実行できます。

問題のある特定のクエリのパフォーマンスをトラブルシューティングする。
1 つまたは複数のデータベースに対する多数のクエリをチューニングする。
想定される物理的な設計変更に対し、調査目的の what-if 分析を実行する。
ストレージ領域を管理する。

データベースエンジンチューニングアドバイザーの利点

データベースの構造とデータベースに対して実行するクエリについて十分に理解していなければ、クエリのパフォーマンスを最適化することは困難です。この作業を簡単に行うことができるように、データベースエンジンチューニングアドバイザーは現在のクエリプランキャッシュを分析したり、作成された Transact-SQL クエリのワークロードを分析して適切な物理的設計を提案します。さらに上級のデータベース管理者のために、DTA には、物理設計に対するさまざまな選択肢の what-if 分析を実行して調査するための強力なメカニズムが用意されています。 DTA からは次のような情報を得ることができます。

クエリオプティマイザーを使用してワークロード内のクエリを分析し、データベースに最適なインデックスの組み合わせを推奨します。
ワークロード内で参照されているデータベースに関して、固定パーティション分割または非固定パーティション分割を推奨します。
ワークロード内で参照されているデータベースに関して、インデックス付きビューを推奨します。
インデックスの使用、テーブル間でのクエリの分布、ワークロード内のクエリパフォーマンスなど、提案された変更を行うとどんな効果が得られるかを分析します。
少数の問題クエリについてデータベースを調整する方法を推奨します。
ディスク領域の制約などの詳細オプションを指定して推奨セットをカスタマイズできます。
特定のワークロードに関して、推奨された構成を実装して得られる効果を要約したレポートを提供します。

データベースエンジンチューニングアドバイザーは、次の種類のクエリワークロードを処理します。

オンライントランザクション処理 (OLTP) クエリのみ
オンライン分析処理 (OLAP) クエリのみ
OLTP と OLAP の混在クエリ
クエリが集中的に行われるワークロード (データ変更数よりもクエリ数が多い)
更新が集中的に行われるワークロード (クエリ数よりもデータ変更数が多い)

DTA のコンポーネントと概念

データベースエンジンチューニングアドバイザーのグラフィカルユーザーインターフェイス
ワークロードを指定したり各種チューニングオプションを選択したりできる使いやすいインターフェイス。
dta ユーティリティ
データベースエンジンチューニングアドバイザーのコマンドプロンプト版。 dta ユーティリティは、データベースエンジンチューニングアドバイザーの機能をアプリケーションとスクリプトで使用するために作成されました。
ワークロード (workload)
チューニングするデータベースの代表的なワークロードが含まれた Transact-SQL スクリプトファイル、トレースファイル、またはトレーステーブル。 SQL Server 2012 以降では、プランキャッシュをワークロードとして指定することができます。
XML 入力ファイル (XML input file)
データベースエンジンチューニングアドバイザーがワークロードのチューニングに使用できる XML 形式のファイル。 XML 入力ファイルは、GUI でも dta ユーティリティでも利用できない高度なチューニングオプションをサポートします。

制限事項と制約事項

データベースエンジンチューニングアドバイザーには、次の制限事項と制約事項があります。

一意インデックスか、あるいは PRIMARY KEY 制約または UNIQUE 制約を適用するインデックスの追加または削除はできません。
シングルユーザーモードに設定されているデータベースは分析できません。
実際に使用可能なディスク領域を超える推奨設定をチューニングするために最大ディスク領域を指定した場合、データベースエンジンチューニングアドバイザーでは、指定したその値が使用されます。ただし、推奨設定を実装するためのスクリプトを実行したときに、最初にディスク領域が追加されないと、そのスクリプトは失敗する可能性があります。最大ディスク領域は、dta ユーティリティの -B オプションを使用するか、または [チューニングオプションの詳細設定] ダイアログボックスで値を入力することによって指定できます。
セキュリティ上の理由により、データベースエンジンチューニングアドバイザーでは、リモートサーバーにあるトレーステーブルのワークロードをチューニングできません。この制限は、トレーステーブルではなくトレースファイルを使用するか、トレーステーブルをリモートサーバーにコピーすることによって回避できます。
推奨設定をチューニングするための最大ディスク領域を (-B オプションまたは [チューニングオプションの詳細設定] ダイアログボックスを使用して) 指定したとき課されるような制約を適用すると、データベースエンジンチューニングアドバイザーによって、強制的に特定の既存のインデックスが削除されることがあります。この場合、結果のデータベースエンジンチューニングアドバイザーの推奨設定では、予測向上率が負になっている可能性があります。
チューニング時間を制限する制約を (dta ユーティリティで -A オプションを使用するか、または [チューニングオプション] タブの [チューニング時間を制限する] チェックボックスをオンにして) 指定した場合、データベースエンジンチューニングアドバイザーでは、正確な予測向上率と、これまでに使用されたさまざまなワークロードに関する分析レポートを生成するために、その制限時間を超えて処理が行われる可能性があります。
次の状況では、データベースエンジンチューニングアドバイザーによって、推奨設定が生成されないことがあります。
1. チューニングされているテーブルに含まれているデータページが 10 ページ未満である場合。
2. 推奨されたインデックスを使用しても、現在の物理データベースのデザインではクエリのパフォーマンスが十分に向上しない場合。
3. データベースエンジンチューニングアドバイザーを実行するユーザーが、db_owner データベースロールまたは sysadmin 固定サーバーロールのメンバーではない場合。ワークロード内のクエリは、データベースエンジンチューニングアドバイザーを実行したユーザーのセキュリティコンテキストで分析されます。ユーザーは、db_owner データベースロールのメンバーである必要があります。
データベースエンジンチューニングアドバイザーでは、チューニングセッションのデータや他の情報を msdb データベースに格納します。 msdb データベースが変更されると、チューニングセッションのデータが失われることがあります。このような危険を回避するには、適切なバックアップストラテジを msdb データベースに実装します。

パフォーマンスに関する注意点

データベースエンジンチューニングアドバイザーでは、分析中に大量のプロセッサリソースとメモリリソースが使用される可能性があります。実稼動サーバーの処理速度の低下を回避するには、次のいずれかの操作を実行します。

サーバーが使用されていないときにデータベースをチューニングします。データベースエンジンチューニングアドバイザーは、メンテナンスタスクのパフォーマンスに影響を与える可能性があります。
テストサーバーと実稼動サーバーの機能のいずれかまたは両方を使用します。詳細については、「実稼動サーバーのチューニング負荷の軽減」を参照してください。
データベースエンジンチューニングアドバイザーで分析するデータベースの物理デザイン構造のみを指定します。データベースエンジンチューニングアドバイザーには多くのオプションが用意されていますが、必要なオプションのみを指定するようにしてください。

xp_msver 拡張ストアドプロシージャへの依存性

データベースエンジンチューニングアドバイザーは、xp_msver 拡張ストアドプロシージャに依存してすべての機能を提供します。この拡張ストアドプロシージャは既定でオンになっています。データベースエンジンチューニングアドバイザーはこの拡張ストアドプロシージャを使用して、チューニングするデータベースがあるコンピューター上のプロセッサの数および利用可能なメモリをフェッチします。 xp_msver が使用できない場合は、データベースエンジンチューニングアドバイザーを実行しているコンピューターのハードウェア特性を推定します。データベースエンジンチューニングアドバイザーを実行しているコンピューターのハードウェア特性を推定できない場合は、1 つのプロセッサと 1,024 MB のメモリがあると仮定します。

推奨パーティションの数はこれらの 2 つの値 (プロセッサ数と利用可能なメモリ) によって変わるので、この依存性によって、パーティション分割に関する推奨設定が影響を受けます。また、テストサーバーを使用して本番運用サーバーをチューニングする場合は、依存性によってチューニング結果にも影響があります。この場合、データベースエンジンチューニングアドバイザーは、xp_msver を使用して本番運用サーバーからハードウェア属性をフェッチします。テストサーバー上でワークロードをチューニングした後に、データベースエンジンチューニングアドバイザーはこれらのハードウェア属性を使用して推奨設定を生成します。詳細については、「xp_msver (Transact-SQL)」を参照してください。

データベースエンジンチューニングアドバイザーのタスク

次の表に、データベースエンジンチューニングアドバイザーの一般的なタスクと、その実行方法を説明するトピックを示します。

データベースエンジンチューニングアドバイザーのタスク	トピック
データベースエンジンチューニングアドバイザーを初期化し、起動する。プランキャッシュの指定、スクリプトの作成、またはトレースファイル (またはトレーステーブル) の生成によってワークロードを作成する。データベースエンジンチューニングアドバイザーのグラフィカルユーザーインターフェイスツールを使用してデータベースをチューニングする。ワークロードをチューニングするための XML 入力ファイルを作成する。データベースエンジンチューニングアドバイザーのユーザーインターフェイスのオプションの説明を表示する。	データベースエンジンチューニングアドバイザーの起動および使用
データベースのチューニング操作の結果を表示する。チューニングに関する推奨設定を選択および実装する。ワークロードに対して what-if 調査分析を実行する。既存のチューニングセッションを確認する。既存のセッションに基づいてセッションを複製する。チューニングに関する既存の推奨設定を編集して再度評価または実装する。データベースエンジンチューニングアドバイザーのユーザーインターフェイスのオプションの説明を表示する。	データベースエンジンチューニングアドバイザーからの出力の表示および操作

データベースエンジンチューニングアドバイザーを初期化し、起動する。
プランキャッシュの指定、スクリプトの作成、またはトレースファイル (またはトレーステーブル) の生成によってワークロードを作成する。
データベースエンジンチューニングアドバイザーのグラフィカルユーザーインターフェイスツールを使用してデータベースをチューニングする。
ワークロードをチューニングするための XML 入力ファイルを作成する。
データベースエンジンチューニングアドバイザーのユーザーインターフェイスのオプションの説明を表示する。

データベースエンジンチューニングアドバイザーの起動および使用

データベースのチューニング操作の結果を表示する。
チューニングに関する推奨設定を選択および実装する。
ワークロードに対して what-if 調査分析を実行する。
既存のチューニングセッションを確認する。既存のセッションに基づいてセッションを複製する。チューニングに関する既存の推奨設定を編集して再度評価または実装する。
データベースエンジンチューニングアドバイザーのユーザーインターフェイスのオプションの説明を表示する。

データベースエンジンチューニングアドバイザーからの出力の表示および操作

データベース エンジン チューニング アドバイザー

データベース エンジン チューニング アドバイザーの利点