June 25, 2009 at 7:09 am
I posted this under SQL 2005 because I did not see any forums for SQL 2008.
After researching the fixes in SQL 2008 SP1 the fix described in http://support.microsoft.com/kb/968693 says that using option(recompile) may return wrong results in the RTM version. So my concern is that I am using this feature for search stored procedures that have many optional parameters. I am using dynamic sql to build the sql statement based on which parameters are passed in and adding option(recompile) to the end of the statement. The article is vague but says wrong results may be returned?
So my question is in SQL 2008 RTM should OPTION(RECOMPILE) not be used? Is a workaround to use WITH RECOMPILE in the stored proc or will that have wrong results also? I realize it is essentially the same thing just not at the statement level but not sure if the bug only occurs specifically with OPTION(RECOMPILE).
This topic is also briefly described here.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=452925
I will have to do some testing and examine the execution plans but just curious if anyone else has seen this.
June 26, 2009 at 5:34 am
This is the Connect item I remember: http://connectbeta.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=386810
It includes full details and a repro.
Any particular reason why you would not apply Service Pack 1?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 26, 2009 at 6:42 am
Thanks for the response and the link. I will apply SP1 but was curious if using WITH RECOMPILE behaves the same. Looks like applying SP1 will fix the possibility of incorrect results but may result in parameter sniffing and poor performance. At any rate that is the lesser of two evils and if performance suffers I can try the option (optimize for....) hint. Or worst case, another option is to have separate sprocs for each search parameter combination (tedious but would cache a distinct plan for each combination of parameters). It is only for a few sprocs that perform searches against very large tables. Thanks again.
June 26, 2009 at 6:55 am
I really wouldn't lose too much sleep over this - the original problem was fairly serious in principle, but the chances of hitting it on a typical production system were relatively small. SP1 simply reverts the behaviour to 2005-style, which works just fine.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply