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

適用対象:SQL ServerAzure SQL Database

データベース エンジンチューニング アドバイザーは、ワークロードの分析とチューニング推奨設定の生成をクエリ オプティマイザーに依存します。 実稼働サーバー上でこの分析を実行すると、サーバーの負荷が増し、チューニング セッション中のサーバーのパフォーマンスが低下することがあります。 実稼働サーバーに加えてテスト サーバーを使用することで、チューニング セッション中のサーバーの負荷への影響を小さくすることができます。

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

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

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

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

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="http://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 入力ファイル リファレンス (データベース エンジン チューニング アドバイザー)