升級SQL 2014,統計資訊(Statistics)的重要性?

統計資訊到底重不重要?

很多人都會說統計資訊的正確性很重要,因此要定時維護。事實上在SQL Server 2008的時候,我都仰賴自動更新而沒有設過任何排成維護。但是我在升級SQL Server 2014時,吃了一個大悶虧是關於統計資訊。原先CPU 好好的,升級後突然CPU高到接近100%。

自動更新的Option如下圖
「sql statistics auto update option」的圖片搜尋結果

先說說為什麼維護統計資訊很重要

  1. SQL Server產生執行計畫Execution Plan會依據統計資訊產生
  2. 所以不好的統計資訊會讓SQL Server誤用執行計畫
  3. 要特別注意統計資訊更新後會促發執行計畫重新編譯 (Recompile)

好的讓我們回到故事,因為SQL Server 2014 採用新的CE (新的演算法)。推論新的CE對於統計資訊的正確性更加的敏感,我的推論是從升級後,當我手動更新統計資訊 (update statistics, sp_updatestats),CPU就降了。但是我發生問題的幾隻SP都是高頻交易的Table,因此過一段時間CPU就會在高上去。原因推斷是因為高頻交易導致統計資訊不正確

 

我因此思考了幾個方向來解決這個問題
問題:雖然已經啟用自動更新統計資料,懷疑更新的不夠頻繁,在高頻交易下導致統計資訊不正確,形成不好的執行計畫。

  1. 排程更新統計資訊
  2. 啟用Trace Flag 2371
  3. 對於有問題的SP 加Hint OPTION(QUERYTRACEON 9481)

以下介紹三個方法的優劣,我最後是採用方法2

  1. 排程更新統計資訊
    手動排程也是一個做法,但是要什麼時候做?做的頻率是多少?並不是一個容易回答的問題
  2. 啟用Trace Flag 2371
    SQL Server 2005 – SQL Server 2014:

    • 假如沒有開起Trace Flag 2371
      • 如Table有 1-500 筆, 假如有500筆變動,統計資訊將會自動更新。
      • 如Table有 500 筆以上的資料, 有500 筆+ 20% 總筆數的變動,統計資訊將會自動更新。
    • 假如開啟Trace Flag 2371
      • 統計資訊自動更新的閥值會隨著資料量的增加而降低,如下圖clip_image002
    • 舉例說明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中建議,如果過時的統計資訊沒有造成效能問題,則無需啟用
  3. 對於有問題的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/

https://docs.microsoft.com/zh-tw/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017

 

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s