Using Plan Guides in SQL Server 2005

Here's how this code works:

@name = N'PlanGuide1',

The above line of code is used to give the Plan Guide it own unique name.

@stmt = N'SELECT COUNT(*) AS Total
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID and h.OrderDate
BETWEEN "1/1/2000" AND "1/1/2005" ',

This line of code is the code that the query optimizer is supposed to match. So whenever this code is sent from an application to the query optimizer, it tries to match the code. When it sees code that matches, like that specified above, then the query optimizer looks up the code in the Plan Guide lookup table, finds the correct Plan Guide, then applies the Plan Guide.

@type = N'SQL',

The above line of code specifies which one of three types of Plan Guide it is. In this case, it is a SQL Plan Guide.

@module_or_batch = NULL,

This line of code is used to specify the name of the object, assuming that this Plan Guide is an Object Plan Guide, which is not the case in this example.

@params = NULL,

The above line of code is only used if this is a Template Plan Guide, which it is not.

@hints = N'OPTION (MERGE JOIN)'

This line of code specifies the hint(s) to add to the query before it is compiled and executed.

The sp_control_plan_guide system stored procedure is used to drop, enable, or disable Plan Guides, using the following syntax:

sp_control_plan_guide [ @operation = ] N'<control_option>' [ , [ @name = ]
     N'plan_guide_name' ]

The <control_option> can be:

• DROP — Used to drop the plan guide specified by plan_guide_name.
• DROP ALL — Used to drop all plan guides in the current database.
• DISABLE — Used to disable the plan guide specified by plan_guide_name.
• DISABLE ALL — Used to disable all plan guides in the current database.
• ENABLE — Used to enable the plan guide specified by plan_guide_name.
• ENABLE ALL — Used to enable all plan guides in the current database.

Below are some examples of how you might use this stored procedure:

sp_control_plan_guide N'DROP', N'PlanGuide1'

sp_control_plan_guide N'DISABLE', N'PlanGuide1'

sp_control_plan_guide N'ENABLE', N'PlanGuide1'

Note: If you try to drop or modify a stored procedure, function, or DML trigger that is referenced by a Plan Guide, it causes an error. The Plan Guide must first be dropped.

Once you create a Plan Guide, you can use a catalog view to see what Plan Guides have been created in a particular database. For example, the following code produces these results:

SELECT * FROM sys.plan_guides

Note: Because of the width of the above results, it has been broken into two sections for easier viewing.

While Plan Guides can be used for applying virtually any hint to code, they are most often used in the following cases:

• To add the OPTIMIZE FOR or RECOMPILE query hints to parameterized queries.
• To add the USE PLAN query hint to force the use of a better-performing query plan.
• To force the non-parallel execution of a query plan using the MAXDOP hint.
• To force join types using join hints.
• To remove, modify, or replace currently existing query hints.

If you decide that you want to try using Plan Guides on your SQL Server 2005 servers, keep the following best practices in mind:

• Plan Guides should only be used by experienced DBAs.
• Only use a Plan Guide if there is no other option to resolve the performance of the poorly performing query.
• If you find that you have to create dozens and dozens of Plan Guides, odds are that you have other performance issues.
• Plan Guides should be thoroughly tested before being put into production.
• Plan Guides should be well documented.
• When upgrading SQL Server versions (or SPs), Plan Guides need to be evaluated to see if they still perform as expected.

Like many other performance tuning hints, Plan Guides are designed to resolve a very specific type of problem. While you may not use them much, if at all, it is nice to know they are available when you do need this functionality.