Database Engine Tuning AdvisorDatabase Engine Tuning Advisor

適用対象: ○SQL Server XAzure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

MicrosoftMicrosoft データベース エンジン チューニング アドバイザー (DTA) は、データベースを分析し、クエリ パフォーマンスを最適化する手助けをします。The MicrosoftMicrosoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. データベース エンジン チューニング アドバイザーを使用すると、データベースの構造や SQL ServerSQL Serverの内部構造に関する専門的な知識がなくても、インデックス、インデックス付きビュー、テーブル パーティション分割の最適な組み合わせを選択して作成することができます。You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL ServerSQL Server. DTA を使用して、次の作業を実行できます。Using the DTA, you can perform the following tasks.

  • 問題のある特定のクエリのパフォーマンスをトラブルシューティングする。Troubleshoot the performance of a specific problem query

  • 1 つまたは複数のデータベースに対する多数のクエリをチューニングする。Tune a large set of queries across one or more databases

  • 想定される物理的な設計変更に対し、調査目的の what-if 分析を実行する。Perform an exploratory what-if analysis of potential physical design changes

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

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

データベースの構造とデータベースに対して実行するクエリについて十分に理解していなければ、クエリのパフォーマンスを最適化することは困難です。Optimizing query performance can be difficult without a full understanding the database structure and the queries that are run against the database. この作業を簡単に行うことができるように、データベース エンジン チューニング アドバイザー (DTA) は現在のクエリ プラン キャッシュを分析したり、作成された Transact-SQLTransact-SQL クエリのワークロードを分析して適切な物理的設計を提案します。The Database Engine Tuning Advisor (DTA) can make this task easier by analyzing the current query plan cache or by analyzing a workload of Transact-SQLTransact-SQL queries that you create and recommending an appropriate physical design. さらに上級のデータベース管理者のために、DTA には、物理設計に対するさまざまな選択肢の what-if 分析を実行して調査するための強力なメカニズムが用意されています。For more advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of different physical design alternatives. DTA からは次のような情報を得ることができます。The DTA can provide the following information.

  • クエリ オプティマイザーを使用してワークロード内のクエリを分析し、データベースに最適な行ストアと列ストアインデックスの組み合わせをお勧めします。Recommend the best mix of rowstore and columnstore indexes for databases by using the query optimizer to analyze queries in a workload.

  • ワークロード内で参照されているデータベースに関して、固定パーティション分割または非固定パーティション分割を推奨します。Recommend aligned or non-aligned partitions for databases referenced in a workload.

  • ワークロード内で参照されているデータベースに関して、インデックス付きビューを推奨します。Recommend indexed views for databases referenced in a workload.

  • インデックスの使用、テーブル間でのクエリの分布、ワークロード内のクエリ パフォーマンスなど、提案された変更を行うとどんな効果が得られるかを分析します。Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.

  • 少数の問題クエリについてデータベースを調整する方法を推奨します。Recommend ways to tune the database for a small set of problem queries.

  • ディスク領域の制約などの詳細オプションを指定して推奨セットをカスタマイズできます。Allow you to customize the recommendation by specifying advanced options such as disk space constraints.

  • 特定のワークロードに関して、推奨された構成を実装して得られる効果を要約したレポートを提供します。Provide reports that summarize the effects of implementing the recommendations for a given workload.

  • 行う可能性がある設計の変更を仮想の構成という形で指定して、データベース エンジン チューニング アドバイザーで評価し、代替案を考慮できます。Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

  • SQL Server クエリ ストア、プラン キャッシュ、SQL Server Profiler トレース ファイルやテーブル、または .SQL ファイルなどのさまざまなソースのワークロードをチューニングします。Tune workloads from a variety of sources including SQL Server Query Store, Plan Cache, SQL Server Profiler Trace file or table, or a .SQL file.

