Combining SQL and TEMPLATE Plan Guides

TEMPLATE plan guides allow two optimization hints, PARAMETERIZATION SIMPLE and PARAMETERIZATION FORCED, which give you some control over SQL Server 2005's query autoparameterization feature. (For more information about autoparameterization, see "Controlling Parameterization," August 2007, InstantDoc ID 96349.) These hints, which are actually the only hints that TEMPLATE plan guides can use, force SQL Server to always use the same plan for an entire "class" of queries and to treat the constant values in the queries as if they were parameters. However, as you saw in "Help for Query Hints," InstantDoc ID 96134, many other hints can be used in SQL or OBJECT plans; in fact, any hint available in the OPTION clause can be used as part of an SQL or OBJECT plan guide. Here, we'll look at how SQL Server determines applicable plan guides for a query, then review an example of combining TEMPLATE plans for controlling autoparameterization with SQL plans, which allow all sorts of other query processing hints to be specified. Using the TEMPLATE plans gives you more control over how the SQL Server relational database engine will process the query, to provide better performance than the query engine might do on its own.

Plan Guide Logic

To determine whether a plan guide is applicable to the current query being processed, when a query is submitted, SQL Server first determines whether any plan guides are in the database. If it finds a plan guide, SQL Server checks whether the statement being optimized matches an SQL plan guide or an OBJECT plan guide. If it finds no matching SQL or OBJECT plan guides, SQL Server then checks for a TEMPLATE plan guide. If it finds an appropriate TEMPLATE guide, SQL Server creates a parameterized form of the current query. Finally, SQL Server tries to match the parameterized query to an SQL plan guide and, if it does so, makes it possible for you to apply additional hints to your queries by using forced parameterization.

Web Figure 1, adapted from SQL Server 2005 Books Online (BOL), shows the process that SQL Server uses to check for applicable plan guides. Here are the key steps in the process:

1. For a specific statement within the batch, SQL Server tries to match the statement to an SQL plan guide, whose @module_or_batch argument matches that of the incoming batch text, including any constant literal values, and whose @stmt argument also matches the statement in the batch. If this type of plan guide exists and the match succeeds, SQL Server modifies the statement text to include the query hints specified in the plan guide, then compiles the statement using the specified hints.
2. If SQL Server doesn't match a plan guide to the statement in step 1, SQL Server tries to parameterize the statement by using forced parameterization. In this step, parameterization can fail for any of these reasons:
   a. The statement is already parameterized or contains local variables.
   b. The PARAMETERIZATION SIMPLE database SET option is applied (the default setting), and there's no plan guide of type TEMPLATE that applies to the statement and specifies the PARAMETERIZATION FORCED query hint.
   c. A plan guide of type TEMPLATE exists that applies to the statement and specifies the PARAMETERIZATION SIMPLE query hint.
3. If forced parameterization succeeds in step 2, SQL Server tries to match the parameterized form of the query with an SQL plan guide. If the match succeeds, SQL Server modifies the statement text to include the query hints specified in the plan guide, then compiles the statement using the specified hints.

Working with Plan Guides

To give you a feel for combining TEMPLATE and SQL plan guides, let's look at an example that involves the distribution of data in the SpecialOfferID column in the Sales.SalesOrderDetail table in the AdventureWorks database. There are 12 different SpecialOfferID values, and most of them only occur a few hundred times at most, out of the 121,317 rows in Sales.SalesOrderDetail, as the script in Listing 1 and output in Table 1, show.

You can use the T-SQL sp_spaceused procedure to determine that there are about 1,230 pages in the Sales.SalesOrderDetail table. As you should know, using a nonclustered index to access data pages is a good choice only when a small percentage of rows in the table needs to be accessed. In general, the optimizer will choose the nonclustered index when the number of rows to be returned is less than about one-third of the number of pages. It's far more expensive to repeatedly seek through a nonclustered index for hundreds of randomly located rows than to do an orderly table scan or a clustered index scan. For the data in the Sales.SalesOrderDetail, only the rows with a SpecialOfferID value of 1, 2, 3, and 13 will not use a nonclustered index on SpecialOfferID. You can verify this for yourself by running the code in Listing 2, which creates a nonclustered index on Sales.SalesOrderDetail and runs a couple of similar queries, with different SpecialOfferID values.

