Export (0) Print
Expand All

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.

Community Additions

ADD
Show:
© 2014 Microsoft