Post reply

create plan guide

  • 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

  • September 2, 2019 at 2:24 pm

    #3676356

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 2, 2019 at 3:00 pm

    #3676366

    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

  • September 3, 2019 at 5:43 am

    #3676783

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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