Can't get sp_create_plan_guide to work

• jean_chr_thiel

  SSC-Addicted

  Points: 473

  More actions

  November 22, 2016 at 7:14 am

  #315186

  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 :

  (@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

  Sorry wrong forum, should be in SQL Server 2012, reposted ..