メニュー
Amazon Relational Database Service
ユーザーガイド (API Version 2014-10-31)

SQL Server チューニングアドバイザーを使用して Amazon RDS DB インスタンスのデータベースワークロードを分析する

データベースエンジンチューニングアドバイザーは、Microsoft によって提供されるクライアントアプリケーションで、データベースワークロードを分析し、実行するクエリのタイプに基づいて Microsoft SQL Server データベースの最適なインデックスセットを推奨します。SQL Server Management Studio と同様に、チューニングアドバイザーは SQL Server を実行している Amazon RDS DB インスタンスに接続するクライアントコンピュータから実行します。クライアントコンピュータは、独自のネットワーク内で、オンプレミスで実行するローカルコンピュータ、または Amazon RDS DB インスタンスと同じリージョンで実行している Amazon EC2 Windows インスタンスです。

このセクションでは、チューニングアドバイザーで分析するためにワークロードをキャプチャする方法を紹介します。Amazon RDS では SQL Server インスタンスへのホストアクセスが制限されるため、これがワークロードをキャプチャするための最適なプロセスです。チューニングアドバイザーの完全なドキュメントは、MSDN をご覧ください。

チューニングアドバイザーを使用するには、いわゆるワークロードをアドバイザーに提供する必要があります。ワークロードは、調整するデータベースに対して実行する一連の Transact-SQL ステートメントです。データベースエンジンチューニングアドバイザーは、データベースを調整する際のワークロード入力として、トレースファイル、トレーステーブル、Transact-SQL スクリプト、または XML ファイルを使用します。Amazon RDS を使用するときは、クライアントコンピュータ上のファイル、またはクライアントコンピュータにアクセス可能な Amazon RDS SQL Server DB のデータベーステーブルがワークロードになります。ファイルまたはテーブルには、調整するデータベースに対するクエリが再生に適した形式で格納されている必要があります。

チューニングアドバイザーをもっとも効果的に機能させるには、ワークロードをできる限り実際的なものにする必要があります。DB インスタンスに対してトレースを実行することで、ワークロードのファイルまたはテーブルを生成できます。トレースの実行中に、DB インスタンスの負荷をシミュレートするか、正常な負荷でアプリケーションを実行できます。

トレースには、クライアント側とサーバー側の 2 種類があります。クライアント側トレースはセットアップが比較的容易で、SQL Server Profiler でキャプチャされたトレースイベントをリアルタイムで監視することができます。サーバー側トレースは、セットアップが複雑で、複数の Transact-SQL スクリプトを作成する必要があります。さらに、トレースは Amazon RDS DB インスタンスのファイルに書き込まれるため、トレースによってストレージ領域が消費されます。この結果ストレージ領域が不足した場合、DB インスタンスは空き領域がない状態になり、使用不能になる可能性があるため、実行中のサーバー側トレースがどのくらいのストレージ領域を使用するかを追跡することが重要になります。

クライアント側の追跡は、十分な量のトレースデータが SQL Server Profiler にキャプチャされると、ローカルコンピュータ上のファイルか、クライアントコンピュータにアクセス可能な DB インスタンスのデータベーステーブルにトレースを格納して、ワークロードファイルを生成できます。クライアント側トレースを使用する主なデメリットは、大量の負荷がかかると、トレースですべてのクエリをキャプチャできない可能性があることです。この結果、データベースエンジンチューニングアドバイザーによって実行される分析の効果が低下します。大量の負荷の下でトレースを実行する必要があり、そのトレースセッション中にすべてのクエリを確実にキャプチャしたい場合は、サーバー側トレースを使用してください。

サーバー側トレースの場合、DB インスタンスのトレース ファイルを適切なワークロードファイルに入れるか、追跡完了後に DB インスタンスのテーブルにトレースを保存することができます。SQL Server Profiler を使用してトレースをローカルコンピュータのファイルに保存するか、チューニングアドバイザーで DB インスタンスのトレーステーブルから読み取ることができます。

SQL Server DB インスタンスでクライアント側トレースを実行する

SQL Server DB インスタンスでクライアント側トレースを実行するには

  1. Start SQL Server Profiler. It is installed in the Performance Tools folder of your SQL Server instance folder. You must load or define a trace definition template to start a client-side trace.

  2. In the SQL Server Profiler File menu, click New Trace. In the Connect to Server dialog box, enter the DB instance endpoint, port, master user name, and password of the database you would like to run a trace on.

  3. In the Trace Properties dialog box, enter a trace name and choose a trace definition template. A default template, TSQL_Replay, ships with the application. You can edit this template to define your trace. Edit events and event information under the Events Selection tab of the Trace Properties dialog box. For more information about trace definition templates and using the SQL Server Profiler to specify a client-side trace see the documentation in MSDN.

  4. Start the client-side trace and watch SQL queries in real-time as they execute against your DB instance.

  5. Select Stop Trace from the File menu when you have completed the trace. Save the results as a file or as a trace table on you DB instance.

