実稼動サーバーのチューニング負荷の軽減
データベース エンジン チューニング アドバイザは、ワークロードの分析とチューニング推奨設定の生成をクエリ オプティマイザに依存します。実稼働サーバー上でこの分析を実行すると、サーバーの負荷が増し、チューニング セッション中のサーバーのパフォーマンスが低下することがあります。実稼働サーバーに加えてテスト サーバーを使用することで、チューニング セッション中のサーバーの負荷への影響を小さくすることができます。
また、SQL Server 2008 を使用することで、データベース エンジン チューニング アドバイザのサーバーの負荷に対する影響を小さくすることもできます。以前のバージョンのデータベース エンジン チューニング アドバイザでは、クエリ オプティマイザを使用して、チューニングされたテーブルを参照するプランを再コンパイルできました。再コンパイルは、データベース エンジン チューニング アドバイザがチューニング セッション中にプロシージャ キャッシュ内のプランを無効化したときに発生します。再コンパイルは、チューニング セッション後に初めてクエリが実行されるまで、発生しません。SQL Server 2008 データベース チューニング アドバイザはプロシージャ キャッシュ内のプランを無効化することはないので、このような追加の再コンパイルは発生しません。
テスト サーバーでデータベース エンジン チューニング アドバイザを使用する方法を次に示します。
データベース エンジン チューニング アドバイザでテスト サーバーを使用する方法
これまでは、テスト サーバーを使用するために、実稼働サーバーからテスト サーバーにすべてのデータをコピーし、テスト サーバーをチューニングして、実稼働サーバーに推奨設定を実装する方法を使用してきました。この処理により、実稼働サーバーのパフォーマンスに影響が及ぶことはありませんが、これは最善の解決策ではありません。たとえば、大量のデータを実稼働サーバーからテスト サーバーにコピーする場合、非常に時間がかかり、多量のリソースが使用される可能性があります。また、テスト サーバーのハードウェアが、実稼働サーバーに配置されているハードウェアほど優れていることはめったにありません。チューニング処理は、クエリ オプティマイザに依存し、生成される推奨設定は基になるハードウェアに部分的に基づきます。テスト サーバーと実稼働サーバーのハードウェアが異なる場合、データベース エンジン チューニング アドバイザの推奨設定の特性が低下します。
このような問題を防ぐために、データベース エンジン チューニング アドバイザでは、大部分のチューニング負荷をテスト サーバーにオフロードして、実稼働サーバー上のデータベースをチューニングします。このチューニングは、実際には実稼働サーバーからテスト サーバーにデータがコピーされずに、実稼働サーバーのハードウェア構成情報を使用して行われます。データベース エンジン チューニング アドバイザでは、実稼働サーバーからテスト サーバーに実際のデータがコピーされることはありません。メタデータと必要な統計だけがコピーされます。
次の手順は、テスト サーバーでの実稼働データベースのチューニング処理の概要を示しています。
テスト サーバーを使用するユーザーが、両方のサーバーに存在することを確認します。
開始する前に、テスト サーバーを使用して実稼働サーバー上のデータベースをチューニングするユーザーが、両方のサーバーに存在することを確認します。このためには、テスト サーバーにユーザーとそのユーザーのログインを作成する必要があります。使用者が両方のコンピュータの sysadmin 固定サーバー ロールのメンバであれば、この手順は不要です。
テスト サーバーでワークロードをチューニングします。
テスト サーバーでワークロードをチューニングするには、XML 入力ファイルと dta コマンド ライン ユーティリティを併用する必要があります。XML 入力ファイルで、TestServer サブ要素にテスト サーバーの名前を指定し、TuningOptions 親要素の下の他のサブ要素の値も指定します。
チューニング処理中、データベース エンジン チューニング アドバイザによって、テスト サーバーにシェル データベースが作成されます。データベース エンジン チューニング アドバイザでは、このシェル データベースを作成してチューニングするために、次の目的で実稼働サーバーに呼び出しが行われます。
データベース エンジン チューニング アドバイザは、実稼働データベースからテスト サーバーのシェル データベースにメタデータをインポートします。このメタデータには、空のテーブル、インデックス、ビュー、ストアド プロシージャ、トリガなどが含まれます。これにより、テスト サーバーのシェル データベースに対してワークロード クエリを実行できるようになります。
データベース エンジン チューニング アドバイザは、クエリ オプティマイザでテスト サーバーのクエリを正確に最適化できるように、実稼働サーバーから統計をインポートします。
データベース エンジン チューニング アドバイザは、プロセッサ数と使用可能なメモリを指定するハードウェア パラメータを実稼働サーバーからインポートし、クエリ プランの生成に必要な情報をクエリ オプティマイザに提供します。
データベース エンジン チューニング アドバイザでは、テスト サーバーのシェル データベースのチューニング完了後、チューニングの推奨設定が生成されます。
テスト サーバーのチューニングによって作成された推奨設定を実稼働サーバーに適用します。
次の図は、テスト サーバーと実稼働サーバーのシナリオを示しています。
.gif "データベース エンジン チューニング アドバイザでテスト サーバーを使用する方法")
.gif "注意") 注意 |
|---|
データベース エンジン チューニング アドバイザのグラフィカル ユーザー インターフェイス (GUI) では、テスト サーバーのチューニング機能はサポートされません。 |
例
最初に、チューニングを実行するユーザーが、テスト サーバーと実稼働サーバーの両方に存在することを確認します。
ユーザー情報をテスト サーバーにコピーした後、データベース エンジン チューニング アドバイザの XML 入力ファイルでテスト サーバーのチューニング セッションを定義できます。次の XML 入力ファイルの例は、テスト サーバーを指定して、データベース エンジン チューニング アドバイザを使用してデータベースをチューニングする方法を示しています。
この例では、MyDatabaseName データベースが MyServerName でチューニングされています。Transact-SQL スクリプトの MyWorkloadScript.sql をワークロードとして使用しています。このワークロードには、MyDatabaseName に対して実行するイベントが含まれています。このデータベースに対し、クエリ オプティマイザによって行われるほとんどの呼び出しは、チューニング処理の一部として行われ、MyTestServerName に存在するシェル データベースによって処理されます。シェル データベースは、メタデータと統計で構成されます。この処理により、チューニングのオーバーヘッドがテスト サーバーにオフロードされます。データベース エンジン チューニング アドバイザでは、この XML 入力ファイルを使用してチューニングの推奨設定を生成するとき、インデックスのみ (<FeatureSet>IDX</FeatureSet>) を考慮し、パーティション分割を行わず、MyDatabaseName に既存の物理デザイン構造を保持する必要がありません。
<?xml version="1.0" encoding="utf-16" ?>
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://schemas.microsoft.com/sqlserver/2004/07/dta">
<DTAInput>
<Server>
<Name>MyServerName</Name>
<Database>
<Name>MyDatabaseName</Name>
</Database>
</Server>
<Workload>
<File>MyWorkloadScript.sql</File>
</Workload>
<TuningOptions>
<TestServer>MyTestServerName</TestServer>
<FeatureSet>IDX</FeatureSet>
<Partitioning>NONE</Partitioning>
<KeepExisting>NONE</KeepExisting>
</TuningOptions>
</DTAInput>
</DTAXML>