Creating a plan guide of type TEMPLATE for the parameterized form of a query
Definition Description
/*The following example creates a plan guide that matches any query that parameterizes to a specified form, and directs SQL Server to force parameterization of the query. The following two queries are syntactically equivalent, but differ only in their constant literal values. */ SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesOrderID = 45639;
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesOrderID = 45640;
Here IS the PLAN guide ON the parameterized form OF the query: EXEC sp_create_plan_guide @name = N'TemplateGuide1', @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesOrderID = @0', @type = N'TEMPLATE', @module_or_batch = NULL, @params = N'@0 int', @hints = N'OPTION(PARAMETERIZATION FORCED)';
IN the previous example, the value FOR the @stmt PARAMETER IS the parameterized form OF the query. The ONLY reliable way TO obtain this value FOR USE IN sp_create_plan_guide IS TO USE the sp_get_query_template SYSTEM stored procedure. The FOLLOWING script can be used BOTH TO obtain the parameterized query AND THEN CREATE a PLAN guide ON it. DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.SalesOrderID = 45639;', @stmt OUTPUT, @params OUTPUT EXEC sp_create_plan_guide N'TemplateGuide1', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)'; /* ms179880.note(en-US,SQL.90).gifImportant: The value of the constant literals in the @stmt parameter passed to sp_get_query_template might affect the data type that is chosen for the parameter that replaces the literal. This will affect plan guide matching. You may have to create more than one plan guide to handle different parameter value ranges. For more information about how to obtain the parameterized form of a query to use in a TEMPLATE-based plan guide, see Designing Plan Guides for Parameterized Queries. */
