Help for Query Hints

In recent articles, I've discussed some of the ways to detect information about the plans SQL Server is using to access data that your queries have requested. Our next step is to explore some of the ways that you can affect what plan will actually be used. Although Microsoft typically recommends that you let the SQL Server query optimizer determine the plan to use for a query, sometimes you need to provide a little guidance for the optimizer in the form of hints. Supplying a query hint is a usually a straightforward change to your application code. However, in certain environments, you might either have no control over the code itself, or changing the code will break your licensing agreement or invalidate your support guarantees.

In such situations, SQL Server 2005's plan guides feature can be helpful. Using a plan guide, you can instruct SQL Server to use a particular hint every time it encounters a specified query, and you don't need to change the query itself. Although plan guides are frequently used with the new optimization hints included in SQL Server 2005, you can use them with almost any hint. Let's start our exploration of plan guides with an overview of how they work, then look at why a plan guide might not work as intended.

Plan Guides Overview
SQL Server 2005 supports three types of plan guides, all of which can be created by using the same procedure. Although almost all other objects in SQL Server 2005 use standard Data Definition Language (DDL)—CREATE to create the object, ALTER to change the object properties, and DROP to remove the object—plan guides haven't quite caught up with that paradigm. SQL Server 2005 provides two new T-SQL stored procedures for working with plan guides: sp_create_plan_guide, which creates a plan guide, and sp_control_plan_guide, which changes a plan guide's properties or removes a plan guide. Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 96134) shows the general form of the sp_create_plan_guide procedure.

The three types of plan guides are

• SQL—tells the optimizer to look for a specific SQL statement in your application as specified in the @stmt parameter. If the @module_or_batch parameter is NULL, the SQL statement must appear in a batch by itself. Otherwise, the @module_or_batch parameter should include the exact text of the entire batch that the statement will appear in.
• OBJECT—tells the optimizer to look for a specific statement in a specific module. The module can be a stored procedure or function, and its name is given in the @module_or_batch parameter.
• TEMPLATE—tells the optimizer to build a template based on a class of queries. Since this type is a bit more complex than the other two, I'll wait to discuss it further in a future article, after I've explained the other two types. And because the @params parameter to sp_create_plan_guide is also only used in conjunction with TEMPLATE plan guides, I'll discuss that parameter in an upcoming article as well.
  

Creating an SQL Plan Guide
On my SQL Server system, the execution plan for the following query (which you should run in the AdventureWorks database) shows that SQL Server will run this query in parallel, over multiple CPUs:

SELECT TOP 10 * 
  FROM Sales.SalesOrderDetail
  ORDER BY UnitPrice DESC;

Figure 1 shows the right side of the graphical plan for the query.

Whether the plan you get for this query will involve parallelism depends on a number of factors, not least of which is whether or not your server has multiple processors available. If I'm having problems with parallel queries, I might decide to force this query to be run on a single CPU, whenever the query is run as a batch in an application. I could do so by creating a plan guide specifying the previous query as the SQL statement and not specifying a value for the @module_or_batch parameter. As the plan guide in Listing 1 shows, I've used the MAXDOP query hint as part of the @hints parameter to specify a maximum degree of parallelism of 1 (i.e., a single CPU).

Once this plan is created in the AdventureWorks database, whenever the optimizer encounters the specified statement in a batch by itself, it will create a plan that uses only a single CPU. If the specified query occurs as part of a larger batch, the optimizer won't invoke the plan guide.

Enabling, Disabling, or Removing a Plan Guide
You can enable or disable a plan guide by using the sp_control_plan_guide procedure. For example, I could disable the plan guide created in Listing 1 by using this statement:

EXEC sp_control_plan_guide
  N'DISABLE',
  N'plan_SalesOrderDetail_DOP1'

(Note that some statements in this article wrap to multiple lines because of space constraints.) My query would then revert to potentially using multiple CPUs when it was executed. I could re-enable the plan guide, also by using the sp_control_plan_ guide procedure, like this:

EXEC sp_control_plan_guide
  N'ENABLE',
  N'plan_SalesOrderDetail_DOP1';

I'd also use sp_control_plan_guide to remove the plan guide, like this:

EXEC sp_control_plan_guide
  N'DROP',
  N'plan_
  SalesOrderDetail_ DOP1';

Plan guides aren't intended to save on optimization time, only to make sure that your applications can perform well if you've specified hints that can help the queries they indicate. The queries themselves might run faster with the hints forced by the plan guides, but optimization can take considerably longer.