SQL Server DB インスタンスでサーバー側トレースを実行する

サーバー側トレースを作成するスクリプトの作成は複雑になる可能性があるため、このドキュメントでは割愛します。このセクションでは、例として使用できるサンプルスクリプトを紹介します。クライアント側トレースと同様に、データベースエンジンチューニングアドバイザーを使用して開くことのできるワークロードファイルまたはトレーステーブルを作成することが目的です。

次に紹介する簡略化したサンプルスクリプトでは、サーバー側トレースを開始し、詳細をキャプチャしてワークロードファイルを作成します。トレースは、最初に D:\RDSDBDATA\Log ディレクトリの RDSTrace.trc ファイルに保存され、100 MB ごとにロールオーバーされて、それ以降のトレースファイルには RDSTrace_1.trc、RDSTrace_2.trc のように名前が付けられます。

Copy
DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace'; DECLARE @max_file_size BIGINT = 100; DECLARE @on BIT = 1 DECLARE @rc INT DECLARE @traceid INT EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size IF (@rc != 0) BEGIN EXEC sp_trace_setevent @traceid, 10, 1, @on EXEC sp_trace_setevent @traceid, 10, 2, @on EXEC sp_trace_setevent @traceid, 10, 3, @on … EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @traceid, 1 END

以下の例はトレースを停止するスクリプトです。前述のスクリプトで作成されるトレースは、明示的にトレースを停止するか、プロセスがディスク容量を使い果たすまで継続されます。

Copy
DECLARE @traceid INT SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) WHERE property = 5 AND value = 1 AND traceid <> 1 IF @traceid IS NOT NULL BEGIN EXEC sp_trace_setstatus @traceid, 0 EXEC sp_trace_setstatus @traceid, 2 END

サーバー側トレースの結果をデータベーステーブルに保存し、fn_trace_gettable 関数を使用することで、そのデータベーステーブルをチューニングアドバイザーのワークロードとして使用することができます。次のコマンドは、D:\rdsdbdata\Log ディレクトリにある RDSTrace.trc という名前の全ファイル (RDSTrace_1.trc などのすべてのロールオーバーファイルを含む) の結果を、現在のデータベースの RDSTrace という名前のテーブルにロードします。

Copy
SELECT * INTO RDSTrace FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default);

特定のロールオーバーファイルをテーブル (たとえば、RDSTrace_1.trc ファイル) に保存するには、fn_trace_gettable の最後のパラメータとしてロールオーバーファイルの名前を指定し、デフォルトの代わりに 1 を代入します。

Copy
SELECT * INTO RDSTrace_1 FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1);

トレースを使用してチューニングアドバイザーを実行する

ローカルファイルまたはデータベーステーブルとしてトレースを作成したら、DB インスタンスに対してチューニングアドバイザーを実行できます。Microsoft の MSDN には、データベースエンジンチューニングアドバイザーの使用方法に関するドキュメントも含まれています。Amazon RDS でチューニングアドバイザーを使用するときのプロセスは、スタンドアロンのリモート SQL Server インスタンスを使用するときと同じです。クライアントマシンのチューニングアドバイザー UI を使用するか、コマンドラインから dta.exe ユーティリティを使用することができます。いずれの場合も、DB インスタンスのエンドポイントを使用して Amazon RDS DB インスタンスに接続し、チューニングアドバイザーを使用するときに、マスターユーザー名とマスターユーザーパスワードを指定する必要があります。

次のコード例では、エンドポイント dta.cnazcmklsdei.us-east-1.rds.amazonaws.com を持つ Amazon RDS DB インスタンスに対して dta.exe コマンドラインユーティリティを使用しています。この例には、マスターユーザー名 admin とマスターユーザーパスワード test が含まれ、調整するサンプルデータベースの名前は RDSDTA で、入力ワークロードは C:\RDSTrace.trc という名前のローカルマシンにあるトレースファイルです。サンプルコマンドラインコードでは、RDSTrace1 というトレースセッション名も指定し、ローカルマシンへの出力ファイルとして、SQL 出力スクリプトには RDSTrace.sql、結果ファイルには RDSTrace.txt、分析の XML ファイルには RDSTrace.xml という名前を指定します。また、RDSDTA データベースに RDSTraceErrors という名前のエラーテーブルが指定されます。

Copy
dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

次は同じコマンドラインコードの例ですが、入力ワークロードがリモート Amazon RDS インスタンスにある RDSDTA データベースの RDSTrace というテーブルである点が異なります。

Copy
dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

dta ユーティリティコマンドラインパラメータの完全なリストは、MSDN をご覧ください。