create plan guide

Question

create plan guide

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797
More actions
September 2, 2019 at 2:06 pm

#3676355
Hi
I'm fairly new to using plan guides (I normally focus on getting rid of cursors and putting the correct indexes in first)
I've got some very stupid queries coming from either an entity framework app or an office application.
I'm getting hundreds of thousands of queries that look like this
SELECT * FROM (SELECT * FROM stock) AS DRVD_TBL WHERE (stock_code LIKE '%eqpf%')
I know the code is incredibly bad, but I can't get it changed... so I thought I could use a plan guide to at least reduce the number of compiles
```
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT * FROM (SELECT * FROM stock) AS DRVD_TBL WHERE (stock_code LIKE ''%eqpf%'');',  
    @stmt OUTPUT,   
    @params OUTPUT  
EXEC sp_create_plan_guide N'TemplateGuide2',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';
```
unfortunatly I get the error
Msg 10524, Level 16, State 1, Procedure sp_get_query_template, Line 1 [Batch Start Line 0]
Cannot parameterize @querytext.
does anyone have any clues? is it the like? or the select * from (select * from )?

MVDBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 1

Not one I've run into before, but I think it's the LIKE. I'm not sure why, but it must not be able to parse that type of command. I ran a combination of different choices on your code. Only the ones with LIKE in them failed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

Thanks Grant - that was my suspicion

is there any way to get this parameterised without shouting at 40 developers and the entire team at Microsoft that built MS Access?

MVDBA

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 3

I really don't know. I think getting the EF code changed is your best bet.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

Interesting... 5 years down the road and I have the same issue with Azure SQL. Any suggestion, please?

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT ProductId FROM dbo.Products',  
    @stmt OUTPUT,   
    @params OUTPUT;