Post reply

Troubleshooting non-working Plan Guide in SQL Server 2008 R2

  • October 18, 2017 at 7:33 am

    #329260

    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)))'

  • November 1, 2017 at 2:19 am

    #1966122

    Try get the exact SQL text from the cached plan instead of profiler, I've found profiler is not the best way as you can miss auto parameterized queries.

    SELECT TEXT,*
    FROM sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    WHERE TEXT LIKE '%FROM TABLE A WHERE ((COLUMN20VALUE= %'
    -- Comment out below lines if no result
    AND cacheobjtype = 'Compiled Plan'
    AND objtype = 'Prepared'

    Retry the profiler trace with "Plan guide successful" & "Plan guide unsuccessful" ticked

Viewing 2 posts - 1 through 1 (of 1 total)

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