運用サーバーのチューニング負荷を軽減する

データベース エンジン チューニング アドバイザーは、クエリ オプティマイザーを使用してワークロードを分析し、チューニングに関する推奨事項を作成します。 運用サーバーでこの分析を実行すると、サーバーの負荷が増え、チューニング セッション中にサーバーのパフォーマンスが低下する可能性があります。 実稼働サーバーに加えてテスト サーバーを使用することで、チューニング セッション中のサーバー負荷への影響を軽減できます。

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

テスト サーバーを使用する従来の方法は、運用サーバーからテスト サーバーにすべてのデータをコピーし、テスト サーバーを調整してから、運用サーバーに推奨事項を実装することです。 このプロセスにより、運用サーバーに対するパフォーマンスへの影響はなくなりますが、それでも最適なソリューションではありません。 たとえば、運用環境からテスト サーバーに大量のデータをコピーすると、大量の時間とリソースが消費される可能性があります。 さらに、テスト サーバー ハードウェアは、実稼働サーバー用に展開されるハードウェアほど強力な機能はほとんどありません。 チューニング プロセスはクエリ オプティマイザーに依存し、生成される推奨事項は基になるハードウェアに基づいています。 テスト サーバーと実稼働サーバーのハードウェアが同一でない場合、データベース エンジン チューニング アドバイザーの推奨事項の品質が低下します。

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

次の手順では、テスト サーバーで運用データベースをチューニングするプロセスについて説明します。

1. テスト サーバーを使用するユーザーが両方のサーバーに存在することを確認します。

   開始する前に、テスト サーバーを使用して実稼働サーバー上のデータベースをチューニングするユーザーが両方のサーバーに存在することを確認します。 そのためには、テスト サーバーでユーザーとそのログインを作成する必要があります。 両方のコンピューターで sysadmin 固定サーバー ロールのメンバーである場合、この手順は必要ありません。

2. テスト サーバーでワークロードを調整します。

   テスト サーバーでワークロードをチューニングするには、 dta コマンド ライン ユーティリティで XML 入力ファイルを使用する必要があります。 XML 入力ファイルで、TuningOptions 親要素の他のサブ要素の値を指定するだけでなく、TestServer サブ要素を使用してテスト サーバーの名前を指定します。

   チューニング プロセス中に、データベース エンジン チューニング アドバイザーによって、テスト サーバー上にシェル データベースが作成されます。 このシェル データベースを作成してチューニングするために、データベース エンジン チューニング アドバイザーは次の目的で運用サーバーを呼び出します。

   1. データベース エンジン チューニング アドバイザーは、実稼働データベースからテスト サーバー シェル データベースにメタデータをインポートします。 このメタデータには、空のテーブル、インデックス、ビュー、ストアド プロシージャ、トリガーなどが含まれます。 これにより、ワークロード クエリをテスト サーバー シェル データベースに対して実行できます。

   2. データベース エンジン チューニング アドバイザーは、クエリ オプティマイザーがテスト サーバー上のクエリを正確に最適化できるように、実稼働サーバーから統計をインポートします。

   3. データベース エンジン チューニング アドバイザーは、プロセッサの数と使用可能なメモリを指定するハードウェア パラメーターを運用サーバーからインポートして、クエリ プランを生成するために必要な情報をクエリ オプティマイザーに提供します。

3. データベース エンジン チューニング アドバイザーは、テスト サーバー シェル データベースのチューニングが完了すると、チューニングの推奨事項を生成します。

4. テスト サーバーのチューニングから受け取った推奨事項を実稼働サーバーに適用します。

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

例

まず、チューニングを実行するユーザーがテスト サーバーと運用サーバーの両方に存在することを確認します。

ユーザー情報がテスト サーバーにコピーされたら、データベース エンジン チューニング アドバイザーの 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 入力ファイル リファレンスの使用に関する考慮事項(データベース エンジン チューニング アドバイザー)