データベース エンジン チューニング アドバイザーは、次の種類のクエリ ワークロードを処理します。The Database Engine Tuning Advisor is designed to handle the following types of query workloads:

  • オンライン トランザクション処理 (OLTP) クエリのみOnline transaction processing (OLTP) queries only

  • オンライン分析処理 (OLAP) クエリのみOnline analytical processing (OLAP) queries only

  • OLTP と OLAP の混在クエリMixed OLTP and OLAP queries

  • クエリが集中的に行われるワークロード (データ変更数よりもクエリ数が多い)Query-heavy workloads (more queries than data modifications)

  • 更新が集中的に行われるワークロード (クエリ数よりもデータ変更数が多い)Update-heavy workloads (more data modifications than queries)

DTA のコンポーネントと概念DTA Components and Concepts

データベース エンジン チューニング アドバイザーのグラフィカル ユーザー インターフェイスDatabase Engine Tuning Advisor Graphical User Interface
ワークロードを指定したり各種チューニング オプションを選択したりできる使いやすいインターフェイス。An easy-to-use interface in which you can specify the workload and select various tuning options.

dta ユーティリティdta Utility
データベース エンジン チューニング アドバイザーのコマンド プロンプト版。The command prompt version of Database Engine Tuning Advisor. dta ユーティリティは、データベース エンジン チューニング アドバイザーの機能をアプリケーションとスクリプトで使用するために作成されました。The dta utility is designed to allow you to use Database Engine Tuning Advisor functionality in applications and scripts.

ワークロードworkload
チューニングするデータベースの代表的なワークロードが含まれた Transact-SQL スクリプト ファイル、トレース ファイル、またはトレース テーブル。A Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune. SQL Server 2012 (11.x)SQL Server 2012 (11.x)以降では、プラン キャッシュをワークロードとして指定することができます。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), you can specify the plan cache as the workload. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降では、クエリ ストアをワークロードとして指定することができます。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can specify the Query Store as the workload.

XML 入力ファイルXML input file
データベース エンジン チューニング アドバイザーがワークロードのチューニングに使用できる XML 形式のファイル。A XML-formatted file that Database Engine Tuning Advisor can use to tune workloads. XML 入力ファイルは、GUI でも dta ユーティリティでも利用できない高度なチューニング オプションをサポートします。The XML input file supports advanced tuning options that are not available in either the GUI or dta utility.

制限事項と制約事項Limitations and Restrictions

