November 22, 2016 at 11:23 am
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
November 22, 2016 at 7:08 pm
jean_chr_thiel (11/22/2016)
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.
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
I parsed the last one (quoted here) and it doesn't return any errors. I also pulled the parameter list and created plain query and it also parses clean. What error do you get on the last one?
I've compared it to a recent plan guide I created and except that my hint is different the rest looks correct.
Stupid question maybe, but are you creating it in the correct database?
Leo
Nothing in life is ever so complex that with a bit of work we can't make it more complex.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 23, 2016 at 12:49 am
This is the error message I receive :
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 the 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 the plan guide is then not recognized neither used.
November 23, 2016 at 12:40 pm
OK, so the 2nd statement does create the Plan Guide. This makes sense because the create statement is correct.
Have you checked that your @stmt is identical to the incoming query? The two must match 100% otherwise you don't get a match. No extra spaces, returns, etc.
Also I've not used TABLE HINTS much, but have you tried qualifying the table name in the hint? I see it's not owned by DBO.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 23, 2016 at 12:59 pm
Yes, the statement is strictly a copy/paste of original one; i tried several other one, adding blanks at the end, etc.., but no change.
This is not linked to being recognized and getting a match since the problem is it does not even get created....
You are right, the hint statement is missing the schema name MVXJDTA but no change, error is in the @stmt line and the parameters in front of it.
November 23, 2016 at 1:19 pm
Your 2nd statement has the correct format. You have to have the parameter list separate from the T-SQL Query. I have compared this to one I've created recently that I know works (different hint) and the format is correct.
So this should work
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 ( MVXJDTA.MITALO, INDEX( MITALO10) , NOLOCK, FORCESEEK ))';
I'd also remove the space in the name as SQL Identifiers aren't really supposed to have spaces in them, but my testing on 2014 didn't raise an error.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 23, 2016 at 1:24 pm
I've just noticed that in my version I sent there is an extra space after the last "DESC"
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 24, 2016 at 8:42 am
Still cannot get this to work, any other ideas ?
November 24, 2016 at 3:25 pm
I'd go this route.
Look for the current plan for this query using this code, run in the context of the relevant database.
USE Your_DB
go
DECLARE @Omit varchar(30)
SET @Omit = 'Ignore this result'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT st.text AS [SQL], cp.cacheobjtype, cp.objtype, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Plan usage], qp.query_plan, plan_handle
FROM sys.dm_exec_cached_plans cp with (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE DB_NAME(st.dbid) = DB_NAME(db_id())
and (st.text like '%(@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%')
and not CAST(qp.query_plan AS NVARCHAR(MAX))LIKE '%Ignore this result%'
-- for extra filtering if required
-- and st.text like '%unbilled%'
-- and st.text like '%56467%'
-- ORDER BY
-- cp.usecounts DESC
This will confirm that a plan exists and the code matches.
Then try create this plan guide. Note the option is RECOMPILE which causes SQL to drop the existing plan from cache:
USE Your_DB
go
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 ( RECOMPILE )'
Now run query 1 and check if the plan has been dropped from the cache. If it has, then the above plan guide was created correctly and is working.
You can now run this to drop the above guide and recreate the correct one:
USE Your_DB
go
sp_control_plan_guide @operation = N'DROP' , @name = N'MITALO_Jct'
go
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 ))';
If the first plan guide didn't work, then the code isn't matching the in coming query correctly, or the plan guide isn't created in the context of the correct database. The only other option I can see is that the hint isn't valid for your version of SQL or isn't correctly formatted, but I know the top guide works on SQL 2014.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 25, 2016 at 4:50 am
Thank you for your detail explanation.
I finally found out why it was not working, I was not specifyig the correct shema, instead it was created in master data base and never used.
I am facing now another problem;
When creating the plan in the correct shema, i receive an error message :
USE [M3FDBPRF]
GO
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 (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 the problem is with the (NOLOCK) in @stmt.
The plan is created witout message if i change the @stmt by adding the WITH word : "@stmt = N'SELECT TOP 1 * FROM MVXJDTA.MITALO WITH (NOLOCK) '
But then of course the statement does not match any more the SQL send by the application and is never used.
November 25, 2016 at 4:56 am
jean_chr_thiel (11/25/2016)
I found out the problem is with the (NOLOCK) in @stmt.
It looks to me as if the problem is with the TABLE HINT clause in the @hints parameter. You appear to have specified an illegal hint.
John
November 25, 2016 at 12:28 pm
I don' see what is wrong with the 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.
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
November 28, 2016 at 2:32 am
The error message does state that the problem is in the TABLE HINT clause, so it's curious that changing the actual statement makes it go away. What is MVXJDTA.MITALO - is it a table-valued function? Using NOLOCK without WITH is deprecated, but until it's finally removed, I can't see why it should cause an error.
John
November 28, 2016 at 3:11 pm
MVXJDTA.MITALO is just a regular table, no table-valued function;
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply