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

2017-03-06

Microsoft データベースエンジンチューニングアドバイザー (DTA) は、データベースを分析し、クエリのパフォーマンスを最適化するために使用できる推奨事項を作成します。データベースエンジンチューニングアドバイザーを使用すると、データベース構造や SQL Server の内部について専門家が理解しなくても、インデックス、インデックス付きビュー、またはテーブルパーティションの最適なセットを選択して作成できます。 DTA を使用すると、次のタスクを実行できます。

特定の問題のあるクエリのパフォーマンスのトラブルシューティング
1 つ以上のデータベース間で大量のクエリセットを調整する
潜在的な物理的設計変更の探索的な what-if 分析を実行する
記憶域スペースを管理する

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

データベース構造とデータベースに対して実行されるクエリを完全に理解しないと、クエリのパフォーマンスの最適化が困難になる場合があります。データベースエンジンチューニングアドバイザーは、現在のクエリプランキャッシュを分析するか、作成した Transact-SQL クエリのワークロードを分析し、適切な物理設計を推奨することで、このタスクを容易にすることができます。より高度なデータベース管理者向けに、DTA は、さまざまな物理設計の代替手段の探索的な what-if 分析を実行するための強力なメカニズムを公開します。 DTA は次の情報を提供できます。

クエリオプティマイザーを使用してワークロード内のクエリを分析することで、データベースに最適なインデックスの組み合わせを推奨します。
ワークロードで参照されるデータベースに対して、アラインされたパーティションまたはアラインされていないパーティションの使用を提案します。
ワークロードで参照されるデータベースにインデックス付きビューを推奨します。
インデックスの使用、テーブル間のクエリ分散、ワークロードのクエリパフォーマンスなど、提案された変更の影響を分析します。
少数の問題のあるクエリに対してデータベースを調整する方法をお勧めします。
ディスク領域の制約などの高度なオプションを指定して、推奨事項をカスタマイズできます。
特定のワークロードの推奨事項を実装した場合の影響を要約したレポートを提供します。

データベースエンジンチューニングアドバイザーは、次の種類のクエリワークロードを処理するように設計されています。

オンライントランザクション処理 (OLTP) クエリのみ
オンライン分析処理 (OLAP) クエリのみ
OLTP クエリと OLAP クエリの混在
クエリ負荷の高いワークロード (データ変更よりも多くのクエリ)
更新負荷の高いワークロード (クエリよりも多くのデータ変更)

DTA コンポーネントと概念

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

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

作業負荷
チューニングするデータベースの代表的なワークロードを含む Transact-SQL スクリプトファイル、トレースファイル、またはトレーステーブル。 SQL Server 2012 以降では、プランキャッシュをワークロードとして指定できます。

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 つのプロセッサと 1024 MB (MB) のメモリが想定されます。

推奨されるパーティションの数はこれら 2 つの値 (プロセッサの数と使用可能なメモリ) に依存するため、この依存関係はパーティション分割の推奨事項に影響します。この依存関係は、テストサーバーを使用して運用サーバーをチューニングする場合のチューニング結果にも影響します。このシナリオでは、データベースエンジンチューニングアドバイザーは 、xp_msver を使用して実稼働サーバーからハードウェアプロパティをフェッチします。テストサーバーでワークロードをチューニングした後、データベースエンジンチューニングアドバイザーはこれらのハードウェアプロパティを使用して推奨事項を生成します。詳細については、「xp_msver (Transact-SQL)」を参照してください。