SQL Server 2008 : Managing Query Performance - Adding Hints Through Plan Guides

2/19/2011 4:03:37 PM
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.

Figure 1. New Plan Guide dialog box

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.)


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

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',

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

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.

Figure 2. Plan guide events in SQL Server Profiler

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.

Figure 3. Successful plan guide execution captured using SQL Server Profiler

Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us