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