Let's assume that you know there are very few queries that actually search for a SpecialOfferID value of 1, 2, 3, or 13, and 99 percent of the time the queries are looking for the less popular values. You'd like the SQL Server optimizer to autoparameterize queries that access the Sales.SalesOrderDetail table, specifying one particular value for SpecialOfferID. So let's create a TEMPLATE plan guide to autoparameterize queries of this form:

SELECT * FROM
  Sales.SalesOrderDetail 
  WHERE SpecialOfferID = 5;

(Note that some code in this article wraps to multiple lines because of space constraints in print.) However, you want to make sure the initial parameter that determines the cache plan isn't one of the values that might use a clustered index scan, namely the values 1, 2, 3, or 13. Thus, you can take the autoparameterized query that was produced by the sp_get_query_template procedure and use it to first create a TEMPLATE plan guide, then create an SQL plan guide with the OPTIMIZE FOR hint. The hint forces SQL Server to assume a specific value of 4 every time the query needs to be reoptimized. Listing 3 shows the entire batch to call sp_get_plan_template and create the two plan guides. Note that because the same variables for the query statement itself and the parameters list are used when creating the template and creating the plan guides, all of Listing 3 has to be executed as a single batch.

You can verify that the plan is being autoparameterized and optimized for a value that uses a nonclustered index on SpecialOfferID by running a few tests. The code in Listing 4 clears all existing plans from the plan cache, sets the STATISTICS IO and STATISTICS XML options on, then runs our query three times with different parameter values. In the STATISTICS IO output, note that each execution uses a different number of reads because SQL Server is finding a different number of rows through the nonclustered index. You can also verify that SQL Server is using the prepared plan by examining the STATISTICS XML output. If you look at the XML plan document for the query in Listing 4, you should have a node near the bottom of your XML document much like the one in Figure 1.

Keeping Track of Your Plan Guides

The metadata view that contains information about plan guides in a particular database is sys.plan_guides. This view contains all the information supplied in the sp_create_plan_guide procedure, plus additional information such as the creation date and last-modification date of each plan guide. Using the information in this view, you can manually reconstruct the plan guide definition, if necessary. However, there's no built-in mechanism to automatically script the plan guide definitions, as you can do with most other SQL Server objects, so it's strongly recommended that you save your actual sp_create_plan_guide scripts in case you ever need to move your definitions to a new server.

What's Your Plan?

Keep in mind that plan guides aren't intended to speed up query-compilation time. Not only does SQL Server first have to determine whether a plan guide exists that could be a potential match for the query being compiled, but the plan enforced by the plan guide must be one that the optimizer would have come up with on its own. To determine that the forced plan is valid, the optimizer has to go through most of the optimization process. Plan guides are intended to reduce execution time for those queries in which the SQL Server optimizer doesn't produce the best plan on its own.

The benefit of plan guides is twofold. First, if you suspect that a specific hint might make a query run more efficiently, you don't need to change the actual application code. You can create a plan guide to tell SQL Server what hints to use, then test the query with the plan guide in place. If the plan guide improves performance, you might at that time choose to add the hints to the actual code. However, keeping the hints as part of plan guides means that at any time in the future, if you decide the hints you've used are no longer optimal, you can remove the hints merely by dropping or disabling the plan guides. Second, plan guides can be a wonderful solution to query performance problems in situations when you can't modify the source code to add hints on your own. This can happen if you're using a third-party application in which the source code isn't available to you, or when your support agreements would no longer be valid if you made any changes directly to the code.