データベース エンジン チューニング アドバイザーには、次の制限事項と制約事項があります。The Database Engine Tuning Advisor has the following limitations and restrictions.

  • 一意インデックスか、あるいは PRIMARY KEY 制約または UNIQUE 制約を適用するインデックスの追加または削除はできません。It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

  • シングル ユーザー モードに設定されているデータベースは分析できません。It cannot analyze a database that is set to single-user mode.

  • 実際に使用可能なディスク領域を超える推奨設定をチューニングするために最大ディスク領域を指定した場合、データベース エンジン チューニング アドバイザーでは、指定したその値が使用されます。If you specify a maximum disk space for tuning recommendations that exceeds the actual available space, Database Engine Tuning Advisor uses the value you specify. ただし、推奨設定を実装するためのスクリプトを実行したときに、最初にディスク領域が追加されないと、そのスクリプトは失敗する可能性があります。However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first. 最大ディスク領域は、 dta ユーティリティの -B オプションを使用するか、または [チューニング オプションの詳細設定] ダイアログ ボックスで値を入力することによって指定できます。Maximum disk space can be specified with the -B option of the dta utility, or by entering a value in the Advanced Tuning Options dialog box.

  • セキュリティ上の理由により、データベース エンジン チューニング アドバイザーでは、リモート サーバーにあるトレース テーブルのワークロードをチューニングできません。For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. この制限は、トレース テーブルではなくトレース ファイルを使用するか、トレース テーブルをリモート サーバーにコピーすることによって回避できます。To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.

  • 推奨設定をチューニングするための最大ディスク領域を ( -B オプションまたは [チューニング オプションの詳細設定] ダイアログ ボックスを使用して) 指定したとき課されるような制約を適用すると、データベース エンジン チューニング アドバイザーによって、強制的に特定の既存のインデックスが削除されることがあります。When you impose constraints, such as those imposed when you specify a maximum disk space for tuning recommendations (by using the -B option or the Advanced Tuning Options dialog box), Database Engine Tuning Advisor may be forced to drop certain existing indexes. この場合、結果のデータベース エンジン チューニング アドバイザーの推奨設定では、予測向上率が負になっている可能性があります。In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.

  • チューニング時間を制限する制約を ( dta ユーティリティで -A オプションを使用するか、または [チューニング オプション] タブの [チューニング時間を制限する] チェック ボックスをオンにして) 指定した場合、データベース エンジン チューニング アドバイザーでは、正確な予測向上率と、これまでに使用されたさまざまなワークロードに関する分析レポートを生成するために、その制限時間を超えて処理が行われる可能性があります。When you specify a constraint to limit tuning time (by using the -A option with the dta utility or by checking Limit tuning time on the Tuning Options tab), Database Engine Tuning Advisor may exceed that time limit to produce an accurate expected improvement and the analysis reports for whatever portion of the workload has been consumed so far.

  • 次の状況では、データベース エンジン チューニング アドバイザーによって、推奨設定が生成されないことがあります。Database Engine Tuning Advisor might not make recommendations under the following circumstances:

    1. チューニングされているテーブルに含まれているデータ ページが 10 ページ未満である場合。The table being tuned contains less than 10 data pages.

    2. 推奨されたインデックスを使用しても、現在の物理データベースのデザインではクエリのパフォーマンスが十分に向上しない場合。The recommended indexes would not offer enough improvement in query performance over the current physical database design.

    3. データベース エンジン チューニング アドバイザーを実行するユーザーが、 db_owner データベース ロールまたは sysadmin 固定サーバー ロールのメンバーではない場合。The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. ワークロード内のクエリは、データベース エンジン チューニング アドバイザーを実行したユーザーのセキュリティ コンテキストで分析されます。The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. ユーザーは、 db_owner データベース ロールのメンバーである必要があります。The user must be a member of the db_owner database role.

  • データベース エンジン チューニング アドバイザーでは、チューニング セッションのデータや他の情報を msdb データベースに格納します。Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. msdb データベースが変更されると、チューニング セッションのデータが失われることがあります。If changes are made to the msdb database you may risk losing tuning session data. このような危険を回避するには、適切なバックアップ ストラテジを msdb データベースに実装します。To eliminate this risk, implement an appropriate backup strategy for the msdb database.

パフォーマンスに関する考慮事項Performance Considerations

データベース エンジン チューニング アドバイザーでは、分析中に大量のプロセッサ リソースとメモリ リソースが使用される可能性があります。Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. 実稼動サーバーの処理速度の低下を回避するには、次のいずれかの操作を実行します。To avoid slowing down your production server, follow one of these strategies:

  • サーバーが使用されていないときにデータベースをチューニングします。Tune your databases when your server is free. データベース エンジン チューニング アドバイザーは、メンテナンス タスクのパフォーマンスに影響を与える可能性があります。Database Engine Tuning Advisor can affect maintenance task performance.

  • テスト サーバーと実稼動サーバーの機能のいずれかまたは両方を使用します。Use the test server/production server feature. 詳細については、「 実稼働サーバーのチューニング負荷の軽減」を参照してください。For more information, see Reduce the Production Server Tuning Load.

  • データベース エンジン チューニング アドバイザーで分析するデータベースの物理デザイン構造のみを指定します。Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. データベース エンジン チューニング アドバイザーには多くのオプションが用意されていますが、必要なオプションのみを指定するようにしてください。Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

xp_msver 拡張ストアド プロシージャへの依存性Dependency on xp_msver Extended Stored Procedure

