在理解統(tǒng)計(jì)信息（3/6）：誰創(chuàng)建和管理統(tǒng)計(jì)信息？在性能調(diào)優(yōu)中，統(tǒng)計(jì)信息的作用里我們討論了統(tǒng)計(jì)信息的自動創(chuàng)建和自動更新。我們真的需要人為維護(hù)統(tǒng)計(jì)信息來保持性能最優(yōu)？答案是肯定的，這取決與你的工作量。SQL Server只在達(dá)到閥限值時(shí)進(jìn)行統(tǒng)計(jì)信息的自動更新。當(dāng)大量的Insert/Update/Delete操作發(fā)生時(shí)，內(nèi)建的自動更新統(tǒng)計(jì)信息不能持續(xù)保證性能的最優(yōu)。

經(jīng)過一系列的Insert/Update/Delete后，統(tǒng)計(jì)信息可能不會是最新。如果SQL Server查詢優(yōu)化器在表里需要指定列的統(tǒng)計(jì)信息，自上次統(tǒng)計(jì)信息創(chuàng)建或更新后經(jīng)歷了實(shí)質(zhì)的更新活動，SQL Server會通過采樣列值自動更新統(tǒng)計(jì)信息（通過自動更新統(tǒng)計(jì)信息）。統(tǒng)計(jì)信息的自動更新由查詢優(yōu)化器或編譯好的計(jì)劃執(zhí)行來觸發(fā)，它只涉及到查詢里引用到的各個(gè)列。如果自動異步更新統(tǒng)計(jì)信息是停用的話，統(tǒng)計(jì)信息會在查詢編譯前更新，啟用的話是在查詢編譯后更新。當(dāng)統(tǒng)計(jì)信息是異步更新時(shí)，受益于觸發(fā)更新的查詢使用老的統(tǒng)計(jì)信息。對一些工作量來說，這可以提供更可預(yù)估的響應(yīng)時(shí)間，尤其是那些大表上的短時(shí)間運(yùn)行的查詢。

當(dāng)一個(gè)查詢首次編譯完成，如果優(yōu)化器需要指定對象的統(tǒng)計(jì)信息，這個(gè)統(tǒng)計(jì)信息存在的話，若已過期則自動更新統(tǒng)計(jì)信息。如果一個(gè)查詢被執(zhí)行且它的計(jì)劃在緩存里，計(jì)劃依賴的統(tǒng)計(jì)信息會被檢查是否過期，如果過期，計(jì)劃會在緩沖中移除，在查詢的重編譯時(shí)，統(tǒng)計(jì)信息會被更新。如果計(jì)劃依賴的任何統(tǒng)計(jì)信息被更新的話，計(jì)劃都會從緩存中移除。

SQL Server 2008基于列修改的計(jì)數(shù)器（colmodctrs）來決定是否更新統(tǒng)計(jì)信息：

在下列情況下，統(tǒng)計(jì)信息對象被認(rèn)為過期：

如果在常規(guī)表上定義的統(tǒng)計(jì)信息，被認(rèn)為過期的話，那么：

1. 表的大小從0行變成了大于0行（測試1）
2. 當(dāng)統(tǒng)計(jì)信息收集時(shí)，表的行數(shù)為500或更少，統(tǒng)計(jì)的第一列對象的計(jì)數(shù)器，自改變?yōu)榇笥?00時(shí)（測試2）。
3. 當(dāng)統(tǒng)計(jì)信息收集時(shí)，表的行數(shù)大于500時(shí)，統(tǒng)計(jì)的第一列對象的計(jì)數(shù)器，受表里超過500 +20%的行數(shù)而改變（測試3）。

上述描述來自微軟的MSDN，具體參見Statistics Used by the Query Optimizer in Microsoft SQL Server 2008。

前2個(gè)條件還是相當(dāng)好的，但第3個(gè)條件在處理大表時(shí)，有些時(shí)候閥值會很高，但對統(tǒng)計(jì)信息更新還是無效。例如有個(gè)表有100000條記錄，只有在200500條件記錄被修改后（update/insert），對于觸發(fā)自動更新還是無效的閥值。

我們來看個(gè)例子。

1 USE StatisticsDB2 GO3 4 DROP TABLE SalesOrderDetail5 SELECT * INTO SalesOrderDetail FROM AdventureWorks2008r2.sales.SalesOrderDetail6 CREATE INDEX ix_PRoductID ON SalesOrderDetail(ProductID)7 SET STATISTICS IO ON8 SELECT * FROM SalesOrderDetail WHERE ProductID=725

我們創(chuàng)建了SalesOrderDetail表的副本，并在上面創(chuàng)建非聚集索引，我們看下最后SELECT查詢的執(zhí)行計(jì)劃，點(diǎn)擊工具欄的顯示包含實(shí)際的執(zhí)行計(jì)劃。

優(yōu)化器選擇了索引查找和書簽查找操作作為優(yōu)化的計(jì)劃，完成這個(gè)操作需要377個(gè)邏輯讀。

salesOrderDetail表有121317條記錄，上述第3個(gè)條件如果要使統(tǒng)計(jì)信息無效的話，121317的20% =24263+500=24763條記錄需要被修改，我們用下列語句只更新5000條記錄，再次看看查詢的執(zhí)行計(jì)劃，點(diǎn)擊工具欄的顯示包含實(shí)際的執(zhí)行計(jì)劃。

1 SET ROWCOUNT 50002 UPDATE SalesOrderDetail SET ProductID=725 WHERE ProductID<>7253 SET ROWCOUNT 04 SET STATISTICS IO ON5 SELECT * FROM SalesOrderDetail WHERE ProductID=725

執(zhí)行計(jì)劃里估計(jì)行數(shù)是374，這是基于上次更新操作收集的統(tǒng)計(jì)信息。優(yōu)化器基于統(tǒng)計(jì)信息，選擇了索引查找和書簽查找作為最優(yōu)計(jì)劃。SELECT操作進(jìn)行5390邏輯讀來完成這個(gè)操作。

下一步，我們用producid值為725來更新19762條記錄。實(shí)際上我們更新24762條記錄（包含上一步5000條更新的記錄），比使統(tǒng)計(jì)信息無效的更新的記錄（24763）少1條。

1 SET ROWCOUNT 197622 UPDATE SalesOrderDetail SET ProductID=725 WHERE ProductID<>7253 SET ROWCOUNT 04 SET STATISTICS IO ON5 SELECT * FROM SalesOrderDetail WHERE ProductID=725

執(zhí)行計(jì)劃里估計(jì)行數(shù)是374，這是基于上次更新操作收集的統(tǒng)計(jì)信息。優(yōu)化器基于統(tǒng)計(jì)信息，選擇了索引查找和書簽查找作為最優(yōu)計(jì)劃。完成這個(gè)操作需要25206個(gè)邏輯讀。

現(xiàn)在我們更新再多一條記錄使統(tǒng)計(jì)信息無效。

1 SET ROWCOUNT 12 UPDATE SalesOrderDetail SET ProductID=725 WHERE ProductID<>7253 SET ROWCOUNT 04 SET STATISTICS IO ON5 SELECT * FROM SalesOrderDetail WHERE ProductID=725

（這里我跌了個(gè)跟頭，在SQL SERVER 2008R2里首次執(zhí)行，始終是下列結(jié)果：

回家吃飯還在思考這個(gè)問題，一想原因，應(yīng)該是自動創(chuàng)建統(tǒng)計(jì)信息和自動更新統(tǒng)計(jì)信息被停用的原因（上篇文章理解統(tǒng)計(jì)信息（3/6）：誰創(chuàng)建和管理統(tǒng)計(jì)信息？在性能調(diào)優(yōu)中，統(tǒng)計(jì)信息的作用代碼執(zhí)行后未還原為默認(rèn)設(shè)置），在數(shù)據(jù)庫屬性里一看，果然是False狀態(tài)，趕緊用下列語句啟用，出現(xiàn)的問題立馬消失！

1 ALTER DATABASE StatisticsDB SET AUTO_CREATE_STATISTICS ON2 ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS ON

看來計(jì)算機(jī)是最誠實(shí)可靠的，即使計(jì)算機(jī)犯了錯(cuò)，也是因?yàn)槿朔稿e(cuò)造成的！ ）

和我們預(yù)期的一樣，SELECT語句觸發(fā)了自動更新統(tǒng)計(jì)信息，計(jì)劃中的估計(jì)行數(shù)和實(shí)際行數(shù)已經(jīng)非常接近了。這可以幫助優(yōu)化器選擇更好的執(zhí)行計(jì)劃。優(yōu)化器選擇了表掃描而不是索引查找和書簽查找。SELECT操作只進(jìn)行了1495個(gè)邏輯讀來選取25137條記錄，比起25212個(gè)邏輯讀才選擇2516條記錄。在第一步，我們只更新了5000條記錄，如果統(tǒng)計(jì)信息在那個(gè)時(shí)候更新的話，優(yōu)化器可能會選擇表掃描作為最優(yōu)計(jì)劃而不是索引查找和書簽查找。那樣的話就可以只用1495個(gè)邏輯讀代替5390個(gè)邏輯讀來完成操作，這樣就會好很多。

從這個(gè)例子我們可以清楚看到，對于自動更新統(tǒng)計(jì)信息的閥值對于獲得最優(yōu)性能還是不夠好。對于大表來說會更糟。我們就需要人為去更新統(tǒng)計(jì)信息用來保證長須的最佳性能，當(dāng)然更新的頻率要看具體的工作量。

在進(jìn)行大量DML操作后，統(tǒng)計(jì)信息都會過期，在查詢計(jì)劃訪問統(tǒng)計(jì)信息前，統(tǒng)計(jì)信息都不會自動更新。更清楚的說，SQL Server會在下列情況自動更新統(tǒng)計(jì)信息：

• 查詢第一次編譯，計(jì)劃使用到的統(tǒng)計(jì)信息已經(jīng)過期
• 查詢已有存在的查詢計(jì)劃，但計(jì)劃中的統(tǒng)計(jì)信息已經(jīng)過期。

繼續(xù)圍觀理解統(tǒng)計(jì)信息（5/6）：如何檢測過期的統(tǒng)計(jì)信息。