Using Plan Guides in SQL Server 2005

By : Brad McGehee Feb 02, 2007

Printer friendly

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.