Temp Table Recompilation Activity in One Session Can Affect Another Session
Query recompilation related to temp table data changes has long been thought limited to individual session scope. In another word, different sessions may populate their temp tables very differently and we count on the query recompilation to happen individually in each session if the temp table crosses different recompilation thresholds (RT). That way, the optimizer can give us an optimal plan based on the stats of the temp table.
However, I have discovered a "defect" that the recompilation thresholds are not always honored. The anomaly is that if one session has crossed a higher level RT and also incurs a recompilation, a later, different session that populates the temp table with fewer rows and also crossed a RT that equal or less than the RT of the aforementioned session, a recompilation that should have incurred would be skipped.
The best way to explain this anomaly is to show you how to repro it. I will use an easy to understand example and also screenshots to illustrate the issue. You don't need to repro it to get the idea it but I bet a lot of you will be intrigued to repro it. When I first discovered this bug, I thought I was out of my mind during the all time high record hot day in Remond, WA!
My environment is Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)
Here's the repro:
First to create objects we need: one dbo.Num table as the source of the temp table and one dbo.proc1 to be executed on different sessions.
use tempdb
create table dbo.Num( CIKey int primary key)
declare @rowcount int = 2000
declare @i int = 1
while @i <= @rowcount
begin
insert into dbo.Num(CIKey)
values (@i)
select @i += 1
end
select * from dbo.Num -- return 1 - 2000
use tempdb
create procedure Proc1 (@start int, @rowcount int, @filter int)
as
begin
create table #t (
[CIKey] [int] identity(1,1) primary key,
[NCKey] [int] ,
filler nvarchar(1000) default (replicate('x',1000))
)
create index #t_NCKey on #t(NCKey)
insert into #t ([NCKey])
select cikey from dbo.num
where cikey between @start and @start + @rowcount
-- Where the recompilation occurs
select * from #t
where NCKey >= @filter
end
go
dbcc freeproccache
dbcc dropcleanbuffers
-- get plan compiled and cached
exec proc1 1,1, 1
To catch the anomaly, let's use SQL profiler and select events SP:Completed and SP:Recompile.
That was the setup of our test environment. We are ready for our repro of the bug.
Open session 1, and run these four calls, all of them should incur 2 - Statistics changed EventSubClass in the profiler.
exec proc1 1,10, 1
exec proc1 1,515, 1
exec proc1 1,1150, 1
exec proc1 1,1900, 1
Open session2 and run the same four calls but to slightly change the first two parameters to differentiate from session 1.
exec proc1 100,11, 1
exec proc1 100,516, 1
exec proc1 100,1151, 1
exec proc1 100,1901, 1
Here is the screen shot of eight calls from two sessions (SPID 75 vs 76):
It's normal as expected with eight recompilations since each call would populate the temp table with more rows just enough to cross the RT and hence the recompilations.
Now the weird repro
Clear the profiler output but keep it running and make these calls in the following order:
Session 1:
exec proc1 1,10, 1
Session 2:
exec proc1 100,11, 1
exec proc1 100,516, 1
Session 1:
exec proc1 1,515, 1
exec proc1 1,1150, 1
Session 2:
exec proc1 100,1151, 1
exec proc1 100,1901, 1
Session 1:
exec proc1 1,1900, 1
I have highlighted the four abnormal calls missing recompilations in the Profiler screenshot below.
The correlation I can draw here is that if a different session has previously populated the temp table with closely same amount of rows, optimality recompilation will be skipped. Look at the right most column, RowCounts in the above screenshot. The abnormal ones have rowcounts that are within the same RT boundaries. For example, the second, normal line has 22 rows while the third, abnormal line has 24.
This has a huge impact on how we use temp table. Take my test proc for example. I have a third parameter @filter used to limit the returned recordset:
select * from #t
where NCKey >= @filter
Let's say session 1 filters out a lot of rows by using a large @filter. For example, exec proc1 1,2000, 1995 statement returns just 6 rows and the good plan is obviously to scan 6 rows on NC plus 6 CI bookmark lookups:
In session 2, without the needed recompilation, when running this call that doesn't do any filtering: exec proc1 1,2000, 1, the above inefficient plan is re-used causing bookmark lookup on the CI for every row (4006 reads)
If the recompilation did happen, SQL would have used a efficient CI scan to dump out the temp table (501 reads).
This unexpected behavior has been reported to SQL team and is confirmed as a bug. One of the developers put it as "a very astute discovery". I will update my blogs when I hear a fix. In the meantime, check your code and see if your application can be affected. Also feel free to leave a comment on how you would work around this.
Happy coding,
Mike Chang (Chien-Shen)
SQL Dev, MSDN