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 does not any sense.
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.
