locked
Can't get sp_create_plan_guide to work RRS feed

  • Question

  • I have the following SQL generated by a third party application that is using a wrong index and create undue parallelisation.
    I tried to create a plan guide without success.

    Here is a the query text retrieved from the plan cache:

    (@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000))SELECT TOP 1 * FROM MVXJDTA.MITALO (NOLOCK)  WHERE MQCONO= @P0  AND MQTTYP= @P1  AND MQRIDN= @P2  AND MQRIDO= @P3  AND MQRIDL= @P4  AND MQRIDX= @P5  AND MQRIDI= @P6  AND (MQWHSL= @P7  AND MQBANO= @P8  AND MQCAMU= @P9  AND MQPLSX= @P10  AND MQWHLO= @P11  AND MQITNO< @P12 ) ORDER BY MQCONO DESC ,MQTTYP DESC ,MQRIDN DESC ,MQRIDO DESC ,MQRIDL DESC ,MQRIDX DESC ,MQRIDI DESC ,MQWHSL DESC ,MQBANO DESC ,MQCAMU DESC ,MQPLSX DESC ,MQWHLO DESC ,MQITNO DESC ,MQSOFT DESC

    It does not accept : (error found in @stmt )

    sp_create_plan_guide
    @name = N'MITALO Jct',
    @stmt = N'(@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000))SELECT TOP 1 * FROM MVXJDTA.MITALO (NOLOCK)  WHERE MQCONO= @P0  AND MQTTYP= @P1  AND MQRIDN= @P2  AND MQRIDO= @P3  AND MQRIDL= @P4  AND MQRIDX= @P5  AND MQRIDI= @P6  AND (MQWHSL= @P7  AND MQBANO= @P8  AND MQCAMU= @P9  AND MQPLSX= @P10  AND MQWHLO= @P11  AND MQITNO< @P12 ) ORDER BY MQCONO DESC ,MQTTYP DESC ,MQRIDN DESC ,MQRIDO DESC ,MQRIDL DESC ,MQRIDX DESC ,MQRIDI DESC ,MQWHSL DESC ,MQBANO DESC ,MQCAMU DESC ,MQPLSX DESC ,MQWHLO DESC ,MQITNO DESC ,MQSOFT DESC '
    , @type = N'SQL'
    , @module_or_batch = NULL
    , @params = N'@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000)'
    ,@hints = N'OPTION (TABLE HINT ( MITALO, INDEX( MITALO10) , NOLOCK, FORCESEEK ))';


    It does not work with :
    sp_create_plan_guide
    @name = N'MITALO Jct',
    @stmt = N'SELECT TOP 1 * FROM MVXJDTA.MITALO (NOLOCK)  WHERE MQCONO= @P0  AND MQTTYP= @P1  AND MQRIDN= @P2  AND MQRIDO= @P3  AND MQRIDL= @P4  AND MQRIDX= @P5  AND MQRIDI= @P6  AND (MQWHSL= @P7  AND MQBANO= @P8  AND MQCAMU= @P9  AND MQPLSX= @P10  AND MQWHLO= @P11  AND MQITNO< @P12 ) ORDER BY MQCONO DESC ,MQTTYP DESC ,MQRIDN DESC ,MQRIDO DESC ,MQRIDL DESC ,MQRIDX DESC ,MQRIDI DESC ,MQWHSL DESC ,MQBANO DESC ,MQCAMU DESC ,MQPLSX DESC ,MQWHLO DESC ,MQITNO DESC ,MQSOFT DESC '
    , @type = N'SQL'
    , @module_or_batch = NULL
    , @params = N'@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000)'
    ,@hints = N'OPTION (TABLE HINT ( MITALO, INDEX( MITALO10) , NOLOCK, FORCESEEK ))';


    Thanks for any hint for this to work

    Tuesday, November 22, 2016 6:34 PM

