Using Plan Guides in SQL Server 2005

 

Using Plan Guides in SQL Server 2005

By : Brad McGehee http://www.sql-server-performance.com Feb 02, 2007

One of the biggest frustrations for a DBA is performance tuning third-party applications. In most cases you can't modify the code directly, so you have to live with the code the application provides you. In most cases, your only opportunity for tuning third-party applications is to add or modify indexes. Sometimes, this can really help out performance.

But what happens if you have added all the appropriate indexes and discover that there are a couple of really bad performing queries that can't be helped by indexing? One option is to complain to the application's vendor. They might actually listen and fix the problem in the next patch. But most likely they won't, and you will be blamed for a problem you can't fix, even though you know exactly what the problem is.

In SQL Server 2005, there is a new feature called Plan Guides that can help out in some cases where you discover poorly performing queries that you don't have direct control over. Essentially, a Plan Guide allows you to add or modify query hints to queries on the fly, just before they are executed.

Here's how they work:

• When an application sends code to SQL Server, the code is first checked by the query optimizer to see if there is an appropriate query plan already cached in the buffer. If so, then the query will be executed with the currently cached execution plan.
• If there is not a match, then the code is compared to an internal lookup table to see if it matches a pre-existing Plan Guide.
• If there is a match to a pre-existing Plan Guide, the original code is modified by the query optimizer to include the hint(s) specified in the Plan Guide. Any previous hint(s) are removed and replaced by the new hint(s).
• The query plan is then compiled and cached.
• The query is executed using the hint(s) you have specified in the Plan Guide. Hopefully now, the query will perform much better.

I am not a personal fan of using query hints to modify queries, but in some cases they are necessary to fix badly behaving queries. Plan Guides offer an opportunity to "fix" some poorly performing queries that you aren't able to directly touch and fix yourself. Of course, the assumption here is that you are an experienced DBA and know enough to recognize when a hint is appropriate or not appropriate for a particular query.

Plan Guides can be applied to queries in three different situations:

• Object Plan Guide: Matches queries that execute in the context of stored procedures, scalar functions, multi-statement table-valued functions, and DML triggers. Identifies specific objects by object name.
• SQL Plan Guide: Matches queries that execute in the context of Transact-SQL statements and batches. Affects specific queries by matching the actual code.
• Template Plan Guide: Matches queries that parameterize to a specified form. This option affects an entire class of queries, not just a single one. If the TEMPLATE option is specified, only the PARAMETERIZATION {FORCED | SIMPLE} query hint can be specified in the hints parameter, making this a very limiting option.

Although it would be rare to use many of the hints, the following hints can be included in a Plan Guide. Any combination of valid query hints is fine.

• {HASH | ORDER} GROUP
• {CONCAT | HASH | MERGE} UNION
• {LOOP | MERGE | HASH} JOIN
• FAST number_rows
• FORCE ORDER
• MAXDOP number_of_processors
• OPTIMIZE FOR ( @variable_name = literal_constant ) [ ,…n ]
• RECOMPILE
• ROBUST PLAN
• KEEP PLAN
• KEEPFIXED PLAN
• EXPAND VIEWS
• MAXRECURSION number
• USE PLAN <xmlplan>

There are two stored procedures used to create and manage Plan Guides:

• sp_create_plan_guide
• sp_control_plan_guide

Let's take a brief look at each.

As you can probably guess, sp_create_plan_guide is used to create new Plan Guides, using the following syntax.

sp_create_plan_guide [ @name = ] N'plan_guide_name'
     , [ @stmt = ] N'statement_text'
     , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
     , [ @module_or_batch = ]
       {
                    N'[ schema_name. ] object_name'
          | N'batch_text'
          | NULL
        }
     , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
     , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' | NULL }

Since syntax examples, like the one above, are a little hard to follow, let's look at a real example.

sp_create_plan_guide
@name = N'PlanGuide1',
@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" ',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MERGE JOIN)'
GO

As I mentioned earlier, there are three kinds of Plan Guides. The example above is a SQL Plan Guide, which is used to identify a particular string of code. Once the code is identified and looked up in the Plan Guide lookup table, the appropriate hint is added to the query for compilation and execution.

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.