[SQL SERVER][Memo]認識重新編譯臨界值
什麼是重新編譯臨界值 ( Recompilation Threshold (RT) )
RT其實就是SQL Server用來決定發生多少資料異動量時，
才可標示統計值過期的依據，我們可以透過sysindexes的rowmodctr欄位查看。
當資料庫開啟自動更新統計值或自動非同步更新統計值選項，
且當資料異動量也大(等)於RT值，SQL Server就會自動觸發更新統計值動作，
而這樣的處理是為了要讓查詢最佳化工具建立較佳的查詢計畫。
下面我就來實際測試自動更新統計值的行為。
SQL 2008對於RT的計算如下(n:資料表基數):
1. 一般資料表
If n <= 500, RT = 500
If n > 500, RT = 500 + 0.20 * n
create table tA
(
c1 int identity(1,1),
c2 int
)
create nonclustered index idx_1 on tA(c2)
insert into tA values(4)
select *
from tA
where c2=101
查看rowmodctr
select id,status,indid,dpages,rowcnt,rowmodctr
from sysindexes
where id=OBJECT_ID('dbo.tA')
目前rowmodctr=1。基數(rowcnt)=1。
查看統計值
select *
from sys.stats t1
where object_id=object_id('dbo.tA')
查看目前最佳化統計資料
DBCC SHOW_STATISTICS ("dbo.tA", 'idx_1');
索引鍵值=4。資料行值等於長條圖步驟之上限的預估資料列數=1。
if n<=500,RT=500，目前n=1，我新增500筆資料(>=RT)測試看看是否會觸發更新統計值
declare @i int
set @i=1
set nocount on
while(@i<=500)
begin
insert into tA values(@i+4)
set @i=@i+1
end
select *
from tA
where c2=101
查看rowmodctr
rowmodctr=0。
查看目前最佳化統計資料
Rows Sampled=501。(擷取部份)
你可以看到目前最佳化統計資料的取樣筆數=501，統計值更新時間也和第一次不同。
If n>500, RT = 500 + 0.20 * n，我再新增500+101筆資料>=RT測試看看是否會再次觸發更新統計值
declare @i int
set @i=1
set nocount on
while(@i<=601)
begin
insert into tA values(@i+4)
set @i=@i+1
end
--查詢
select *
from tA
where c2=101
查看rowmodctr
rowmodctr又歸0。(看來只要超過RT值，rowmodctr欄位變會歸0並重新累積計算)
查看目前最佳化統計資料
Rows Sampled=1102。(擷取部份)
果然如預期更新目前最佳化統計資料，資料取樣筆數1102。
現在我知道下一次觸發更新統計值的資料筆數要 > = RT=500+(0.2*1102)=720.4，
但這次我只新增700筆資料(不超過RT值)，看看是否如預期不會觸發更新統計值。
declare @i int
set @i=1
set nocount on
while(@i<=700)
begin
insert into tA values(@i+4)
set @i=@i+1
end
--查詢
select *
from tA
where c2=101
查看rowmodctr
rowmodctr=700(並未觸發自動更新統計值)。
查看目前最佳化統計資料
目前統計值筆數取樣=1102。更新時間也未改變。
影響rowmodctr(colmodctr)變化
| 陳述句 | rowmodctr(colmodctr)變化 |
| Delete n | rowmodctr + n |
| Insert n | rowmodctr + n |
| Bulk Insert | rowmodctr + n (如同 Insert n) |
| Truncate Table | rowmodctr + n (如同 Delete n) |
| Update n | rowmodctr + n |
n:筆數。
注意:rowmodctr累積計算不參予交易，假設總共新增10筆資料，其中復原5筆資料，
那麼rowmodctr累積計算依然=10，而不是5。
2. 暫存資料表
If n < 6, RT = 6.
If 6 <= n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n
暫存資料表有興趣的朋友可以自行測試看看。
3. 資料表值參數
資料表值參數並無存在統計值。
參考