All replies

  • I believe that this

    ,@hints = N'OPTION (TABLE HINT ( MITALO, INDEX( MITALO10) , NOLOCK, FORCESEEK ))';

    should be

    ,@hints = N'OPTION (TABLE HINT ( MVXJDTA.MITALO, INDEX( MITALO10) , NOLOCK, FORCESEEK ))';

    That is, you must specify the schema as well.
    More precisely, Books Online says this about this argument:

    /exposed_object_name can be one of the following references:

    *  When an alias is used for the table or view in the FROM clause of the query, exposed_object_name is the alias.

    *  When an alias is not used, exposed_object_name is the exact match of the table or view referenced in the FROM clause. For example, if the table or view is referenced using a two-part name, exposed_object_name is the same two-part name./

    Tuesday, November 22, 2016 10:51 PM
  • Yes you are right, there was a syntax in the hint, I corrected it.

    but the problem is still there.

    This is the error message I receive when creating the first  sp_create_plan_guide, where the @stmt match exactly the query sent by application :
    Msg 102, Niveau 15, État 1, Ligne 1
    Incorrect syntax near @p0.
    Msg 10516, Niveau 16, État 1, Procédure sp_create_plan_guide, Ligne 20
    Cannot create plan guide 'MITALO Jct' because @module_or_batch can not be compiled.

    It refers to the line @stmt = N'(@P0 int,@P1 int,@.....

    It accepts to create the plan guide if I remove the first part : (@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 decimal(38,0),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000))

    This is done in second statement , but then the plan guide is not recognized neither used

    Wednesday, November 23, 2016 7:54 AM
  • Setting up plan guides is not trivial, since you need to match the statement in the cache exactly. But you should not include the parameter list.

    I cannot see what is wrong, but here is a working example that runs in the old demo database Northwind:

    DBCC FREEPROCCACHE
    go
    EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
                       N'@orderdate datetime', @orderdate = '19960101'
    go
    EXEC sp_create_plan_guide
         @name = N'MyGuide',
         @stmt = N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
         @type = N'SQL',
         @module_or_batch = NULL,
         @params = N'@orderdate datetime',
         @hints =  N'OPTION (TABLE HINT (dbo.Orders , INDEX (OrderDate)))'
    go
    EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
                       N'@orderdate datetime', @orderdate = '19980101'
    go
    EXEC sp_control_plan_guide N'DROP', N'MyGuide'

    Wednesday, November 23, 2016 10:48 PM
  • I cannot get this to work, any other suggestions ?

    Thursday, November 24, 2016 3:40 PM
  • What couldn't you get to work The script that I posted, or your own case?

    If my script did not work in your testing, I'm surprised - I did test
    it when I ran it.

    If you were not able to make your own script to work, I would first recommend that you start over, and check sys.plan_guides and make sure that all plan guides are dropped (unless you had plan guides for other statements in play since before). Then I would recommend you compose a script like mine that uses sp_executesql, so that you can see that you get it right.

    As I said, getting a plan guide to work can be quite difficult, because it has to match the statement exactly.

    Thursday, November 24, 2016 10:49 PM
  • I know this schema - Lawson - m3.
    Friday, November 25, 2016 12:03 AM
  • I still get same error message with hint :
    @hints = N'OPTION (TABLE HINT ( MVXJDTA.MITALO, INDEX( MITALO10) , NOLOCK ))

    Msg 8724, Niveau 16, État 1, Ligne 1
    Cannot execute query. Table-valued or OPENROWSET function 'MVXJDTA.MITALO' cannot be specified in the TABLE HINT clause.

    I found out that it works if I change statement

    from 
    , @stmt = N'SELECT TOP 1 * FROM MVXJDTA.MITALO (NOLOCK)  WHERE ...

    to 
    , @stmt = N'SELECT TOP 1 * FROM MVXJDTA.MITALO WITH (NOLOCK)  WHERE ...

    but then plan is not recognized anymore

    Any way to make this work ?

    Friday, November 25, 2016 8:31 PM
  • Dunno.  I only have done a plan guide once, and it has to match right down to the carriage returns.

    Josh

    Friday, November 25, 2016 8:44 PM
  • So that's one more reason you should not use NOLOCK. (OK, so I assume that you did not write this query, but NOLOCK is heavily abused by programmers who think that is more important that a query runs quickly than it produces a correct result.)

    Obviously, adding only WITH in the plan guide will not cut it - the text must match the actually query down to every space. I find, however, if make my example this way:

    DBCC FREEPROCCACHE
    go
    EXEC sp_executesql N'SELECT * FROM dbo.Orders WITH (NOLOCK) WHERE OrderDate > @orderdate',
                       N'@orderdate datetime', @orderdate = '19960101'
    go
    EXEC sp_create_plan_guide
         @name = N'MyGuide',
         @stmt = N'SELECT * FROM dbo.Orders WITH (NOLOCK) WHERE OrderDate > @orderdate',
         @type = N'SQL',
         @module_or_batch = NULL,
         @params = N'@orderdate datetime',
         @hints =  N'OPTION (TABLE HINT (dbo.Orders, NOLOCK, INDEX (OrderDate)))'
    go
    EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
                       N'@orderdate datetime', @orderdate = '19980101'
    go
    EXEC sp_control_plan_guide N'DROP', N'MyGuide'

    The table hint is not respected. (The plan guide is obviously recognised, becase I get an error message, if I don't include NOLOCK in the plan-guide text.

    Friday, November 25, 2016 9:37 PM
  • If you look at the (estimated?) plan for a statement in XML, somewhere it says whether it used a plan guide, and I think there may be a flag somewhere in the plan cache DMV's as well.

    Josh

    Friday, November 25, 2016 11:32 PM
  • Indeed I cannot change the query that is sent by a third party application.

    I can see in the execution plan if the plan guide is used or not by looking at the Execution Plan, right click on select clause, => parameter window show the plan guide if used, which is not the case.

    So back to my question, knowing that the query and @stmt parameter cannot be changed:

     @stmt = N'SELECT TOP 1 * FROM MVXJDTA.MITALO (NOLOCK)  WHERE ...

    The @hints parameter is not accepted by  sp_create_plan_guide because of the "nolock" option and returns Msg 8724 level 16. 

    It only accepts the hint "@hints = N'OPTION (TABLE HINT ( MVXJDTA.MITALO, INDEX( MITALO10) , NOLOCK ))"

    when @stmt is changed from "(nolock)" to "with (nolock)"; at this point however, the plan guide is never choosen since the query do not match anymore the @stmt. 

    Any ideas ?

    Saturday, November 26, 2016 10:07 AM
  • And if you would able to change the original query to use WITH (NOLOCK), the hint would still be ignored.

    So in summary: you lose this time.

    But at least you have learnt one more reason why NOLOCK is bad, (And so have I.)

    Saturday, November 26, 2016 11:11 PM