Concurrent use of OPTION (RECOMPILE) causes incorrect results

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

Closed

as Fixed

Type:	Bug
ID:	386810
Opened:	12/5/2008 4:08:05 PM
Access Restriction:	Public
Duplicates:	420856

Workaround(s)

view

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)

Details (expand)

English

SQL Server 2008 - Developer Edition

SQL Engine

Windows XP SP2 Professional

US English

Note: This repro requires the Northwind sample database.

Step 1: In Northwind, create this stored procedure:

create proc P (
@i int,
@oid int
) as

select @i, @oid, OrderID, CustomerID as cn
from Orders
where @oid < OrderID
option (recompile);

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.

use Northwind
go
SET NOCOUNT ON
go
create table #T1 (
iter int,
i int,
j int,
v nvarchar(50)
);

create table #T2 (
iter int,
i int,
j int,
v nvarchar(50)
);

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;
end;

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
go
drop table #T1, #T2;
go

Any result rows indicate that P yielded incorrect results. For example, these were the results once:

cnt cnt
---- ----- ----
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.

0 attachments

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