SQL Server Profiler を使用したプラン ガイドの作成とテストUse SQL Server Profiler to Create and Test Plan Guides

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

プラン ガイドを作成するとき、 SQL Server プロファイラーSQL Server Profiler sp_create_plan_guide ストアド プロシージャの statement_text 引数に使用するために、 を使用して正確なクエリ テキストをキャプチャできます。 SQL Server プロファイラーSQL Server Profiler When you are creating a plan guide, you can use SQL Server プロファイラーSQL Server Profiler to capture the exact query text for use in the *statement_text* argument of the **sp_create_plan_guide** stored procedure. これにより、コンパイル時にプラン ガイドをクエリに一致させることができます。This helps make sure that the plan guide will be matched to the query at compile time. プラン ガイドを作成した後、プラン ガイドが実際にクエリに一致することをテストするためにも SQL Server プロファイラーSQL Server Profiler を使用できます。After the plan guide is created, SQL Server プロファイラーSQL Server Profiler can also be used to test that the plan guide is, in fact, being matched to the query. 通常、クエリがプラン ガイドに一致することを確認するには、 SQL Server プロファイラーSQL Server Profiler を使用してプラン ガイドをテストする必要があります。Generally, you should test plan guides by using SQL Server プロファイラーSQL Server Profiler to verify that your query is being matched to your plan guide.

SQL Server Profiler を使用したクエリ テキストのキャプチャCapturing Query Text by Using SQL Server Profiler

クエリを実行し、 SQL ServerSQL Server を使用して SQL Server プロファイラーSQL Server Profilerに送信されたテキストを正確にキャプチャすると、そのクエリ テキストに正確に一致する SQL 型または TEMPLATE 型のプラン ガイドを作成できます。If you run a query and capture the text exactly as it was submitted to SQL ServerSQL Server by using SQL Server プロファイラーSQL Server Profiler, you can create a plan guide of type SQL or TEMPLATE that will match the query text exactly. これにより、このプラン ガイドをクエリ オプティマイザーに使用させることができます。This makes sure that the plan guide is used by the query optimizer.

スタンドアロン バッチとしてアプリケーションから送信される次のクエリについて考えてみます。Consider the following query that is submitted by an application as a stand-alone batch:

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 が示しているとします。Suppose you want this query to execute using a merge join operation, but SHOWPLAN indicates that the query is not using a merge join. アプリケーションでクエリを直接変更することはできませんが、代わりにプラン ガイドを作成して、コンパイル時に MERGE JOIN クエリ ヒントがクエリに追加されるように指定します。You cannot change the query directly in the application, so instead you create a plan guide to specify that the MERGE JOIN query hint be appended to the query at compile time.

SQL ServerSQL Server が受信したクエリのテキストを正確にキャプチャするには、次の手順に従います。To capture the text of the query exactly as SQL ServerSQL Server receives it, follow these steps:

  1. SQL Server プロファイラーSQL Server Profiler トレースを開始し、イベントの種類として SQL:BatchStarting が選択されていることを確認します。Start a SQL Server プロファイラーSQL Server Profiler trace, making sure that the SQL:BatchStarting event type is selected.

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

  3. SQL Server プロファイラーSQL Server Profiler トレースを一時停止します。Pause the SQL Server プロファイラーSQL Server Profiler Trace.

  4. クエリに対応する SQL:BatchStarting イベントをクリックします。Click the SQL:BatchStarting event that corresponds to the query.

  5. イベントを右クリックして [イベント データの抽出] をクリックします。Right-click and select Extract Event Data.

    重要

    SQL Server Profiler のトレース ウィンドウ内の下のペインで、バッチ テキストを選択してコピーすることは避けてください。Do not try to copy the batch text by selecting it from the lower pane of the Profiler trace window. 作成するプラン ガイドが元のバッチと一致しなくなる場合があります。This might cause the plan guide that you create to not match the original batch.

  6. ファイルにイベント データを保存します。Save the event data to a file. バッチ テキストが保存されます。This is the batch text.

  7. メモ帳でバッチ テキスト ファイルを開き、テキストをクリップボードにコピーします。Open the batch text file in Notepad and copy the text to the copy and paste buffer.

  8. プラン ガイドを作成し、 @stmt 引数に指定する引用符 ( '' ) 内にコピーしたテキストを貼り付けます。Create the plan guide and paste the copied text inside the quotation marks ('') specified for the @stmt argument. @stmt 引数内に単一引用符がある場合は、その前にもう 1 つ単一引用符を追加してエスケープする必要があります。You must escape any single quotation marks in the stmt argument by preceding them with another single quotation mark. 単一引用符を挿入する際は、別の文字を追加したり削除したりしないように注意してください。Be careful not to add or remove any other characters when you insert these single quotation marks. たとえば、日付リテラル ' 20000101 ' は、 '' 20000101 '' として区切る必要があります。For example, the date literal ' 20000101 ' must be delimited as '' 20000101 ''.

次に、このプラン ガイドを示します。Here is the plan guide:

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 を使用したプラン ガイドのテストTesting Plan Guides by Using SQL Server Profiler

プラン ガイドがクエリに一致することを確認するには、次の手順に従います。To verify that a plan guide is being matched to a query, follow these steps:

  1. SQL Server プロファイラーSQL Server Profiler トレースを開始し、イベントの種類として Showplan XML が選択されていることを確認します ( [Performance] ノードの下にあります)。Start a SQL Server プロファイラーSQL Server Profiler trace, making certain that the Showplan XML event type is selected (located under the Performance node).

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

  3. SQL Server プロファイラーSQL Server Profiler トレースを一時停止します。Pause the SQL Server プロファイラーSQL Server Profiler Trace.

  4. 影響するクエリの Showplan XML イベントを検索します。Find the Showplan XML event for the affected query.

    注意

    Showplan XML for Query Compile イベントは使用できません。The Showplan XML for Query Compile event cannot be used. PlanGuideDB はそのイベントに存在しません。 PlanGuideDB does not exist in that event.

  5. プラン ガイドが OBJECT 型または SQL 型の場合は、 Showplan XML イベントに、クエリと一致させるプラン ガイドの PlanGuideDB 属性と PlanGuideName 属性が含まれていることを確認します。If the plan guide is of type OBJECT or SQL, verify that the Showplan XML event contains the PlanGuideDB and PlanGuideName attributes for the plan guide that you expected to match the query. または、TEMPLATE プラン ガイドの場合は、 Showplan XML イベントに、クエリと一致させるプラン ガイドの TemplatePlanGuideDB 属性と TemplatePlanGuideName 属性が含まれていることを確認します。Or, in the case of a TEMPLATE plan guide, verify that the Showplan XML event contains the TemplatePlanGuideDB and TemplatePlanGuideName attributes for the expected plan guide. これにより、プラン ガイドが機能していることを確認できます。This verifies that the plan guide is working. これらの属性は、プランの <StmtSimple> 要素に含まれます。These attributes are contained under the StmtSimple> element of the plan.

参照See Also

sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)