[SQL Server][Statistics]統計值(三)自動更新統計值的條件(RT)

上一篇我們注意到統計值(Statistics)不夠即時可能造成SQL Optimizer無法產生最佳的執行計畫,另外也發現自動更新統計值並不是發生在資料更新的同時,而是發生在更新後第一次使用統計值的SQL指令作編譯前,那麼多少的異動量條件會觸發統計值更新?

統計值系列之三: 更新統計值的條件

測試使用版本: SQL 2014 Enterprise

 


官方解答

從微軟白皮書很快找到重新更新統計值的門檻,洋文念Recompilation threshold (RT),中文是重新編譯門檻,來試試。

Permanent table If n <= 500, RT = 500. If n > 500, RT = 500 + 0.20 * n.
Temporary table If n < 6, RT = 6. If 6 <= n <= 500, RT = 500. If n > 500, RT = 500 + 0.20 * n.
Table variable RT does not exist. Therefore, recompilations do not happen because of changes in cardinalities of table variables.

*SQL2016以前,未啟用TRACE 2371適用。

 

從上表可以注意到固定資料表、#temp table及@table variable的觸發門檻。

如果是一般資料表,資料筆數 <= 500,觸發的條件就是資料異動量超過500;超過500筆以上,則是資料表筆數的20% + 500,來試試。

 

*從 SQL Server 2016 開始,資料庫相容性層級 130 以上, SQL Server 會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。 這是以 1,000 的平方根乘以目前的資料表基數來計算。 透過這項變更,大型資料表上的統計資料會經常更新。

 


檢查環境

我們繼續使用上一篇的皇馬球員資料表。

先查看一下現狀,總筆數131,073,而且未有資料異動(資料異動量colmodctr=0)

 


大量更新但尚未觸發RT(Recompilation threshold)

我們第一次先刪除26710筆Isco的資料

131073 * 20% = 26215 + 500 = 26715

DELETE TOP(26710) FROM RealMadridClub
  WHERE NAME = 'isco'

SELECT * FROM RealMadridClub
WHERE NAME = 'isco'

EXEC usp_showStatistics 'RealMadridClub'

統計值果然尚未更新

 


累積更新觸發RT(Recompilation threshold)

接著再刪除5筆

DELETE TOP(5) FROM RealMadridClub
  WHERE NAME = 'isco'
SET STATISTICS TIME ON 
SELECT * FROM RealMadridClub
  WHERE NAME = 'isco'

EXEC usp_showStatistics 'RealMadridClub'

觸發了統計值更新,一次更新了兩個與name有關的統計值。

因為要統計值更新,所以query compilation增加了額外的時間。

白皮書內的Batch compilation and recompilation process

 


小結: 

  • 除了自動更新的選項確保啟用外,當資料庫大量資料匯入或是資料庫搬家、升級後,DBA大人也會排定工作進行統計值更新來確保統計值的新鮮度。
  • SQL Server 2008 R2 - SQL Server 2014 可以使用TRACE 2371 時, SQL Server 就會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。
  • SQL 2016-SQL 2017 相容性層級130以上,SQL就會使用使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。
  • 如果資料表很巨大,又擔心更新統計值會影響交易時間,也許可以啟用非同步更新統計值,下一篇繼續紀錄。
  • 有關SQL2016的動態RT以及T2371的說明,推薦大家閱讀Rico大的SQL2016 RT改善文章

 

大佳河濱公園​

 


參考

微軟白皮書Plan Caching in SQL Server 2008

微軟白皮書Plan Caching in SQL Server 2012

Running SAP Applications on the Microsoft Platform(Trace flag 2371)

Rico大的SQL 2016 RT改善