実稼動サーバーのチューニング負荷の軽減

大きなワークロードをチューニングすると、チューニングしているサーバーでオーバーヘッドが大幅に増加することがあります。このオーバーヘッドは、チューニング処理中に、データベース エンジン チューニング アドバイザからクエリ オプティマイザに対して行われる多数の呼び出しが原因で発生します。実稼動サーバーに加えて、テスト サーバーも使用することで、このオーバーヘッドに関する問題を軽減できます。

データベース エンジン チューニング アドバイザでテスト サーバーを使用する方法

これまでは、テスト サーバーを使用するために、実稼動サーバーからテスト サーバーにすべてのデータをコピーし、テスト サーバーをチューニングして、実稼動サーバーに推奨設定を実装する方法を使用してきました。この処理により、実稼動サーバーのパフォーマンスに影響が及ぶことはありませんが、これは最善の解決策ではありません。たとえば、大量のデータを実稼動サーバーからテスト サーバーにコピーする場合、非常に時間がかかり、多量のリソースが使用される可能性があります。また、テスト サーバーのハードウェアが、実稼動サーバーに配置されているハードウェアほど優れていることはめったにありません。チューニング処理は、クエリ オプティマイザに依存し、生成される推奨設定は基になるハードウェアに部分的に基づきます。テスト サーバーと実稼動サーバーのハードウェアが異なる場合、データベース エンジン チューニング アドバイザの推奨設定の特性が低下します。

このような問題を防ぐために、データベース エンジン チューニング アドバイザでは、大部分のチューニング負荷をテスト サーバーにオフロードして、実稼動サーバー上のデータベースをチューニングします。このチューニングは、実際には実稼動サーバーからテスト サーバーにデータがコピーされずに、実稼動サーバーのハードウェア構成情報を使用して行われます。データベース エンジン チューニング アドバイザでは、実稼動サーバーからテスト サーバーに実際のデータがコピーされることはありません。メタデータと必要な統計だけがコピーされます。

次の手順は、テスト サーバーでの実稼動データベースのチューニング処理の概要を示しています。

1. テスト サーバーを使用するユーザーが、両方のサーバーに存在することを確認します。
   開始する前に、テスト サーバーを使用して実稼動サーバー上のデータベースをチューニングするユーザーが、両方のサーバーに存在することを確認します。このためには、テスト サーバーにユーザーとそのユーザーのログインを作成する必要があります。使用者が両方のコンピュータの sysadmin 固定サーバー ロールのメンバであれば、この手順は不要です。
2. テスト サーバーでワークロードをチューニングします。
   テスト サーバーでワークロードをチューニングするには、XML 入力ファイルと dta コマンド ライン ユーティリティを併用する必要があります。XML 入力ファイルで、TestServer サブ要素にテスト サーバーの名前を指定し、TuningOptions 親要素の下の他のサブ要素の値も指定します。
   チューニング処理中、データベース エンジン チューニング アドバイザによって、テスト サーバーにシェル データベースが作成されます。データベース エンジン チューニング アドバイザでは、このシェル データベースを作成してチューニングするために、次の目的で実稼動サーバーに呼び出しが行われます。
   1. 実稼動データベースからテスト サーバーのシェル データベースにメタデータをインポートします。このメタデータには、空のテーブル、インデックス、ビュー、ストアド プロシージャ、トリガなどが含まれます。これにより、テスト サーバーのシェル データベースに対してワークロード クエリを実行できるようになります。
   2. クエリ オプティマイザでテスト サーバーのクエリを正確に最適化できるように、実稼動サーバーから統計をインポートします。
   3. プロセッサ数と使用可能なメモリを指定するハードウェア パラメータを実稼動サーバーからインポートし、クエリ プランの生成に必要な情報をクエリ オプティマイザに提供します。
3. データベース エンジン チューニング アドバイザでは、テスト サーバーのシェル データベースのチューニング完了後、チューニングの推奨設定が生成されます。
4. テスト サーバーのチューニングによって作成された推奨設定を実稼動サーバーに適用します。

次の図は、テスト サーバーと実稼動サーバーのシナリオを示しています。

メモ :
データベース エンジン チューニング アドバイザのグラフィカル ユーザー インターフェイス (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>

参照

概念

テスト サーバーの使用に関する注意点

その他の技術情報

XML 入力ファイル リファレンス (DTA)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手