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

2018-01-14

上一篇我們注意到統計值(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改善文章。

大佳河濱公園