Can't get Sp_create_plan_guide to execute successfully

  • Hi,

    I am not a DBA. We have a stored procedure used by our app that is using inefficient query plans. The sproc needs rebuilding and has been rebuilt for an up-coming hot-fix but we need an interim work-around (which cannot include altering the sproc). Re-indexing and updateStats works sporadically but not reliably.

    As a last resort I am attempting to use a Plan Guide to force the sproc to use a specific execution plan extracted from when the sproc was functioning well. If I alter the sproc and simply add the OPTION (USE PLAN N'<XML>') to the problematic statement, the sproc is altered successfully, the sproc runs efficiently and the application runs well again.

    However, if I use Sp_create_plan_guide, I keep getting the below error even with the exact same plan xml that works by altering the sproc:

    [font="Courier New"]Error detected at line 3: Incorrect syntax near the keyword 'ASC'

    [/font]

    I think it relates to this part of the plan (there are a few similar occurrences but this is the first)

    <DefinedValue>

    <ColumnReference Column="Expr1185" />

    <ScalarOperator ScalarString="CASE WHEN [@SortDirection]=N''ASC'' THEN [DataBaseName].[dbo].[Delegate].[DelegateIdentity] ELSE NULL END">

    <IF>

    <Condition>

    <ScalarOperator>

    <Compare CompareOp="EQ">

    <ScalarOperator>

    <Identifier>

    <ColumnReference Column="@SortDirection" />

    </Identifier>

    </ScalarOperator>

    <ScalarOperator>

    <Const ConstValue="N''ASC''" />

    </ScalarOperator>

    </Compare>

    </ScalarOperator>

    </Condition>

    <Then>

    <ScalarOperator>

    <Identifier>

    <ColumnReference Database="[DataBaseName]" Schema="[dbo]" Table="[Delegate]" Column="DelegateIdentity" />

    </Identifier>

    </ScalarOperator>

    </Then>

    <Else>

    <ScalarOperator>

    <Const ConstValue="NULL" />

    </ScalarOperator>

    </Else>

    </IF>

    </ScalarOperator>

    </DefinedValue>

    which relates to this part of the sproc

    ORDER BY

    CTE.LocationFullName ASC

    ,CASE

    WHEN

    @SortDirection= N''ASC''

    THEN

    CTE.DelegateIdentity

    END

    ASC

    ,CASE

    WHEN

    @SortDirection<> N''ASC''

    THEN

    CTE.DelegateIdentity

    END

    DESC

    However, if I go through and remove all the '' (2 single quotes) then it the error goes away but I get another error relating to another literal value in the script.

    If I change all occurrences of ''ASC'' to just ASC then the sproc Sp_create_plan_guide runs successfully and the plan guide is successfully mapped to the statement (as confirmed with profiler's PlanGuideSuccessful event). But, I am reluctant to use this in production because I have 'hacked' the xml plan which MS advise not to do.

    I am pretty sure the string I am passing to @hints is correct, that is, if I use PRINT with the same value it comes out as I would expect. However, I did exchange all single quotes for 2 x single quote in the plan xml that I retrieved from Profiler originally.

    Any advice or similar experiences? Let me know if you need the entire syntax I am using, it is quite large.

    Thanks in advance.

Viewing 0 posts

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