SQL Server Home
Concurrent use of OPTION (RECOMPILE) causes incorrect results
12/5/2008 4:08:05 PM
User(s) can reproduce this bug
[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)
SQL Server 2008 - Developer Edition
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
Note: This repro requires the Northwind sample database.
Step 1: In Northwind, create this stored procedure:
create proc P (
select @i, @oid, OrderID, CustomerID as cn
where @oid < OrderID
Step 2: Paste copies of the following batch into two SSMS editor windows. Execute the batch in one window, then about a second later in the second window.
SET NOCOUNT ON
create table #T1 (
create table #T2 (
declare @i int
select @i = 1000;
declare @f float
select @f = RAND(0);
while @i > 0 begin
declare @oid int;
set @oid = 10248+(select BINARY_CHECKSUM(RAND())%830);
set @i = @i - 1;
insert into #T1 exec P @i, @oid
insert into #T2 exec P @i, @oid;
select coalesce(t1.iter, t2.iter), t1.cnt, t2.cnt
from (select iter, count(*) as cnt from #T1 group by iter) AS t1
full join (select iter, count(*) as cnt from #T2 group by iter) AS t2
on t1.iter = t2.iter
where coalesce(t1.cnt, 0) <> coalesce(t2.cnt, 0)
order by 1
drop table #T1, #T2;
Any result rows indicate that P yielded incorrect results. For example, these were the results once:
---- ----- ----
455 1660 830
479 NULL 830
630 712 356
656 NULL 830
872 1660 830
900 NULL 619
An empty result set, indicating that P never produced two different results for the same parameters.
to post a comment.
Please enter a comment.
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.
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)).
on 2/11/2009 at 11:22 PM
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.
Senior Program Manager
SQL Core RDBMS Team
on 2/9/2009 at 1:42 PM
Thanks for the update. This bug report is now marked "Resolved (Fixed)." Can you elaborate on that? Is a hotfix available?
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.
SQL Engine Development
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
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
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"
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
to post a workaround.
Please enter a workaround.
© 2014 Microsoft