Home Dashboard Directory Help
Search

Concurrent use of OPTION (RECOMPILE) causes incorrect results by Steve Kass


Status: 

Closed
 as Fixed Help for as Fixed


20
0
Sign in
to vote
Type: Bug
ID: 386810
Opened: 12/5/2008 4:08:05 PM
Access Restriction: Public
Duplicates: 420856
0
Workaround(s)
view
5
User(s) can reproduce this bug

Description

[Tony Rogerson initially reported this behavior. I created a repro, and Erland Sommarskog polished it into the form given here. The bug has been observed on various combinations of 32-bit and 64-bit editions, CU1 and CU2 builds, and Express and Developer editions.]

Description: Two sessions each make many calls to a procedure P with changing parameter values. The procedure P executes one query against static data, sometimes with OPTION (RECOMPILE) and sometimes without.

Occasionally P gives incorrect results (for the repro below, this typically happens about 1/2% - 1% of the time). When P's results are wrong, P returns either 0 or twice the expected number of rows.

The bug has not been observed without OPTION (RECOMPILE)
Details
Sign in to post a comment.
Posted by Razvan Socol on 11/16/2009 at 11:45 AM
Some even better good news: SP1CU5 contains a fix for this issues, without disabling the optimizations initially performed for OPTION RECOMPILE, see KB 976603.
Posted by yavora on 3/1/2009 at 12:16 PM
Some good news, CU4 will contain the fix described above (disabling plan-tailoring in the presence of OPTION(RECOMPILE)).

Thanks,
Yavor
Posted by Microsoft on 2/11/2009 at 11:22 PM

Dear Steve,

I want to thank you for bringing this important bug to the attention of the SQL Engine team.

The fix will be part of SQL 2008 SP1 and will resolve the "incorrect results" problem. It will do so by disabling the variable and parameter substitutions SQL Engine performs in the presence of the OPTION(RECOMPILE) hint. The plan will be compiled for unknown/sniffed parameter values, instead of the actual values at the time of execution. Please refer to this article for the potential pitfalls associated with the parameter sniffing: http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx.

In the future, we plan to restore the agressive plan tailoring, so please continue to use the OPTION(RECOMPILE) hint per our current best practices. We will know in a few weeks if the permanent fix will make it into SP2, or will only meet the bar for SQL 11. I will post it here once I have more information.

Sincerely,

Yavor Angelov
Senior Program Manager
SQL Core RDBMS Team
Posted by Steve Kass on 2/9/2009 at 1:42 PM
Hi Adam,

Thanks for the update. This bug report is now marked "Resolved (Fixed)." Can you elaborate on that? Is a hotfix available?

Steve
Posted by Microsoft on 1/30/2009 at 10:29 AM
We have discovered the root of the problem. We will try to get a fix in Katmai SP1. In the meantime, the only workaround is not to use option(recompile) if there is the possibilitly of concurrent compiles.

Thank you for narrowing it down for us - the repro script was invaluable.

-Adam
SQL Engine Development
Posted by Steven Wilmot on 1/26/2009 at 6:04 AM
To confirm, this problem still exists in CU3

Although, that is hardly surprising, given that there was no "resollution" listed in the hotfix list that CU3 contained
Posted by Microsoft on 12/9/2008 at 2:57 PM
Thanks for the feedback! I'll make sure one of our developers takes a look at this. -Eric
Posted by Steven Wilmot on 12/8/2008 at 3:10 PM
Ignore my comment - I had not read the "Paste copies of the following batch into two SSMS editor windows"
Posted by Steven Wilmot on 12/8/2008 at 9:30 AM
Interestingly enough, having tried this on two different machines, I am unable to cause it to fail.

If anyone feels like dropping me an email ( steven at data hyphen utilities dot co dot uk ) , I'd be happy to try some alternatives
Sign in to post a workaround.