October 5, 2011 at 6:32 am
Hello to all,
I'm running in trouble to specify a plan guide for a query issued with sp_executesql.
the problem is getting the template from sp_get_query_template. By pasting (and adjusting the string in order to escape the string value) I just get:
Msg 10524, Level 16, State 1, Procedure sp_get_query_template, Line 1
Cannot parameterize @querytext.
the query is somewhat weird since it has some parameter specified on the parameter list and other setted directly in the statemet
I've tried by removing the sp_executesql and by substituting the parameters that follow the query (example):
sp_executesql N' select foo form tblForr where fldfoo=@pfoo',@pfoo='foo'
to
select foo form tblForr where fldfoo='foo'
the template gets created and also the plan_guide, but it cannot get applied on the query.
the server version is 2005 9.00.5000.00.
By searching in BOL i do not see that creating a template from sp_executesql is impossible.
Do you know if there are some limitations?
many thanks
andrea dal farra
October 5, 2011 at 7:42 am
From what you're saying, it's dynamic. You won't be able to create a plan guide for it. Modify the text and put in hints directly or take away the dynamic nature of the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2011 at 8:10 am
Thanks for the answer.
Unfortunately the product that raise the query is proprietary: the only option left to us would be change a constraint in a covered index, but I think we'll surrender
thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply