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