[轉載] SQLServer裡統計維護功能 (autostats)

--王成輝翻譯整理，轉貼請註明出自微軟BI開拓者www.windbi.com
--原帖地址

摘要

最新引進的統計維護功能AutoStat可能在生產系統上通過執行下面的行為產生不必要的開銷：

在繁重的生產期間開始統計更新

或者

在特定點開始大量的更新統計程式

這篇文章詳細描述你期望看到的產生autostats和在資料庫的表上執行UPDATE STATISTICS的條件。

關於SQLServer2000裡autostats更多的資訊，請檢視

注如果你使用的是SQLServer2005看下面微軟白皮書裡關於查詢優化器怎樣使用統計的資訊。

更多資訊

常規資訊

SQLServer使用基於成本的優化（CBO），所以對錶和索引提供的統計資訊非常敏感。如果沒有正確和最新的統計資訊，那麼SQLServer在為特定的查詢產生最好的執行計劃時會面臨挑戰。

SQLServer每個表上的統計維護使用下面的資訊幫助優化器做出基於成本的決定：

• 表的行數
• 表使用的頁數
•自上次統計更新以來表的鍵被更改的數量

為索引（每一個索引）儲存的另外資訊包括：

•第一列上等高的柱狀圖
•所有列字首的密度
• 平均鍵長

當一個新索引建立時索引上的統計就自動建立了。另外，現在也可以在其他列上建立和維護統計了。

為了儘可能的維護統計資訊使其最新，SQLServer引進了AutoStat功能，通過追蹤表的更改，當達到某一個更改閾值時，它能為表自動更新統計。SQLServer還引進了auto-create-statistics功能，它能為正確優化特定的查詢而使伺服器自動的產生所有需要的統計。

AutoStat觸發的時機

正如上面所述，當達到一個更改閾值（"change threshold"）時，AutoStat會為特定的表自動更新統計。系統表sysindexes的列rowmodctr用來記錄自上次更新表的統計後插入、刪除、更新的行的總數，隨著時間的推移，它可以影響查詢處理器的決策過程。這個計數器每當下面的事件發生時被更新：

•插入一行
• 刪除一行
• 更新了索引列

注：TRUNCATE TABLE不會更新rowmodctr。

表統計更新後，rowmodctr的值重新設定為0，並且更新表的統計架構版本。

更進一步，在儲存過程從快取中採用執行計劃並且計劃對統計比較敏感的情形下，統計架構版本將和目前的版本比較。如果有新的版本可用，儲存過程的計劃將被重新編譯。

自動更新統計的基本法則：
• 如果表的集的勢小於6並在tempdb資料庫裡，對錶的每6個更改就會自動更新統計。
• 如果表的集的勢大於6且小於等於500，則每500個更改就自動更新統計。
• 如果表的集的勢大於500，則當有500加上表的20％個更改時自動更新統計。
• 對於表變數，更改集的勢不會觸發自動更新統計。

注：在此嚴謹的場合，SQLServer用表的行數計算集的勢。
注：除了集的勢之外，斷言的選擇也會影響AutoStats的觸發時機。這意味著如果集的勢小於500且每500個更改之後或者如果集的勢大於500且每20％的更改之後統計不會被更新。一個按比例增加的因素（取值範圍從1到4，包括1和4）的產生依賴於選擇性、該因素的積、根據法則得到的更改行數（該行數是對於觸發AutoStats要求更改的實際行數）。

上面的法則可用下表的形式來展現：

表型別	清空條件	清空閾值	不清空閾值
永久表	<500行	更改數>=500	更改數>=500+20%*集的勢
臨時表	<6行	更改數>=6	更改數>=500+20%*集的勢
表變數	更改集的勢不會觸發AutoStats

下面有兩個例子來幫助示範這個概念：

示例1

考慮pubs資料庫裡的authors表，它有23行，2個索引。在一列au_id上有一個唯一聚集索引UPKCL_auidind，在列au_lname和au_fname上有一個非聚集的複合索引aunmind。因為這個表小於500行，AutoStat將在表資料的500個更改之後開始。更改可以是針對索引的列如au_lname或它的任何聯合的500或更多的insert、delete中的一個。

因此，你能通過追蹤隨著每次更改增加的sysindexes.rowmodctr的值來預計UPDATE STATISTICS將在什麼時候開始。當它達到或超過500時，你可以預計UPDATE STATISTICS要啟動了。

示例2

考慮另一個集的勢為1000的表t2。對於大於500行的表來說，SQLServer將在有（500+20％*集的勢）個更改時啟動UPDATE STATISTICS。按照規則，1000的20％是200，所以你可以預計在對錶做出大約700個更改之後會觸發AutoStat。

