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 (http://www.sqlmag.com, InstantDoc
ID 96634), 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:
- 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.
- 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.
- 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.