"Auto update statistics" option on tempdb

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