Database Engine Tuning Advisor

  • [アーティクル]

適用対象:SQL Server

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

  • 問題のある特定のクエリのパフォーマンスをトラブルシューティングする。

  • 1 つまたは複数のデータベースに対する多数のクエリをチューニングする。

  • 想定される物理的な設計変更に対し、調査目的の what-if 分析を実行する。

  • ストレージ領域を管理する。

注意

データベース エンジン チューニング アドバイザーは、Azure SQL データベース または Azure SQL Managed Instance ではサポートされていません。 代わりに、「Azure SQL Database と Azure SQL Managed Instance での監視とパフォーマンス チューニング」で推奨されている戦略を検討してください。 Azure SQL データベース については、「Azure SQL Database のデータベース アドバイザーのパフォーマンスに関する推奨事項」も参照してください。

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

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

  • クエリ オプティマイザーを使用してワークロード内のクエリを分析し、データベースに最適な行ストアと列ストアインデックスの組み合わせをお勧めします。

  • ワークロード内で参照されているデータベースに関して、固定パーティション分割または非固定パーティション分割を推奨します。

  • ワークロード内で参照されているデータベースに関して、インデックス付きビューを推奨します。

  • インデックスの使用、テーブル間でのクエリの分布、ワークロード内のクエリ パフォーマンスなど、提案された変更を行うとどんな効果が得られるかを分析します。

  • 少数の問題クエリについてデータベースを調整する方法を推奨します。

  • ディスク領域の制約などの詳細オプションを指定して推奨セットをカスタマイズできます。

  • 特定のワークロードに関して、推奨された構成を実装して得られる効果を要約したレポートを提供します。

  • 行う可能性がある設計の変更を仮想の構成という形で指定して、データベース エンジン チューニング アドバイザーで評価し、代替案を考慮できます。

  • SQL Server クエリ ストア、プラン キャッシュ、SQL Server Profiler トレース ファイルやテーブル、または .SQL ファイルなどのさまざまなソースのワークロードをチューニングします。

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

  • オンライン トランザクション処理 (OLTP) クエリのみ

  • オンライン分析処理 (OLAP) クエリのみ

  • OLTP と OLAP の混在クエリ

  • クエリが集中的に行われるワークロード (データ変更数よりもクエリ数が多い)

  • 更新が集中的に行われるワークロード (クエリ数よりもデータ変更数が多い)

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

データベース エンジン チューニング アドバイザーのグラフィカル ユーザー インターフェイス
ワークロードを指定したり各種チューニング オプションを選択したりできる使いやすいインターフェイス。

dta ユーティリティ
データベース エンジン チューニング アドバイザーのコマンド プロンプト版。 dta ユーティリティは、データベース エンジン チューニング アドバイザーの機能をアプリケーションとスクリプトで使用するために作成されました。

ワークロード
チューニングするデータベースの代表的なワークロードが含まれた Transact-SQL スクリプト ファイル、トレース ファイル、またはトレース テーブル。 SQL Server 2012 (11.x)以降では、プラン キャッシュをワークロードとして指定することができます。 SQL Server 2016 (13.x) 以降では、クエリ ストアをワークロードとして指定することができます。

XML 入力ファイル
データベース エンジン チューニング アドバイザーがワークロードのチューニングに使用できる 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 調査分析を実行する。

既存のチューニング セッションを確認し、既存のセッションに基づいてセッションを複製する
チューニングに関する既存の推奨設定を編集して再度評価または実装する。

データベース エンジン チューニング アドバイザーのユーザー インターフェイスのオプションの説明を表示する。		 データベース エンジン チューニング アドバイザーからの出力の表示および操作