SQL Guide Plan not being used?

2 votes

2 answers

1723 views

sql-server sql-server-2016 plan-guides

                          I'm try to get force Parameterization on a simple adhoc SQL query.As explained in this article https://www.simple-talk.com/sql/performance/fixing-cache-bloat-problems-with-guide-plans-and-forced-parameterization/ 

But even trying to do this with the simplest query I cant get it to work

    CREATE TABLE fruit
    (
     id BIGINT PRIMARY KEY(id)
    ,title VARCHAR(150)
    )
    INSERT INTO fruit VALUES ( 1, 'Apple') , ( 2, 'Banana'), ( 3, 'Orange'), ( 4, 'Pear')
    
    DECLARE @params nvarchar(max);
    DECLARE @stmt nvarchar(max);
    EXEC sp_get_query_template N'SELECT title FROM fruit WHERE id = 4',@stmt OUTPUT, @params OUTPUT;
    
    --SELECT @params
    EXEC sp_create_plan_guide 
    	N'fruitGuide', 
    	@stmt, 
    	N'TEMPLATE', 
    	NULL, 
    	@params, 
    	N'OPTION(PARAMETERIZATION FORCED)';
    GO
    
    SELECT title FROM fruit WHERE id = 1

Plan XML:

Shows a compile and doesn't use the plan guide, is there something i'm missing here? Where am i going wrong?

Asked by davey (679 rep)

Nov 23, 2016, 09:32 PM
Last activity: Dec 8, 2016, 06:24 PM

SQL Guide Plan not being used?

Related Questions