• Article

How to: Create a Plan Guide (SQL Server Management Studio)

This topic describes how to create a plan guide by using SQL Server Management Studio. Plan guides influence query optimization by attaching query hints or a fixed query plan to them. In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. When the query executes, the query optimizer matches the Transact-SQL statement to the plan guide and either attaches the OPTION clause to the query at run time or uses the specified query plan.

The following example creates a plan guide for a Transact-SQL statement and applies the FORCESEEK query hint to the statement. This query hint forces the optimizer to use an index seek operation to access the data in the specified table.

To create a plan guide example

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. Expand Databases, expand the AdventureWorks2008R2 database, and then expand Programmability.

  3. Right-click Plan Guides, and then click New Plan Guide.

  4. In Name, enter ForceseekPlan as the name of the plan guide.

  5. In Statement, enter the following Transact-SQL statement. This is the statement against which the plan guide is to be applied.

    SELECT p.LastName, p.FirstName, HumanResources.Employee.JobTitle
FROM HumanResources.Employee
JOIN Person.Person AS p ON HumanResources.Employee.BusinessEntityID = p.BusinessEntityID
WHERE HumanResources.Employee.OrganizationLevel = 3 ORDER BY p.LastName, p.FirstName

  6. In Scope type, select SQL as the type of entity in which the Transact-SQL statement appears.

  7. In Hints, enter the following OPTION clause.

    OPTION (TABLE HINT(HumanResources.Employee, FORCESEEK))

  8. To create the plan guide, click OK.

To verify that the plan guide is being matched to a query

  1. Start a SQL Server Profiler trace, making certain that the Plan Guide Successful and Plan Guide Unsuccessful event types are selected (located under the Performance node).

  2. Run the query provided in step 5 of the previous procedure.

  3. Pause the SQL Server Profiler Trace.

  4. Find the Plan Guide Successful event for the affected query.

  5. If the plan guide could not be matched to the query, ensure that the query is provided in the same format, character-for-character, as it was specified in the plan guide statement. This includes tabs, spaces, carriage returns, or line feeds.