March 1, 2013 at 3:04 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy