Let your SQL Server know what query to parameterize if beneficial. When the PARAMETERIZATION database option is set to SIMPLE, the SQL Server query optimizer may choose to parameterize the queries. This means that any literal values that are contained in a query are substituted with parameters. This process is referred to as simple parameterization. When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not. However, you can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database option to FORCED.
You can overwrite behavior given by database setting using this script. This is also feasible from SSMS but some of you prefer scripting it out and this is the way.
Created plan guides can be found in programmability section of actual database
WARNING: It always depends on your workload and data distribution if parameterization can bring performance boost, it can on the other hand bring more troubles as well. So test properly before pushing to production environment!
-- query (check execution plan before) SELECT pi.ProductID, SUM(pi.Quantity) AS Total FROM Production.ProductModel AS pm INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID WHERE pi.ProductID = 2 GROUP BY pi.ProductID, pi.Quantity HAVING SUM(pi.Quantity) > 5 -- lets create guide for it execution plan 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 AS pm INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID WHERE pi.ProductID = 2 GROUP BY pi.ProductID, pi.Quantity HAVING SUM(pi.Quantity) > 5', @stmt OUTPUT, @params OUTPUT; EXEC sp_create_plan_guide N'TemplateGuide1', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)';
Script results screen:
Script messages after execution:
Commands completed successfully. or Msg 10529, Level 16, State 1, Procedure sp_create_plan_guide, Line 20 [Batch Start Line 11] Cannot create plan guide 'TemplateGuide1' because there is already a planguide 'TemplateGuide1' of @type 'template' on @stmt.