Plan
guides were introduced in SQL Server 2005 to provide the ability to add
a hint to a query without having to manually alter the query. Many
times, you are at the mercy of the queries thrown at your database by
certain applications. Plan guides let you supply a hint to those
incoming application queries in order to produce a more efficient query
plan.
SQL Server 2008 has
actually taken plan guides a step further so that you can provide an
entire XML query plan as a query hint. Unlike the USE PLAN query hint, an invalid XML query plan supplied as a query hint using plan guides will not cause the query to fail.
1. Creating a Plan Guide
To create a new plan guide,
expand the Programmability node under the database where you would like
the plan guide, right-click the Plan Guides folder, and select New Plan
Guide from the context menu to display the New Plan Guide dialog box, as
shown in Figure 1.
Let's create a plan guide for the same query used in Listing 1 of this article
to force the execution plan to use an index seek instead of an index
scan. The options available when creating a new plan guide include the
following:
Name: Enter a descriptive name for the plan guide. We named ours PlanGuide-Ch15Test.
Statement: Enter the T-SQL statement for which you would like to apply the plan guide. We entered the T-SQL statement from Listing 1 of this article.
|
Make sure you enter the T-SQL
statement exactly as it will be sent to SQL Server. Even extra spaces
will cause the query optimizer to ignore the plan guide.
|
|
Scope Type:
The scope type specifies the context for matching the T-SQL statement
to the plan guide. Valid values are Object, Sql, and Template. Object is
the stored procedure, scalar function, multi-statement table-valued
function, or T-SQL Data Manipulation Language (DML) trigger that
contains the T-SQL statement. Sql indicates that the T-SQL statement is
in the context of a stand-alone statement or batch. Template is used to
change the default query parameterization behavior. If Template is
selected, you can only use the PARAMETERIZATION {FORCED | SIMPLE} query hint. We will be using the Sql scope type in this example.
Scope Batch:
Enter the T-SQL batch that contains the statement for which you would
like to apply the plan guide. If you leave the Scope Batch box blank,
the Statement text will be used.
Scope Schema Name: If the scope type is Object, enter the name of the schema that contains the object.
Scope Object Name:
If the scope type is Object, enter the name of the object that contains
the T-SQL statement for which you would like to apply the plan guide.
Parameters:
Enter the parameter name and data type for the T-SQL statement. The
parameters must be submitted exactly as they appear using the sp_executesql statement. If the scope type is Template, parameters are required.
Hints: Enter the query hint or XML execution plan that will be applied to the T-SQL statement. SQL Server 2008 allows you to use the FORCESEEK
table hint as a query hint by entering the following statement. (You
should also notice that we are referencing the alias B for the table
name we used in the query instead of the actual table name.)
OPTION (TABLE HINT (B, FORCESEEK))
The final T-SQL command to create the plan guide is shown in Listing 1. You can query the sys.plan_guides catalog view to return information for each plan guide you have created in the current database.
Example 1. T-SQL Code to Create the Plan Guide Shown in Figure 1
USE AdventureWorks2008
GO
EXEC sp_create_plan_guide
@name = N'[PlanGuide-Ch15Test]',
@stmt = N'Select *
FROM HumanResources.Employee A
JOIN HumanResources.EmployeeDepartmentHistory B
ON A.BusinessEntityID = B.BusinessEntityID
WHERE A.BusinessEntityID > 270 OR
(A.BusinessEntityID < 10 and B.DepartmentID =1)',
@type = N'SQL',
@hints = N'OPTION (TABLE HINT(B, FORCESEEK))'
GO
|
2. Validating a Plan Guide
Because a plan guide will not
cause a query to fail, it is important to validate the plan guides. This
is primarily necessary after you perform an upgrade to SQL Server or
change the underlying data structure in the database. If the plan guide
is invalid, the query optimizer will ignore it and generate a new
execution plan for the query. You can use the sys.fn_validate_plan_guide function to validate a plan guide by passing the plan_guide_id function from the sys.plan_guides catalog view. The sys.fn_validate_plan_guide
function will return the first error message encountered when the plan
guide is applied to the query. If you receive an empty result set, the
plan guide does not contain any errors. The following script will check
the validity of all the plan guides in the AdventureWorks2008 database.
USE AdventureWorks2008
GO
SELECT plan_guide_id, msgnum, severity, state, message
FROM sys.plan_guides
CROSS APPLY sys.fn_validate_plan_guide(plan_guide_id)
You can use the Plan Guide Successful and Plan Guide Unsuccessful
events in SQL Server Profiler located under the Performance node to
capture valid and invalid attempts of the query optimizer using plan
guides. Start a new Profiler trace and select Show All Events, expand
the Performance node, and select Plan Guide Successful and Plan Guide
Unsuccessful, as shown in Figure 2. Select Run to start the trace.
Now, execute the query from Listing 1 of this article that we used to create the plan guide. As you can see in Figure 3,
the query optimizer successfully applied the plan guide to the query,
and then the query was executed using the hint provided in the plan
guide.
