1

I have problems with a Plan Guide I have created in Microsoft SQL Server 2008 R2. The Plan guide is purposed for a performance issue in Microsoft Dynamics AX 2009 and forces a specific question to use a TABLE HINT index. However, when I perform a SQL Profiler Trace, the execution plan still uses two other indexes (one of them is a clustered). The Plan Guide itself works only when the parameters @p1 and @p2 are specified in the question, not for random parameter values. I suspect the problem can be related to how variables are sent from Dynamics AX. I have double checked that the SELECT statement is exactly the same as the one found in Profiler. Do you have any advice how to troubleshoot this problem? In my world, it should really work.

T-SQL USED FOR CREATING NON WORKING PLAN GUIDE

EXEC sp_create_plan_guide   
    @name = N'PLAN_GUIDE',
    @stmt = N'SELECT TOP 1 A.COLUMN01,...............A.COLUMN35 FROM TABLE A WHERE ((COLUMN20VALUE=@P1) AND (COLUMN25VALUE=@P2))',
    @type = N'SQL',  
    @module_or_batch = NULL,  
    @params = N'@P1 nvarchar(4),@P2 nvarchar(20)',
    @hints = N'OPTION (TABLE HINT (A, INDEX(I_708COLUMN20)))'

T-SQL FOR CREATING PLAN GUIDE (THAT WORKS) WITH PARAMETER VALUES IN THE SELECT QUERY (@params commented out)

EXEC sp_create_plan_guide   
    @name = N'',
    @stmt = N'SELECT TOP 1 A.COLUMN01,...............A.COLUMN35 FROM TABLE A WHERE ((COLUMN20VALUE='abc') AND (COLUMN25VALUE='L3355'))',
    @type = N'SQL',  
    @module_or_batch = NULL,  
    --@params = N'@P1 nvarchar(4),@P2 nvarchar(20)',
    @hints = N'OPTION (TABLE HINT (A, INDEX(I_708COLUMN20)))'
  • Did somebody change the query in ax to include literals instead of parameters? Can you try to capture the cursor creation using profiler or extended events? The fact that it works when you use parameter values instead of parameters makes me think the actual sql statement also includes the values instead of @p1 etc – Tom V Oct 18 '17 at 18:12
  • 1
    Also are you sure the code you say that is working actually is working? You seem to have a single quote issue there – Tom V Oct 18 '17 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.