SQL Server Profiler を使用したプラン ガイドの作成とテスト

  • [アーティクル]

適用対象:SQL ServerAzure SQL Database

プラン ガイドを作成するとき、sp_create_plan_guide ストアド プロシージャの statement_text 引数に使用するために、SQL Server Profiler を使用して正確なクエリ テキストをキャプチャできます。 これにより、コンパイル時にプラン ガイドをクエリに一致させることができます。 プラン ガイドを作成した後、プラン ガイドが実際にクエリに一致することをテストするためにも SQL Server Profiler を使用できます。 通常、クエリがプラン ガイドに一致することを確認するには、SQL Server Profiler を使用してプラン ガイドをテストする必要があります。

SQL Server Profiler を使用したクエリ テキストのキャプチャ

クエリを実行し、SQL Server Profiler を使用して SQL Server に送信されたテキストを正確にキャプチャすると、そのクエリ テキストに正確に一致する SQL 型または TEMPLATE 型のプラン ガイドを作成できます。 これにより、このプラン ガイドをクエリ オプティマイザーに使用させることができます。

スタンドアロン バッチとしてアプリケーションから送信される次のクエリについて考えてみます。

SELECT COUNT(*) AS c  
FROM Sales.SalesOrderHeader AS h  
INNER JOIN Sales.SalesOrderDetail AS d  
  ON h.SalesOrderID = d.SalesOrderID  
WHERE h.OrderDate BETWEEN '20000101' and '20050101';

マージ結合操作を使用してこのクエリを実行する必要がありますが、クエリでマージ結合を使用しないことを SHOWPLAN が示しているとします。 アプリケーションでクエリを直接変更することはできませんが、代わりにプラン ガイドを作成して、コンパイル時に MERGE JOIN クエリ ヒントがクエリに追加されるように指定します。

SQL Server が受信したクエリのテキストを正確にキャプチャするには、次の手順に従います。

  1. SQL Server Profiler トレースを開始し、イベントの種類として SQL:BatchStarting が選択されていることを確認します。

  2. アプリケーションでクエリを実行します。

  3. SQL Server Profiler トレースの一時停止。

  4. クエリに対応する SQL:BatchStarting イベントをクリックします。

  5. イベントを右クリックして [イベント データの抽出]をクリックします。

    重要

    SQL Server Profiler のトレース ウィンドウ内の下のペインで、バッチ テキストを選択してコピーすることは避けてください。 作成するプラン ガイドが元のバッチと一致しなくなる場合があります。

  6. ファイルにイベント データを保存します。 バッチ テキストが保存されます。

  7. メモ帳でバッチ テキスト ファイルを開き、テキストをクリップボードにコピーします。

  8. プラン ガイドを作成し、@stmt引数に指定する引用符 ( ' ') 内にコピーしたテキストを貼り付けます。 @stmt 引数内に単一引用符がある場合は、その前にもう 1 つ単一引用符を追加してエスケープする必要があります。 単一引用符を挿入する際は、別の文字を追加したり削除したりしないように注意してください。 たとえば、日付リテラル '20000101' は、 ''20000101''として区切る必要があります。

次に、このプラン ガイドを示します。

EXEC sp_create_plan_guide   
    @name = N'MyGuide1',  
    @stmt = N'<paste the text copied from the batch text file here>',  
    @type = N'SQL',  
    @module_or_batch = NULL,  
    @params = NULL,  
    @hints = N'OPTION (MERGE JOIN)';

SQL Server Profiler を使用したプラン ガイドのテスト

プラン ガイドがクエリに一致することを確認するには、次の手順に従います。

  1. SQL Server Profiler トレースを開始し、イベントの種類として Showplan XML が選択されていることを確認します ([Performance] ノードの下にあります)。

  2. アプリケーションでクエリを実行します。

  3. SQL Server Profiler トレースの一時停止。

  4. 影響するクエリの Showplan XML イベントを検索します。

    注意

    Showplan XML for Query Compile イベントは使用できません。 PlanGuideDB はそのイベントに存在しません。

  5. プラン ガイドが OBJECT 型または SQL 型の場合は、 Showplan XML イベントに、クエリと一致させるプラン ガイドの PlanGuideDB 属性と PlanGuideName 属性が含まれていることを確認します。 または、TEMPLATE プラン ガイドの場合は、 Showplan XML イベントに、クエリと一致させるプラン ガイドの TemplatePlanGuideDB 属性と TemplatePlanGuideName 属性が含まれていることを確認します。 これにより、プラン ガイドが機能していることを確認できます。 これらの属性は、プランの <StmtSimple> 要素に含まれます。

参照

sp_create_plan_guide (Transact-SQL)