在了解統計資訊（3/6）：誰建立和管理統計資訊？在性能調優中，統計資訊的作用裡我們讨論了統計資訊的自動建立和自動更新。我們真的需要人為維護統計資訊來保持性能最優？答案是肯定的，這取決與你的工作量。SQL Server隻在達到閥限值時進行統計資訊的自動更新。當大量的Insert/Update/Delete操作發生時，内建的自動更新統計資訊不能持續保證性能的最優。
經過一系列的Insert/Update/Delete後，統計資訊可能不會是最新。如果SQL Server查詢優化器在表裡需要指定列的統計資訊，自上次統計資訊建立或更新後經曆了實質的更新活動，SQL Server會通過采樣列值自動更新統計資訊（通過自動更新統計資訊）。統計資訊的自動更新由查詢優化器或編譯好的計劃執行來觸發，它隻涉及到查詢裡引用到的各個列。如果自動異步更新統計資訊是停用的話，統計資訊會在查詢編譯前更新，啟用的話是在查詢編譯後更新。當統計資訊是異步更新時，受益于觸發更新的查詢使用老的統計資訊。對一些工作量來說，這可以提供更可預估的響應時間，尤其是那些大表上的短時間運作的查詢。
當一個查詢首次編譯完成，如果優化器需要指定對象的統計資訊，這個統計資訊存在的話，若已過期則自動更新統計資訊。如果一個查詢被執行且它的計劃在緩存裡，計劃依賴的統計資訊會被檢查是否過期，如果過期，計劃會在緩沖中移除，在查詢的重編譯時，統計資訊會被更新。如果計劃依賴的任何統計資訊被更新的話，計劃都會從緩存中移除。
SQL Server 2008基于列修改的計數器（colmodctrs）來決定是否更新統計資訊：
在下列情況下，統計資訊對象被認為過期：
如果在正常表上定義的統計資訊，被認為過期的話，那麼：
- 表的大小從0行變成了大于0行（測試1）
- 當統計資訊收集時，表的行數為500或更少，統計的第一列對象的計數器，自改變為大于500時（測試2）。
- 當統計資訊收集時，表的行數大于500時，統計的第一列對象的計數器，受表裡超過500 +20%的行數而改變（測試3）。
上述描述來自微軟的MSDN，具體參見Statistics Used by the Query Optimizer in Microsoft SQL Server 2008。
前2個條件還是相當好的，但第3個條件在處理大表時，有些時候閥值會很高，但對統計資訊更新還是無效。例如有個表有100000條記錄，隻有在200500條件記錄被修改後（update/insert），對于觸發自動更新還是無效的閥值。
我們來看個例子。
1 USE StatisticsDB
2 GO
3
4 DROP TABLE SalesOrderDetail
5 SELECT * INTO SalesOrderDetail FROM AdventureWorks2008r2.sales.SalesOrderDetail
6 CREATE INDEX ix_ProductID ON SalesOrderDetail(ProductID)
7 SET STATISTICS IO ON
8 SELECT * FROM SalesOrderDetail WHERE ProductID=725 我們建立了SalesOrderDetail表的副本，并在上面建立非聚集索引，我們看下最後SELECT查詢的執行計劃，點選工具欄的
顯示包含實際的執行計劃。
優化器選擇了索引查找和書簽查找操作作為優化的計劃，完成這個操作需要377個邏輯讀。
salesOrderDetail 表有121317條記錄，上述第3個條件如果要使統計資訊無效的話，121317的20% =24263+500=24763條記錄需要被修改，我們用下列語句隻更新5000條記錄，再次看看查詢的執行計劃，點選工具欄的
1 SET ROWCOUNT 5000
2 UPDATE SalesOrderDetail SET ProductID=725 WHERE ProductID<>725
3 SET ROWCOUNT 0
4 SET STATISTICS IO ON
5 SELECT * FROM SalesOrderDetail WHERE ProductID=725 執行計劃裡估計行數是374，這是基于上次更新操作收集的統計資訊。優化器基于統計資訊，選擇了索引查找和書簽查找作為最優計劃。SELECT操作進行5390邏輯讀來完成這個操作。
下一步，我們用producid值為725來更新19762條記錄。實際上我們更新24762條記錄（包含上一步5000條更新的記錄），比使統計資訊無效的更新的記錄（24763）少1條。
1 SET ROWCOUNT 19762
2 UPDATE SalesOrderDetail SET ProductID=725 WHERE ProductID<>725
3 SET ROWCOUNT 0
4 SET STATISTICS IO ON
5 SELECT * FROM SalesOrderDetail WHERE ProductID=725 執行計劃裡估計行數是374，這是基于上次更新操作收集的統計資訊。優化器基于統計資訊，選擇了索引查找和書簽查找作為最優計劃。完成這個操作需要25206個邏輯讀。
現在我們更新再多一條記錄使統計資訊無效。
1 SET ROWCOUNT 1
2 UPDATE SalesOrderDetail SET ProductID=725 WHERE ProductID<>725
3 SET ROWCOUNT 0
4 SET STATISTICS IO ON
5 SELECT * FROM SalesOrderDetail WHERE ProductID=725 （這裡我跌了個跟頭，在SQL SERVER 2008R2裡首次執行，始終是下列結果：
回家吃飯還在思考這個問題，一想原因，應該是自動建立統計資訊和自動更新統計資訊被停用的原因（上篇文章了解統計資訊（3/6）：誰建立和管理統計資訊？在性能調優中，統計資訊的作用 代碼執行後未還原為預設設定），在資料庫屬性裡一看，果然是False狀态，趕緊用下列語句啟用，出現的問題立馬消失！
1 ALTER DATABASE StatisticsDB SET AUTO_CREATE_STATISTICS ON
2 ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS ON 看來計算機是最誠實可靠的，即使計算機犯了錯，也是因為人犯錯造成的！ ）
和我們預期的一樣，SELECT語句觸發了自動更新統計資訊，計劃中的估計行數和實際行數已經非常接近了。這可以幫助優化器選擇更好的執行計劃。優化器選擇了表掃描而不是索引查找和書簽查找。SELECT操作隻進行了1495個邏輯讀來選取25137條記錄，比起25212個邏輯讀才選擇2516條記錄。在第一步，我們隻更新了5000條記錄，如果統計資訊在那個時候更新的話，優化器可能會選擇表掃描作為最優計劃而不是索引查找和書簽查找。那樣的話就可以隻用1495個邏輯讀代替5390個邏輯讀來完成操作，這樣就會好很多。
從這個例子我們可以清楚看到，對于自動更新統計資訊的閥值對于獲得最優性能還是不夠好。對于大表來說會更糟。我們就需要人為去更新統計資訊用來保證長須的最佳性能，當然更新的頻率要看具體的工作量。
在進行大量DML操作後，統計資訊都會過期，在查詢計劃通路統計資訊前，統計資訊都不會自動更新。更清楚的說，SQL Server會在下列情況自動更新統計資訊：
- 查詢第一次編譯，計劃使用到的統計資訊已經過期
- 查詢已有存在的查詢計劃，但計劃中的統計資訊已經過期。
繼續圍觀了解統計資訊（5/6）：如何檢測過期的統計資訊。
參考文章：
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/07/02/sql-server-part-4-all-about-sql-server-statistics-auto-update-statistics-threshold-importance-of-manual-statistics-maintenance-/
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！