Database Engine Tuning Advisor を使用して Amazon RDS for SQL Server DB インスタンスのデータベースワークロードを分析する
Database Engine Tuning Advisor は、Microsoft によって提供されるクライアントアプリケーションで、データベースワークロードを分析し、実行するクエリのタイプに基づいて Microsoft SQL Server データベースの最適なインデックスセットを推奨します。SQL Server Management Studio と同様に、チューニングアドバイザーは SQL Server を実行している Amazon RDS DB インスタンスに接続するクライアントコンピュータから実行します。クライアントコンピュータは、独自のネットワーク内で、オンプレミスで実行するローカルコンピュータ、または Amazon RDS DB インスタンスと同じリージョンで実行している Amazon EC2 Windows インスタンスです。
このセクションでは、チューニングアドバイザーで分析するためにワークロードをキャプチャする方法を紹介します。Amazon RDS では SQL Server インスタンスへのホストアクセスが制限されるため、これがワークロードをキャプチャするための最適なプロセスです。詳細については、Microsoft のドキュメントの Database Engine Tuning Advisor
チューニングアドバイザーを使用するには、いわゆるワークロードをアドバイザーに提供する必要があります。ワークロードは、調整するデータベースに対して実行する一連の Transact-SQL ステートメントです。データベースエンジンチューニングアドバイザーは、データベースを調整する際のワークロード入力として、トレースファイル、トレーステーブル、Transact-SQL スクリプト、または XML ファイルを使用します。Amazon RDS を使用するときは、クライアントコンピュータ上のファイル、またはクライアントコンピュータにアクセス可能な Amazon RDS for 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 インスタンスでクライアント側トレースを実行するには
-
SQL Server Profiler を起動します。これは SQL Server インスタンスのフォルダの Performance Tools フォルダにインストールされます。クライアント側トレースを開始するには、トレース定義テンプレートをロードするか定義する必要があります。
-
SQL Server Profiler の [ファイル] メニューで、[新しいトレース] を選択します。[Connect to Server] ダイアログボックスで、トレースを実行するデータベースの DB インスタンスエンドポイント、ポート、マスターユーザー名、およびパスワードを入力します。
-
[Trace Properties] ダイアログボックスで、トレース名を入力し、トレース定義テンプレートを選択します。デフォルトテンプレート TSQL_Replay は、アプリケーションに標準で装備されています。このテンプレートを編集して、トレースを定義できます。[トレースのプロパティ] ダイアログボックスの [イベントの選択] タブで、イベントとイベント情報を編集します。
トレース定義テンプレートの詳細と SQL Server Profiler を使用してクライアント側トレースを指定する方法については、Microsoft ドキュメントの Database Engine Tuning Advisor
を参照してください。 -
クライアント側トレースを開始し、DB インスタンスに対して実行される間、SQL クエリをリアルタイムで監視してください。
-
トレースが完了したら、[ファイル] メニューから [トレースの停止] を選択します。結果をファイルまたはトレーステーブルとして DB インスタンスに保存します。
SQL Server DB インスタンスでサーバー側トレースを実行する
サーバー側トレースを作成するスクリプトの作成は複雑になる可能性があるため、このドキュメントでは割愛します。このセクションでは、例として使用できるサンプルスクリプトを紹介します。クライアント側トレースと同様に、データベースエンジンチューニングアドバイザーを使用して開くことのできるワークロードファイルまたはトレーステーブルを作成することが目的です。
次に紹介する簡略化したサンプルスクリプトでは、サーバー側トレースを開始し、詳細をキャプチャしてワークロードファイルを作成します。トレースは、最初に D:\RDSDBDATA\Log ディレクトリの RDSTrace.trc ファイルに保存され、100 MB ごとにロールオーバーされて、それ以降のトレースファイルには RDSTrace_1.trc、RDSTrace_2.trc のように名前が付けられます。
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
以下の例はトレースを停止するスクリプトです。前述のスクリプトで作成されるトレースは、明示的にトレースを停止するか、プロセスがディスク容量を使い果たすまで継続されます。
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 という名前のテーブルにロードします。
SELECT * INTO RDSTrace FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default);
特定のロールオーバーファイルをテーブル (例えば、RDSTrace_1.trc ファイル) に保存するには、fn_trace_gettable の最後のパラメータとしてロールオーバーファイルの名前を指定し、デフォルトの代わりに 1 を代入します。
SELECT * INTO RDSTrace_1 FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1);
トレースを使用してチューニングアドバイザーを実行する
ローカルファイルまたはデータベーステーブルとしてトレースを作成したら、DB インスタンスに対してチューニングアドバイザーを実行できます。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 が含まれています。チューニングするサンプルデータベースは、C:\RDSTrace.trc という名前が付いたマシンです。サンプルコマンドラインコードでは、RDSTrace1 というトレースセッション名も指定し、ローカルマシンへの出力ファイルとして、SQL 出力スクリプトには RDSTrace.sql、結果ファイルには RDSTrace.txt、分析の XML ファイルには RDSTrace.xml という名前を指定します。また、RDSDTA データベースに RDSTraceErrors という名前のエラーテーブルが指定されます。
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 インスタンスにある RDSTrace データベースの RDSDTA というテーブルである点が異なります。
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 ユーティリティのコマンドラインパラメータの一覧については、Microsoft のドキュメントの dta Utility
