[SQL SERVER]SQL2016-RT改善

2016-10-27

SQL Server 2016 預設會動態降低資料表RT，無須額外啟用T2371。

大型資料庫中我都會額外啟用T2371，由於大型資料表RT過高(20%)，

常導致統計值不良，啟用T2371後會隨者資料表筆數依序降低RT，

避免發生統計值未更新情況，現在SQL Server 2016的RT預設會依照資料表動態降低且無需啟用T2371

(資料庫相容性層級須設定130，並建議啟用非同步更新統計值)，

但如果資料庫相容性層級<130，你依然可以啟用2371，這篇簡單來驗證看看。

圖片來源: Changes to automatic update statistics in SQL Server – traceflag 2371

--總筆數: 200000
select count(*) from testRT

--查看資料表累積值
select id,status,indid,dpages,rowcnt,rowmodctr 
from sys.sysindexes
where id=OBJECT_ID('dbo.testRT')

--查看目前統計資料
select st.object_id,st.name,st.stats_id,stpro.last_updated,stpro.rows_sampled from sys.stats st 
cross apply sys.dm_db_stats_properties (object_id, stats_id) stpro
where st.object_id = object_id ('testRT')

一般資料表RT計算如下:

N:資料表筆數

If n <= 500, RT = 500

If n > 500, RT = 500 + 0.20 * n

SQL Server 2016以前的版本，RT=500+(0.2* 200000)= 40500.0，

表示資料異動須超過40500，SQL Server才會幫你自動更新統計值，

現在我刪除30000筆，看看統計資料是否會自動更新。

set nocount on
delete top (30000) from testRT
select * from testRT
where c1>100 and c2>100

--查看資料表累積值
select id,status,indid,dpages,rowcnt,rowmodctr 
from sys.sysindexes
where id=OBJECT_ID('dbo.testRT')

目前rowmodctr累積0(只要超過RT值，rowmodctr欄位變會歸0並重新累積計算)

--查看目前統計資料
select st.object_id,st.name,st.stats_id,stpro.last_updated,stpro.rows_sampled from sys.stats st 
cross apply sys.dm_db_stats_properties (object_id, stats_id) stpro
where st.object_id = object_id ('testRT')