統計資訊到底重不重要？
很多人都會說統計資訊的正確性很重要，因此要定時維護。事實上在SQL Server 2008的時候，我都仰賴自動更新而沒有設過任何排成維護。但是我在升級SQL Server 2014時，吃了一個大悶虧是關於統計資訊。原先CPU 好好的，升級後突然CPU高到接近100%。
自動更新的Option如下圖
先說說為什麼維護統計資訊很重要
- SQL Server產生執行計畫Execution Plan會依據統計資訊產生
- 所以不好的統計資訊會讓SQL Server誤用執行計畫
- 要特別注意統計資訊更新後會促發執行計畫重新編譯 (Recompile)
好的讓我們回到故事，因為SQL Server 2014 採用新的CE (新的演算法)。推論新的CE對於統計資訊的正確性更加的敏感，我的推論是從升級後，當我手動更新統計資訊 (update statistics, sp_updatestats)，CPU就降了。但是我發生問題的幾隻SP都是高頻交易的Table，因此過一段時間CPU就會在高上去。原因推斷是因為高頻交易導致統計資訊不正確。
我因此思考了幾個方向來解決這個問題
問題：雖然已經啟用自動更新統計資料，懷疑更新的不夠頻繁，在高頻交易下導致統計資訊不正確，形成不好的執行計畫。
- 排程更新統計資訊
- 啟用Trace Flag 2371
- 對於有問題的SP 加Hint OPTION(QUERYTRACEON 9481)
以下介紹三個方法的優劣，我最後是採用方法2
- 排程更新統計資訊
手動排程也是一個做法，但是要什麼時候做？做的頻率是多少？並不是一個容易回答的問題 - 啟用Trace Flag 2371
SQL Server 2005 – SQL Server 2014：- 假如沒有開起Trace Flag 2371
- 如Table有 1-500 筆, 假如有500筆變動，統計資訊將會自動更新。
- 如Table有 500 筆以上的資料, 有500 筆+ 20% 總筆數的變動，統計資訊將會自動更新。
- 假如開啟Trace Flag 2371
- 統計資訊自動更新的閥值會隨著資料量的增加而降低，如下圖
- 統計資訊自動更新的閥值會隨著資料量的增加而降低，如下圖
- 舉例說明100,000 筆左右的資料當有10%的變化就會觸發更新，10,000,000 到50,000,000 筆之間，只要有1%的變化就會觸發更新。更甚至是100,000,000 筆資料只要大約 0.31%的變化就會觸發更新。
- 優點
- Trace Flag 2371 記載在官方文件
- Trace Flag 2371 在SQL SERVER 2016之後的版本是預設開啟
- 缺點
- Trace Flag 2371 是Global的，所以是Server層級的設定
- KB 2754171中建議，如果過時的統計資訊沒有造成效能問題，則無需啟用
- 優點
- 假如沒有開起Trace Flag 2371
- 對於有問題的SP 加Hint OPTION(QUERYTRACEON 9481)
這個方法強迫該SP沿用舊的CE，對於Server是最小變動的做法。SQL SERVER 2016可以使用 OPTION (USE HINT (‘FORCE_LEGACY_CARDINALITY_ESTIMATION’ ))
統計資訊的維護是不能過於消極也不能過於積極：
- 過於消極：假如Table中有數百萬筆資料，有天你發現Query的效能變慢了，並且發現原因是因為統計資料從來沒更新過，正是因為消極導致沒有注意到維護統計資訊的重要性。
- 過於積極：安排過多的統計資訊維護，當你在做一些消耗高IO的維護時，如Reindex，同時做統計資訊的更新可能導致Server有IO瓶頸。過於積極的統計資訊維護可能導致不必要的IO和資源浪費。
比較適合的方向：
- 在非尖鋒時刻利用腳本或是排程更新統計資料，大多數的人是每週做一次。
- 只有在需要時才頻繁更新統計資訊，然後定義這些頻繁的作業僅更新有問題統計資訊。 假如必須，針對該統計資訊使用FULLSCAN，並且記錄哪些查詢該統計資訊。
- 如果Table有數百萬筆資料並且遇到統計資訊不準確導致效能低落的情況，考慮使用Trace Flag 2371來增加這些Table的統計資訊的更新頻率，而不是設定全時手動更新統計資訊。
- 如果有批處理或ETL進行大量數據的運作，考慮在進程結束時更新受影響Table的統計資訊。
如果已Rebuild Index，則與該Index相關的統計資訊已使用FULLSCAN更新，並不需要任何維護。
https://littlekendra.com/2016/04/18/updating-statistics-in-sql-server-maintenance-answers/
DBA 黑白講 