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:
Obtain the parameterized form of the query by executing sp_get_query_template.
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.
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.
See Also