SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Troubleshooting non-working Plan Guide in SQL Server 2008 R2


Troubleshooting non-working Plan Guide in SQL Server 2008 R2

Author
Message
Johan.Jansson
Johan.Jansson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 14
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)))'

Andrew G
Andrew G
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9527 Visits: 2372
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search