データベース エンジン チューニング アドバイザーは、 xp_msver 拡張ストアド プロシージャに依存してすべての機能を提供します。Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. この拡張ストアド プロシージャは既定でオンになっています。This extended stored procedure is turned on by default. データベース エンジン チューニング アドバイザーはこの拡張ストアド プロシージャを使用して、チューニングするデータベースがあるコンピューター上のプロセッサの数および利用可能なメモリをフェッチします。Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and available memory on the computer where the database that you are tuning resides. xp_msver が使用できない場合は、データベース エンジン チューニング アドバイザーを実行しているコンピューターのハードウェア特性を推定します。If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. データベース エンジン チューニング アドバイザーを実行しているコンピューターのハードウェア特性を推定できない場合は、1 つのプロセッサと 1,024 MB のメモリがあると仮定します。If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed.

推奨パーティションの数はこれらの 2 つの値 (プロセッサ数と利用可能なメモリ) によって変わるので、この依存性によって、パーティション分割に関する推奨設定が影響を受けます。This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory). また、テスト サーバーを使用して本番運用サーバーをチューニングする場合は、依存性によってチューニング結果にも影響があります。The dependency also affects your tuning results when you use a test server to tune your production server. この場合、データベース エンジン チューニング アドバイザーは、 xp_msver を使用して運用サーバーからハードウェア属性をフェッチします。In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. テスト サーバー上でワークロードをチューニングした後に、データベース エンジン チューニング アドバイザーはこれらのハードウェア属性を使用して推奨設定を生成します。After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation. 詳細については、「xp_msver (Transact-SQL)」を参照してください。For more information, see xp_msver (Transact-SQL).

データベース エンジン チューニング アドバイザーのタスクDatabase Engine Tuning Advisor Tasks

次の表に、データベース エンジン チューニング アドバイザーの一般的なタスクと、その実行方法を説明するトピックを示します。The following table lists common Database Engine Tuning Advisor tasks and the topics that describe how to perform them.

データベース エンジン チューニング アドバイザーのタスクDatabase Engine Tuning Advisor Task トピックTopic
データベース エンジン チューニング アドバイザーを初期化し、起動する。Initialize and start the Database Engine Tuning Advisor.

プラン キャッシュの指定、スクリプトの作成、またはトレース ファイル (またはトレース テーブル) の生成によってワークロードを作成する。Create a workload by specifying the plan cache, by creating a script, or by generating a trace file or trace table.

データベース エンジン チューニング アドバイザーのグラフィカル ユーザー インターフェイス ツールを使用してデータベースをチューニングする。Tune a database by using the Database Engine Tuning Advisor graphical user interface tool.

ワークロードをチューニングするための XML 入力ファイルを作成する。Create XML input files to tune workloads.

データベース エンジン チューニング アドバイザーのユーザー インターフェイスのオプションの説明を表示する。View descriptions of the Database Engine Tuning Advisor user interface options.
データベース エンジン チューニング アドバイザーの起動および使用Start and Use the Database Engine Tuning Advisor
データベースのチューニング操作の結果を表示する。View the results of the database tuning operation.

チューニングに関する推奨設定を選択および実装する。Select and implement tuning recommendations.

ワークロードに対して what-if 調査分析を実行する。Perform what-if exploratory analysis against the workload.

既存のチューニング セッションを確認する。既存のセッションに基づいてセッションを複製する。Review existing tuning sessions, clone sessions based on existing ones
チューニングに関する既存の推奨設定を編集して再度評価または実装する。or edit existing tuning recommendations for further evaluation or implementation.

データベース エンジン チューニング アドバイザーのユーザー インターフェイスのオプションの説明を表示する。View descriptions of the Database Engine Tuning Advisor user interface options.
データベース エンジン チューニング アドバイザーからの出力の表示および操作View and Work with the Output from the Database Engine Tuning Advisor