[SQL SERVER]SQL2016-RT改善

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')

Enjoy SQL Server 2016

 

參考

What's New in Database Engine

Default auto statistics update threshold change for SQL Server 2016

Changes to automatic update statistics in SQL Server – traceflag 2371

[SQL SERVER][Memo]認識重新編譯臨界值