統計更新自動化

為了在AutoStat將執行時自動更新統計，你可以檢測sysindexes表，確定表更改次數何時達到觸發點。下面是基本的演算法：
if (sysindexes.rows > 500)
if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
hours) //500 change leeway
begin
disable autostats
log autostats disable
end
else
begin
stats ok
end
else
if (sysindexes.rowmodctr >= 425) //75 change leeway
begin
disable autostats
log autostats disable
end
你可以稍後按照下面排程作業：

在你強迫禁用autostat期間的所有表上執行UPDATE STATISTICS。

並且

重新啟用AutoStat，因為當UPDATE STATISTICS執行時每一個表的更改計數器會重置為0。

控制UPDATE STATISTICS是否在表上執行

當AutoStat被證明有問題的時候，最明顯的解決方法是禁用自動統計，從而讓DBA在資料庫不忙的時候自由排程UPDATE STATISTICS。你可以通過使用UPDATE STATISTICS或sp_autostats儲存過程來完成，UPDATE STATISTICS的語法如下：
UPDATE STATISTICS

...with NORECOMPUTE

sp_autostats儲存過程的語法如下：

sp_autostats , ,
stats_flag的取值為"on"或"off"。

你也可以使用sp_dboption在每個資料庫級禁用UPDATE STATISTICS或CREATE STATISTICS：
sp_dboption ,'auto update statistics',

或者

sp_dboption ,'auto create statistics',

控制UPDATE STATISTICS程式的併發數

通常，只要沒有為特定表禁用AutoStat，就不可能同時自動產生大量的UPDATE STATISTICS語句（DCR 51539已對此存檔）。不管怎樣，伺服器限制UPDATE STATISTICS程式的併發數為每處理器4個。
何時執行Autostats當依賴於統計的儲存過程重新編譯時，你可以使用跟蹤標記205來報告AutoStat的結果。這個標記將寫下面的資訊到錯誤日誌：
1998-10-15 11:10:51.98 spid9 Recompile issued : ProcName: sp_helpindex
LineNo: 75 StmtNo: 29

當跟蹤標記205開啟時，更新統計的來自跟蹤8721的AutoStat資訊也包括下面的資訊。開始資訊會在sysindexes的列RowModCnt中以大於0的值儲存。在執行UPDATE STATISTICS後，結束資訊會在列RowModCnt中以0儲存：
1998-10-15 11:38:43.68 spid8 Schema Change: Tbl Dbid: 7 Objid:
133575514 RowModCnt: 60500 RowModLimit: 60499

對於該資訊，"RowModCnt"是對錶的更改總數。"RowModLimit"是閾值，當超過這個閾值時，UPDATE STATISTICS語句將為表而執行。

開啟跟蹤標記8721也可能在AutoStat執行時將資訊存入錯誤日誌。下面是你希望看到的資訊型別的例子：
1998-10-14 16:22:13.21 spid13 AUTOSTATS: UPDATED Tbl: [authors]
Rows: 23 Mods: 501 Bound: 500 Duration: 47ms UpdCount: 2

對於該資訊，"Mods"是對錶的更改總數，"Bound"是更改閾值，"Duration"是語句UPDATE STATISTICS執行完成需要的時間，"UpdCount"更新統計的計數。

你也可以使用事件探查器來分辨UPDATE STATISTICS語句是否執行，操作步驟詳見文章《SQLServer UPDATE STATISTICS提示》。

注：如果很多統計是用AutoStat來更新的，那麼大量的資訊會寫到錯誤日誌裡。所以在生產伺服器或其他關鍵的伺服器上使用它們之前要對這些跟蹤標記進行徹底的試驗。

架構鎖

SQLServer使用兩種型別的架構鎖，當對錶進行統計更新時都要用到：
Sch-S: 架構穩定鎖
----------------------------
該鎖確定任何會話在架構元素如表或索引上有架構穩定鎖時架構元素不被刪除。
Sch-M-UPD-STATS: 架構更改鎖
-----------------------------------------
這是一個非阻塞鎖，系統用來確定在給定時刻表上只有一個自動更新統計程式在執行。
Sp_lock儲存過程將使用型別為TAB，資源為UPD-STATS、模式為SCH-M來報告該鎖。

你可以通過執行sp_lock或從syslockinfo表裡選擇來檢視這些鎖。

http://www.windbi.com/showtopic.aspx?forumid=5&topicid=38&go=prev

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-509791/，如需轉載，請註明出處，否則將追究法律責任。