"Auto update statistics" option is enabled by default on tempdb, if it is disabled, you may get trouble in some case.Today when I tested script, I found the index created on the temp table didn't work because of "Auto update statistics" disabled.
here is the script.
USE [master]
GO
ALTER DATABASE [tempdb] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
GO
USE [tempdb]
GO
dbcc freeproccache
GO
Create table #mytemp1(a int, b int)
GO
create index temp_inx1 on #mytemp1(a)
GO
declare @int int
set @int=1
while @int <10000
begin
insert into #mytemp1 values(@int, @int+1)
--insert into #mytemp2 values(@int, @int+1)
set @int+=1
end
GO
set statistics profile on
GO
select b from #mytemp1 where a=50
GO
set statistics profile off
GO
and you can see the script use table scan instead of index seek, because we created an index on a, table scan is not we wanted.
let's enable the "Auto update statistics", and do the test again
USE [master]
GO
ALTER DATABASE [tempdb] SET AUTO_UPDATE_STATISTICS on WITH NO_WAIT
GO
USE [tempdb]
GO
dbcc freeproccache
GO
Create table #mytemp1(a int, b int)
GO
create index temp_inx1 on #mytemp1(a)
GO
declare @int int
set @int=1
while @int <10000
begin
insert into #mytemp1 values(@int, @int+1)
--insert into #mytemp2 values(@int, @int+1)
set @int+=1
end
GO
set statistics profile on
GO
select b from #mytemp1 where a=50
GO
set statistics profile off
GO
drop table #mytemp1
this time we got index seek, this is because:
when "Auto update statistics" is enabled, sql server will check if statistics is stall before generation query plan. since we inserted 1000 rows after index created, the statistics is stall. then sql server will update the statistics, so sql server can get correct statistics and select index seek instead of table scan as query plan.
if you are working with big temp table object, be careful for the "Auto update statistics" on tempdb.
