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
Concepts
Optimizing Queries in Deployed Applications by Using Plan Guides
Other Resources
Query Performance
sp_create_plan_guide (Transact-SQL)