SQL Server - Force parameterization for query plan

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!

Script source: https://docs.microsoft.com/en-us/sql/relational-databases/performance/specify-query-parameterization-behavior-by-using-plan-guides?view=sql-server-2017

-- 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)';  

Results

Script results screen:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *