Auto update statistics threshold of temp table in stored procedure

The threshold of temp table in stored procedure is little bit tricky, it doesn’t exactly follow the regular threshold.

First of all, you can’t use the modification_counter to determine when auto update statistics happens. Because the data in tempdb is deleted every time after execution, replying on the data that that already deleted is pointless.

Secondly, the algorithm is different:

Here is the original threshold of temp table that are not in stored procedure.

 

Here is the new algorithm. (note, compatibility of user database decides old threshold or sublinear threshold, the compatibility of tempdb does not matter)

Table cardinality (n)	Old threshold	Sublinear threshold
n < 6	>=n+6	>=n+6
6 <= n <= 500	>=n+500	>=n+500
500<n <=19682	>= n+(500 +0.2*n) or <= n-(500+0.2*n)	>= n+(500 +0.2*n) or <= n-(500+0.2*n)
n >19682	500 + (0.20 * n)	>= n+sqrt(1000*n) or <=n-sqrt(1000*n)

 

 

Here is an example in compatibility level 150 in SQL 2019.

1.Enable the AUTO_update_STATISTICS for temp table and create a table with 30,000 rows.

alter database tempdb set AUTO_update_STATISTICS on--

go

use dbstat

go

create table table1(c1 int,c2 int,c3 int)

------------ --------INSERT 30,000 rows to table table1

go

with cte

as

(

   select top 30000 ROW_NUMBER() over(order by c.object_id) id from sys.columns c cross join sys.all_objects

)

insert table1

select id,id,id from cte

go

 

2.Create a stored procedure. In this stored procedure, it imports the data from table1 created in step1, into the temp table #t1, then run select query against the temp table #t1

create proc procTempTable

@num int

as

create table #t1(c1 int index ic nonclustered,c2 int,c3 int)---create temp table with primary key

insert #t1 select top(@num) * from table1 order by c1---insert data to temp table

select * from #t1  where c1=@num

select object_id('tempdb.dbo.#t1') as TableId

3.Let’s run the stored procedure which parameter 2.

exec procTempTable 2

 

4.Here is what happened when the stored procedure is created the first time.

1)The temp table #t1 is created, and it will be cached for reuse until the stored procedure gets recompiled.

If you’re not familiar with the temp table caching, please refer Paul White’s article.

2)Because index ic on column1 is specified inline in the create table DDL, a statistics with same name is created at the same time when table #t1 is created.

3)The statistics is refreshed when following query is executed.

select * from #t1 where c1=@num

 

4).Here is the statistics info

use tempdb

go

select sp.*From sys.stats s cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp  where s.object_id=-1242610934

 

 

4.For temp table that are not in stored procedure, the statistics is updated after the threshold is reached and a query running against the statistics.

But it’s totally different if the temp table is in stored procedure, the threshold does not depend on the modification_counter, which is a cumulative value.

For example, I run the stored procedure 200 times, the modification_counter increase to 1002, but update statistics will not happen.

exec procTempTable 2

go 200

 

 

To trigger the auto update statistics, the number of row change has before the temp table is deleted in single execution has to reach the threshold.

In this case, the cardinality is 2, the threshold is n+6=2+6=8.

Running following t-sql will trigger the auto update statistics.

use dbstat

go

exec procTempTable 8

 

 

 

N<500

Now the cardinality of temp table becomes 8, the threshold become n+500=508 according to the formula.

Running following t-sql will trigger the auto update statistics.

use dbstat

go

exec procTempTable 508

 

 

 

 

500<n<=19682

a)According to the formula:

the number of data change is >= n+(500 +0.2*n)=508+(500+508*0.2)=1109

or

the number of data change is <= n-(500 +0.2*n)=508-(500+508*0.2)= -93,which is not possible

exec procTempTable 1109

 

 

 

b)1109 is still less than 19682, According to the formula:

the number of data change is >= n+(500 +0.2*n)= 1109+(500+1109*0.2)= 1830

or

the number of data change is <= n-(500 +0.2*n)= 1109-(500+1109*0.2)= 388.

Let’s use 388 this time.

exec procTempTable 388

 

 

 

n>19682

Now we have 20,000 rows by running the exec procTempTable 20000

 

The threshold is

>= n+sqrt(1000*n)= 20000+sqrt(1000*20000) = 24472

or

 <= n-sqrt(1000*n)= 20000- sqrt(1000*20000) = 15528.

 

 

 

 

Add tags