在了解統計資訊（2/6）：直方圖 中，我們讨論了直方圖，密度向量，還有SQL Server如何用統計資訊做基數計算（cardinality estimation）。這篇文章會讨論統計資訊如何被建立，還有統計資訊在性能調優中的重要性。
有2類統計資訊，索引統計資訊和列統計資訊。索引統計資訊是索引建立的一部分（建立索引會自動建立索引統計資訊）。在where條件列被引用或查詢的group by子句裡包含列，列統計資訊都會由SQL Server自動建立。
有資料庫屬性設定裡，可以設定資料庫是否自動建立統計資訊并自動更新統計資訊（資料庫屬性->選項->自動）。
自動建立統計資訊預設是啟用的，它幫助查詢優化器在需要更好的進行查詢預估時，建立列統計資訊。為了更好的性能，建議保留啟用。
自動更新統計資訊預設也是啟用的，它幫助查詢優化器在統計資訊過期的時候自動更新。當資料有明顯變化時，統計資訊就需要更新。這裡有個閥限（threshold limit）來标記統計資訊是否過期。
自動異步更新統計資訊預設是不啟用的。當自動異步更新統計資訊被啟用的時候，會有2種方式進行自動更新。異步模式（預設模式），如果統計資訊已經過期，查詢優化器會等到計劃生成完成才更新統計資訊。同步模式，查詢優化器會初始化統計資訊，不會等到計劃的生成完成。通過改變更新統計資訊為同步模式可以使性能上一些工作量始終受益。SQL Server在自動建立/更新統計資訊的時候，不會進行完全掃描。它隻會在可接受的時間内采樣資料來計算統計資訊。
在了解統計資訊（1/6）：密度裡，我們看到，當引用的列在group by或where條件裡時，統計資訊會自動建立。我們來看看當自動建立統計資訊關掉的時候，SQL Server如何進行預估。我們運作下面的語句并看看輸出結果。
1 ALTER DATABASE StatisticsDB SET AUTO_CREATE_STATISTICS OFF
2 GO
3 DROP TABLE SalesOrderDetail_NoStats
4 SELECT * INTO SalesOrderDetail_NoStats FROM AdventureWorks2008r2.Sales.SalesOrderDetail
5 GO
6 SELECT ProductID,COUNT(*) FROM dbo.SalesOrderDetail_NoStats GROUP BY ProductID
7 GO
8 EXEC SP_HELPSTATS 'SalesOrderDetail_NoStats', 'ALL' 第1句，我們關掉了StatisticsDB資料庫的自動更新統計資訊。第2句，我們建立了salesOrderDetail表的副本。現在我們對ProductId進行group 扮演操作，點選工具欄的
顯示包含實際的執行計劃。
在執行計劃裡，我們在表掃描運算符裡看到一個黃色的驚歎号。具體來說，它是警告我們沒有可用的統計資訊。在聚集運算符裡，我們看到優化器的估計行數是348.306。沒有統計資訊，優化器要如何估計行數呢？ 這裡的值是拿記錄總數開方而來。這個表有121317條記錄，如果你對它開方，即
，我們就得到348.306。如果你對這個表group by的其他任何列，預估行數還是一樣的。 請注意，自動建立統計資訊隻控制列統計資訊的自動建立。它不控制索引建立時，統計資訊的自動建立。
我們來看下統計資訊如何影響查詢性能。來看下面2個查詢，記得在最後的查詢語句執行前點選工具欄的
1 USE StatisticsDB
2 GO
3 /* Part 1 WITH AUTO STATS UPDATE ON */
4
5 ALTER DATABASE StatisticsDB SET AUTO_CREATE_STATISTICS ON
6 ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS ON
7 SET STATISTICS IO ON
8 DROP TABLE SalesOrderDetail_NoStats
9 SELECT * INTO SalesOrderDetail_NoStats FROM SalesOrderDetail
10 CREATE INDEX ix_productid ON SalesOrderDetail_NoStats (productid)
11 UPDATE dbo.SalesOrderDetail_NoStats SET ProductID=775 WHERE SalesOrderDetailID<>1
12 SELECT * FROM dbo.SalesOrderDetail_NoStats WHERE ProductID=776
13 SELECT * FROM dbo.SalesOrderDetail_NoStats WHERE ProductID=775
14
15 /* Part 2 WITH AUTO STATS UPDATE Off */
16
17 ALTER DATABASE StatisticsDB SET AUTO_CREATE_STATISTICS OFF
18 ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS OFF
19 SET STATISTICS IO ON
20 DROP TABLE SalesOrderDetail_NoStats
21 SELECT * INTO SalesOrderDetail_NoStats FROM SalesOrderDetail
22 CREATE INDEX ix_productid ON SalesOrderDetail_NoStats (productid)
23 UPDATE dbo.SalesOrderDetail_NoStats SET ProductID=775 WHERE SalesOrderDetailID<>1
24 --Disabling the auto update stats
25 ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS OFF
26 SELECT * FROM dbo.SalesOrderDetail_NoStats WHERE ProductID=776
27 SELECT * FROM dbo.SalesOrderDetail_NoStats WHERE ProductID=775 上述2組語句我們都在productid列建立了索引（統計資訊也會自動建立），然後我們更新productid為775，隻留1條還是不同的productid值。更新後，表裡隻有2個不同的productid值775和776。第1組語句，我們進行了自動更新統計資訊啟用的SELECT查詢。第2組語句我們進行了自動更新統計資訊停用的SELECT查詢。我們來看看2者執行計劃和IO統計資訊的不同。
我們來看看啟用的執行計劃。第1個where條件是productid=776的查詢估計行數是1,000348，優化器進行的是索引查找。第2個where條件是productid=775的查詢估計行數是121316，優化器選擇的是表掃描，而不是非聚集索引查找和書簽查找。對優化器來說表掃描更有效，相比使用索引查找和書簽查找來擷取表裡的所有記錄（隻有一條記錄productid是776）。完成這個操作隻需要1495個邏輯讀。
我們來看看停用的執行計劃。第1個where條件是productid=776的查詢估計行數是228，優化器進行的是索引查找。這個是基于索引建立是的統計資訊來預估的，這個資訊在update後已經過期了。第2個where條件是productid=775的查詢估計行數是234，這就嚴重誤導了查詢優化器使用了非聚集掃描和書簽查找來操作，而不是表掃描來擷取表的所有記錄（隻有一條記錄productid是776）。完成這個操作需要121710個邏輯讀，相比啟用情況下僅1495個邏輯讀是非常非常高了。
從上面的例子，我們清楚的看到優化器需要更新的統計資訊來選擇最優執行計劃，即使你有了必須的索引。在處理性能問題時，我們也需要關注下統計資訊。把估計行數與實際行數的差別當作一個好名額，用來深入了解下統計資訊，或統計資訊的人為更新。
自動更新統計資訊可以在以下3個級别進行關閉：
- 資料庫級别，使用修改資料庫指令：ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS OFF
- 索引級别，在建立或重建索引時使用STATISTICS_NORECOMPUTE 選項。這有點令人迷惑。這個選項預設是關閉的。就是說自動更新屬性是啟用的。
- 統計資訊級别，當建立或更新統計資訊時使用NORECOMPUTE 選項。
使用sp_autostats 存儲過程可以檢視表的對應統計資訊的自動更新統計資訊設定情況。如果在資料級别設定自動更新統計資訊為停用，那表級别也會停用。可以使用sp_autostats存儲過程修改表級别的自動更新統計資訊設定情況。繼續圍觀了解統計資訊（4/6）：自動更新統計資訊的閥值——人為更新統計資訊的重要性。
參考文章：
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/07/01/sql-server-part-3-all-about-sql-server-statistics-who-create-and-manage-the-statistics-/
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！