مشاركة عبر

  • مقالة

Using SQL Server Profiler to Create and Test Plan Guides

When you are creating a plan guide, you can use 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. After the plan guide is created, SQL Server Profiler can also be used to test that the plan guide is, in fact, being matched to the query. Generally, you should test plan guides by using SQL Server Profiler to verify that your query is being matched to your plan guide.

Capturing Query Text by Using SQL Server Profiler

If you run a query and capture the text exactly as it was submitted to SQL Server by using 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';

Suppose you want this query to execute using a merge join operation, but SHOWPLAN indicates that the query is not using a 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.

To capture the text of the query exactly as SQL Server receives it, follow these steps:

  1. Start a SQL Server Profiler trace, making sure that the SQL:BatchStarting event type is selected.

  2. Have the application run the query.

  3. Pause the SQL Server Profiler Trace.

  4. Click the SQL:BatchStarting event that corresponds to the query.

  5. Right-click and select Extract Event Data.

    Important

    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. Create the plan guide and paste the copied text inside the quotation marks ('') specified for the @stmt argument. 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. 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)';

Testing Plan Guides by Using SQL Server Profiler

To verify that a plan guide is being matched to a query, follow these steps:

  1. Start a 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. Pause the SQL Server Profiler Trace.

  4. Find the Showplan XML event for the affected query.

  5. 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. 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. These attributes are contained under the <StmtSimple> element of the plan.