Designing Plan Guides for Parameterized Queries

You can create a plan guide on a query that is parameterized. A query can be parameterized for any one of the following reasons:

• The query is submitted by using sp_executesql.

• Forced parameterization is enabled in the database. This parameterizes all eligible queries.

• A separate plan guide has been created on a class of queries to which this query belongs, specifying that they be parameterized.

When you create a plan guide on a parameterized query, you are essentially creating a plan guide for all queries that parameterize to the same form, but differ only in their constant literal values. For example, in a database on which forced parameterization is enabled, the following two queries parameterize to the same form:

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 50;

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 100;

To create a plan guide on parameterized queries, create a plan guide of type SQL and specify the parameterized form of the query in the sp_create_plan_guide stored procedure.

For example, to obtain the parameterized form of one of the queries in the previous example and create a plan guide on it to force the optimizer to use a hash join, follow these steps:

1. Obtain the parameterized form of the query by executing sp_get_query_template.

2. If the query is not already being parameterized by SQL Server by using sp_executesql or the PARAMETERIZATION FORCED database SET option, create a plan guide of type TEMPLATE to force parameterization.

3. Create a plan guide of type SQL on the parameterized query.

The following batch executes all three of these steps:

--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
    FROM Production.ProductModel pm 
    INNER JOIN Production.ProductInventory pi 
        ON pm.ProductModelID = pi.ProductID 
    WHERE pi.ProductID = 101 
    GROUP BY pi.ProductID, pi.Quantity 
    HAVING SUM(pi.Quantity) > 50',
@stmt OUTPUT, 
@params OUTPUT;
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(HASH JOIN)';

The plan guide will now apply to all queries that parameterize to the form specified, but contain different constant literal values.