February 16, 2009 at 1:58 am
Yeah I did think of that but presumably if I'm happy with the parameters I optimize for will give me a good(ish) plan every time then it is better to do this than have the cost of actually compiling the plan each time. Although I don't know actually know how much a compilation will cost on a plan that small, I've always tried to avoid the recompile option unless I know that its the only way to get round some sort of optional parameter problem or something.
February 16, 2009 at 5:57 am
Ken Gaul (2/16/2009)
Yeah I did think of that but presumably if I'm happy with the parameters I optimize for will give me a good(ish) plan every time then it is better to do this than have the cost of actually compiling the plan each time. Although I don't know actually know how much a compilation will cost on a plan that small, I've always tried to avoid the recompile option unless I know that its the only way to get round some sort of optional parameter problem or something.
You can get the compile times by turning on STATISTICS TIME when you run the query. It'll show exactly how much you're paying for the compile and it might be worth it. I'd test both ways to see what the gains & costs are for each approach. Be sure to use a bad parameter value with the OPTIMIZE FOR to see what happens there.
"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
February 17, 2009 at 2:09 pm
one way to pass hints to a view is with a plan guide - certainly you can pass a maxdop for i9nstance - if you can extract the query from cache then you should be able to create a plan guide - whether you can pass one index hint i have no idea - never tried that. Have used plan guides though with some success ( with a poor app that generated sql on the fly from within it - before you all ask )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 17, 2009 at 2:22 pm
colin Leversuch-Roberts (2/17/2009)
whether you can pass one index hint i have no idea - never tried that.
You can't. Plan guides are limited to the hints that appear in the OPTION clause, which index hints do not.
The only way to force an index is to use the WITH PLAN hint, which is a little extreme.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 5:42 am
GilaMonster (2/17/2009)
colin Leversuch-Roberts (2/17/2009)
whether you can pass one index hint i have no idea - never tried that.You can't. Plan guides are limited to the hints that appear in the OPTION clause, which index hints do not.
The only way to force an index is to use the WITH PLAN hint, which is a little extreme.
And more than a little difficult to implement and maintain.
"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
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply