This article is contributed. See the original author and article here.

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.


Liwei_0-1612732311764.png


 


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


Liwei_1-1612732311766.png


 


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


Liwei_2-1612732311768.png


 


 


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


Liwei_3-1612732311770.png


 


 


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


Liwei_4-1612732311771.png


 


Liwei_5-1612732311772.png


 


 


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


Liwei_6-1612732311773.png


 


Liwei_7-1612732311774.png


 


 


 


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


Liwei_8-1612732311774.png


 


Liwei_9-1612732311775.png


 


 


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


Liwei_10-1612732311776.png


 


Liwei_11-1612732311777.png


 


 


n>19682


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


Liwei_14-1612733126502.png


 


The threshold is


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


or


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


 


Liwei_12-1612732311777.png


 


Liwei_13-1612732311778.png


 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.