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.

Validating a Forced Plan

If the database in which the query is being run contains any plan guides at all, SQL Server must check whether any of the plan guides matches the query being processed. SQL Server hashes the query text and compares it with the hashed version of the queries for all existing plan guides, to verify a match. If it finds a match, SQL Server must verify that the plan guide matches the given query in the specified environment (the batch or module), and doing so takes extra time. Then the hints themselves need to be evaluated and compared with the plans that SQL Server would generate on its own. To guarantee that the forced plan with the hints is actually valid, SQL Server chooses to use only a plan that it could come up with on its own. The forced plan needs to be one that was considered, then rejected by the optimizer.

Let's look at an example of a plan that's considered invalid. The plan guide that Listing 2 creates is based on the query in the AdventureWorks database that Listing 3 shows. There are indexes on the SalesOrderID column in both the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables, and the index for Sales.SalesOrderHeader is unique. SQL Server's optimizer will realize this and determine that each table can be accessed by using a seek operation. In this situation, SQL Server 2005 won't consider a HASH JOIN to be any use. Nevertheless, SQL Server lets you create the plan guide in Listing 2 that includes the hint OPTION (HASH JOIN). However, if you then run the query in Listing 3 expecting that SQL Server will use the plan guide, you're in for a shock, and you'll get the unfriendly error message in Figure 2.

The moral here is be careful when using plan guides. Users running the query who don't know anything about your plan guides will also get that error message, and they might have no idea what's causing the problem.

Use Plan Guides with Care

In upcoming articles, we'll look at the other two types of plan guides and the new optimizer hints that you can use with them. Keep in mind, however, that hinting isn't something you should expect to have to do on every query. Plan guides are intended for situations where hinting is the only way to get the desired performance from your queries. Plan guides have overhead of their own, especially during query optimization, so overuse of them can potentially make slow performance even slower. But in those cases where plan guides are truly useful, they can be the best thing that ever happened to your SQL Server applications.