Auto update statistics threshold of temp table in stored procedure

By
Published 02-07-2021 02:28 PM 1,570 Views
Microsoft

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.

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

 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2117182%22%20slang%3D%22en-US%22%3EAuto%20update%20statistics%20threshold%20of%20temp%20table%20in%20stored%20procedure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117182%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20threshold%20of%20temp%20table%20in%20stored%20procedure%20is%20little%20bit%20tricky%2C%20it%20doesn%E2%80%99t%20exactly%20follow%20the%20regular%20threshold.%3C%2FP%3E%0A%3CP%3EFirst%20of%20all%2C%20you%20can%E2%80%99t%20use%20the%20modification_counter%20to%20determine%20when%20auto%20update%20statistics%20happens.%20Because%20the%20data%20in%20tempdb%20is%20deleted%20every%20time%20after%20execution%2C%20replying%20on%20the%20data%20that%20that%20already%20deleted%20does%20not%20any%20sense.%3C%2FP%3E%0A%3CP%3ESecondly%2C%20the%20algorithm%20is%20different%3A%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20original%20threshold%20of%20temp%20table%20that%20are%20not%20in%20stored%20procedure.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_0-1612732311764.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253113iBD2E6376DC836760%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_0-1612732311764.png%22%20alt%3D%22Liwei_0-1612732311764.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20new%20algorithm.%20(note%2C%20compatibility%20of%20user%20database%20decides%20old%20threshold%20or%20sublinear%20threshold%2C%20the%20compatibility%20of%20tempdb%20does%20not%20matter)%3C%2FP%3E%0A%3CTABLE%20width%3D%221153%22%3E%0A%3CTHEAD%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22156%22%3E%3CP%3E%3CSTRONG%3ETable%20cardinality%20(%3CEM%3En%3C%2FEM%3E)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22210%22%3E%3CP%3E%3CSTRONG%3EOld%20threshold%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22787%22%3E%3CP%3E%3CSTRONG%3ESublinear%20threshold%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTHEAD%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22156%22%3E%3CP%3E%3CEM%3En%3C%2FEM%3E%26nbsp%3B%26lt%3B%206%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22210%22%3E%3CP%3E%26gt%3B%3Dn%2B6%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22787%22%3E%3CP%3E%26gt%3B%3Dn%2B6%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22156%22%3E%3CP%3E6%20%26lt%3B%3D%26nbsp%3B%3CEM%3En%3C%2FEM%3E%26nbsp%3B%26lt%3B%3D%20500%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22210%22%3E%3CP%3E%26gt%3B%3Dn%2B500%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22787%22%3E%3CP%3E%26gt%3B%3Dn%2B500%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22156%22%3E%3CP%3E%3CEM%3E500%3CN%3E%3C%2FN%3E%3C%2FEM%3E%26nbsp%3B%26lt%3B%3D19682%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22210%22%3E%3CP%3E%26gt%3B%3D%20n%2B(500%20%2B0.2*n)%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26lt%3B%3D%20n-(500%2B0.2*n)%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22787%22%3E%3CP%3E%26gt%3B%3D%20n%2B(500%20%2B0.2*n)%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26lt%3B%3D%20n-(500%2B0.2*n)%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22156%22%3E%3CP%3E%3CEM%3En%3C%2FEM%3E%26nbsp%3B%26gt%3B19682%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22210%22%3E%3CP%3E500%20%2B%20(0.20%20*%26nbsp%3B%3CEM%3En%3C%2FEM%3E)%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22787%22%3E%3CP%3E%26gt%3B%3D%20n%2Bsqrt(1000*n)%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26lt%3B%3Dn-sqrt(1000*n)%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20example%20in%20compatibility%20level%20150%20in%20SQL%202019.%3C%2FP%3E%0A%3CP%3E1.Enable%20the%20AUTO_update_STATISTICS%20for%20temp%20table%20and%20create%20a%20table%20with%2030%2C000%20rows.%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ealter%20database%20tempdb%20set%20AUTO_update_STATISTICS%20on--%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Euse%20dbstat%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ecreate%20table%20table1(c1%20int%2Cc2%20int%2Cc3%20int)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E------------%20--------INSERT%2030%2C000%20rows%20to%20table%20table1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ewith%20cte%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eas%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E(%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%26nbsp%3B%26nbsp%3B%20select%20top%2030000%20ROW_NUMBER()%20over(order%20by%20c.object_id)%20id%20from%20sys.columns%20c%20cross%20join%20sys.all_objects%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20table1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20id%2Cid%2Cid%20from%20cte%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.Create%20a%20stored%20procedure.%20In%20this%20stored%20procedure%2C%20it%20imports%20the%20data%20from%20table1%20created%20in%20step1%2C%20into%20the%20temp%20table%20%23t1%2C%20then%20run%20select%20query%20against%20the%20temp%20table%20%23t1%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ecreate%20proc%20procTempTable%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%40num%20int%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eas%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ecreate%20table%20%23t1(c1%20int%20index%20ic%20nonclustered%2Cc2%20int%2Cc3%20int)---create%20temp%20table%20with%20primary%20key%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20%23t1%20select%20top(%40num)%20*%20from%20table1%20order%20by%20c1---insert%20data%20to%20temp%20table%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20*%20from%20%23t1%26nbsp%3B%20where%20c1%3D%40num%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20object_id('tempdb.dbo.%23t1')%20as%20TableId%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E3.Let%E2%80%99s%20run%20the%20stored%20procedure%20which%20parameter%202.%3C%2FP%3E%0A%3CP%3Eexec%20procTempTable%202%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_1-1612732311766.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253114iEFA3591E5DCC8CA4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_1-1612732311766.png%22%20alt%3D%22Liwei_1-1612732311766.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.Here%20is%20what%20happened%20when%20the%20stored%20procedure%20is%20created%20the%20first%20time.%3C%2FP%3E%0A%3CP%3E1)The%20temp%20table%20%23t1%20is%20created%2C%20and%20it%20will%20be%20cached%20for%20reuse%20until%20the%20stored%20procedure%20gets%20recompiled.%3C%2FP%3E%0A%3CP%3EIf%20you%E2%80%99re%20not%20familiar%20with%20the%20temp%20table%20caching%2C%20please%20refer%20Paul%20White%E2%80%99s%20%3CA%20href%3D%22https%3A%2F%2Fwww.sql.kiwi%2F2012%2F08%2Ftemporary-object-caching-explained.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Earticle%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E2)Because%20index%20ic%20on%20column1%20is%20specified%20inline%20in%20the%20create%20table%20DDL%2C%20a%20statistics%20with%20same%20name%20is%20created%20at%20the%20same%20time%20when%20table%20%23t1%20is%20created.%3C%2FP%3E%0A%3CP%3E3)The%20statistics%20is%20refreshed%20when%20following%20query%20is%20executed.%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20*%20from%20%23t1%20where%20c1%3D%40num%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4).Here%20is%20the%20statistics%20info%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Euse%20tempdb%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20sp.*From%20sys.stats%20s%20cross%20apply%20sys.dm_db_stats_properties(s.object_id%2Cs.stats_id)%20sp%26nbsp%3B%20where%20s.object_id%3D-1242610934%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_2-1612732311768.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253115iB623A5F79BBB5678%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_2-1612732311768.png%22%20alt%3D%22Liwei_2-1612732311768.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.For%20temp%20table%20that%20are%20not%20in%20stored%20procedure%2C%20the%20statistics%20is%20updated%20after%20the%20threshold%20is%20reached%20and%20a%20query%20running%20against%20the%20statistics.%3C%2FP%3E%0A%3CP%3EBut%20it%E2%80%99s%20totally%20different%20if%20the%20temp%20table%20is%20in%20stored%20procedure%2C%20the%20threshold%20does%20not%20depend%20on%20the%20modification_counter%2C%20which%20is%20a%20cumulative%20value.%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20I%20run%20the%20stored%20procedure%20200%20times%2C%20the%20modification_counter%20increase%20to%201002%2C%20but%20update%20statistics%20will%20not%20happen.%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eexec%20procTempTable%202%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%20200%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_3-1612732311770.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253118i429809FC1B445D72%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_3-1612732311770.png%22%20alt%3D%22Liwei_3-1612732311770.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20trigger%20the%20auto%20update%20statistics%2C%20the%20number%20of%20row%20change%20has%20before%20the%20temp%20table%20is%20deleted%20in%20single%20execution%20has%20to%20reach%20the%20threshold.%3C%2FP%3E%0A%3CP%3EIn%20this%20case%2C%20the%20cardinality%20is%202%2C%20the%20threshold%20is%20n%2B6%3D2%2B6%3D8.%3C%2FP%3E%0A%3CP%3ERunning%20following%20t-sql%20will%20trigger%20the%20auto%20update%20statistics.%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Euse%20dbstat%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eexec%20procTempTable%208%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_4-1612732311771.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253116i9A8135A65644137A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_4-1612732311771.png%22%20alt%3D%22Liwei_4-1612732311771.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_5-1612732311772.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253117i567EA4ECDAB3D2C2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_5-1612732311772.png%22%20alt%3D%22Liwei_5-1612732311772.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EN%26lt%3B500%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ENow%20the%20cardinality%20of%20temp%20table%20becomes%208%2C%20the%20threshold%20become%20n%2B500%3D508%20according%20to%20the%20formula.%3C%2FP%3E%0A%3CP%3ERunning%20following%20t-sql%20will%20trigger%20the%20auto%20update%20statistics.%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Euse%20dbstat%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eexec%20procTempTable%20508%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_6-1612732311773.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253119i77FFC98BB0BCB663%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_6-1612732311773.png%22%20alt%3D%22Liwei_6-1612732311773.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_7-1612732311774.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253121iF8C136A2C36269C1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_7-1612732311774.png%22%20alt%3D%22Liwei_7-1612732311774.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E500%3CN%3E%26lt%3B%3D19682%3C%2FN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3Ea)According%20to%20the%20formula%3A%3C%2FP%3E%0A%3CP%3Ethe%20number%20of%20data%20change%20is%20%26gt%3B%3D%20n%2B(500%20%2B0.2*n)%3D508%2B(500%2B508*0.2)%3D1109%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3Ethe%20number%20of%20data%20change%20is%20%26lt%3B%3D%20n-(500%20%2B0.2*n)%3D508-(500%2B508*0.2)%3D%20-93%2Cwhich%20is%20not%20possible%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eexec%20procTempTable%201109%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_8-1612732311774.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253120iAB8FFB6AC1DF0E54%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Liwei_8-1612732311774.png%22%20alt%3D%22Liwei_8-1612732311774.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_9-1612732311775.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253124i69807FF19D3E9700%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_9-1612732311775.png%22%20alt%3D%22Liwei_9-1612732311775.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eb)1109%20is%20still%20less%20than%2019682%2C%20According%20to%20the%20formula%3A%3C%2FP%3E%0A%3CP%3Ethe%20number%20of%20data%20change%20is%20%26gt%3B%3D%20n%2B(500%20%2B0.2*n)%3D%201109%2B(500%2B1109*0.2)%3D%201830%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3Ethe%20number%20of%20data%20change%20is%20%26lt%3B%3D%20n-(500%20%2B0.2*n)%3D%201109-(500%2B1109*0.2)%3D%20388.%3C%2FP%3E%0A%3CP%3ELet%E2%80%99s%20use%20388%20this%20time.%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eexec%20procTempTable%20388%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_10-1612732311776.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253122i715BF03AF521EC18%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_10-1612732311776.png%22%20alt%3D%22Liwei_10-1612732311776.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_11-1612732311777.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253123i77B2081507EFB03B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_11-1612732311777.png%22%20alt%3D%22Liwei_11-1612732311777.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3En%26gt%3B19682%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ENow%20we%20have%2020%2C000%20rows%20by%20running%20the%26nbsp%3B%3CFONT%20color%3D%22%230000FF%22%3Eexec%20procTempTable%2020000%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_14-1612733126502.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253130i4883065B6F2E59D3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_14-1612733126502.png%22%20alt%3D%22Liwei_14-1612733126502.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20threshold%20is%3C%2FP%3E%0A%3CP%3E%26gt%3B%3D%20n%2Bsqrt(1000*n)%3D%2020000%2Bsqrt(1000*20000)%20%3D%2024472%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26lt%3B%3D%20n-sqrt(1000*n)%3D%2020000-%20sqrt(1000*20000)%20%3D%2015528.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_12-1612732311777.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253125iE96396E01598BDE5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Liwei_12-1612732311777.png%22%20alt%3D%22Liwei_12-1612732311777.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_13-1612732311778.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253126i122A97DDE86F5B7B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_13-1612732311778.png%22%20alt%3D%22Liwei_13-1612732311778.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2117182%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Feb 07 2021 02:29 PM
Updated by: