SQL Server 中的統計的維護功能 (AutoStats)

摘要

統計的維護功能，而 AutoStat，可能會產生不必要的額外負荷，生產系統上，執行下列動作之一：

在生產系統負載沈重時進行統計值更新初始化。 -或者-
在時間內初始化極大量的更新統計資料處理程序，在指定的點。

這篇文章會告訴您，您可以預期會看見 autostats 產生以及更新統計資料對資料庫中資料表所執行的條件。在 SQL Server 2000年中的 autostats 的相關資訊，請參閱統計資料使用中 Microsoft SQL Server 2000年查詢最佳化器] 在下列 MSDN 網站：

http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx 注意：如果您使用 Microsoft SQL Server 2005年時，請參閱下列 Microsoft 白皮書，如需有關如何在 SQL Server 2005 中的查詢最佳化器使用統計資料資訊：

http://technet.microsoft.com/en-us/library/cc966419.aspx

其他相關資訊

一般資訊

SQL Server 會使用成本型最佳化器可以極為敏感所提供的資料表和索引的統計資訊。如果沒有正確且最新的統計資訊，SQL Server 可以很難判斷特定查詢的最佳執行計劃。在 SQL Server，成本為基礎的決策過程包括協助您最佳化器中的每個資料表上維護的統計資料:

在資料表中的資料列數目。
使用表所列的分頁數目。
索引鍵資料表的統計資料上次更新之後所做的修改數目。

額外的資訊儲存的索引，包括（每個索引）：

在第一欄上的相等高度色階分佈圖。
在所有的資料行前置詞的密度。
平均的金鑰長度。

每當建立新的索引時，會自動建立索引的統計資料。此外，也可建立和維護其他資料行的統計資料。若要維護盡可能一樣是最新的統計資訊，SQL Server 引入 AutoStat，，透過 SQL Server 監視的資料表修改即能有特定變更臨界值時，自動更新資料表的統計資料已到達。此外，SQL Server 會引入自動-建立的統計資料，而造成伺服器自動產生正確的特定的查詢最佳化所需的所有統計資料。

判斷 AutoStat 產生即將

如上所述，AutoStat 時，自動更新特定資料表的統計資料已經到達「變更閾值」。 Sysindexes.rowmodctr資料行中維護變動合計的資料表，可能會影響經過一段時間的查詢處理器決策程序的所有修改。這個計數器會更新每次任何下列的事件發生時：

是由單一資料列插入。
是由單一資料列刪除。
進行索引的資料行來更新。

附註截斷資料表並不會更新rowmodctr。已更新統計資料的資料表之後, rowmodctr值會重設為0，而資料表的統計資料的結構描述版本會更新。此外，在其中預存程序的執行計劃來自快取，且該計劃很容易受到統計資料的情況下，統計資料的結構描述版本比較目前的版本。如果沒有可用的新的統計資料，都會重新編譯預存程序的計劃。自動更新統計資料的基本演算法是：

如果資料表基數是小於 6 而在tempdb資料庫中，資料表的資料表每 6 修改，就會自動更新。
如果資料表的基數大於 6，但小於或等於 500，就會更新每 500 的修改。
如果基數的資料表大於 500，更新統計資料時（500 + 20%的表格）發生過任何變更。
資料表變數為基數的變更不會觸發自動更新統計資料。

附註在這麼嚴格說來，SQL Server 會計算資料表中的資料列數目為基數。附註除了 [基數] 述詞的選擇性也會影響 Autostats 產生。這表示，統計資料可能不會更新之後每 500 的修改，如果基數為小於 500，或變更每 20%如果基數大於 500。調因素（這個值的範圍從 1 到 4）會產生，取決於選擇性，且此一因素和取得自演算法的變更數目的產品會修改 AutoStats 產生所需的實際數目。資料表的形式將摘要說明上述的演算法： _________________________________________________________________________________ Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty _________________________________________________________________________________ Permanent | < 500 rows | # of Changes >= 500 | # of Changes >= 500 + (20% of Cardinality) ___________________________________________________________________________ Temporary | < 6 rows | # of Changes >= 6 | # of Changes >= 500 + (20% of Cardinality) ___________________________________________________________________________ Table Variables | Change in cardinality does not affect AutoStats generation. ___________________________________________________________________________ 下列兩個範例說明示範這個概念。

範例 1

請考慮 authors 資料表在pubs資料庫中，包含 23 的資料列，並有 2 的索引。唯一的叢集的索引， UPKCL_auidind，在一個欄位中， au_id，編製索引，並已建立複合的非叢集的索引， aunmind， au_lname和au_fname的資料行上。此表格包含少於 500 個資料列，因為發生 500 資料表資料的變更之後，就會開始 AutoStat。所做的變更可以是 500，或多個插入、刪除、變更為一個索引的資料行，例如 au_lname 或這些項目的任何組合。因此，可以預測時將啟動更新統計資料，藉由監看的sysindexes.rowmodctr值，會在每次更新時遞增。當它達到 500 時，您可以預期啟動更新統計資料。

範例 2

請考慮第二個資料表，而t2，有的基數為 1000。對於具有超過 500 個資料列的資料表，SQL Server 會更新統計資料時（500 + 20%）有所變更。進行運算，1000 的 20%是 200，，所以您可以預期會看見在大約 700 修改過後的表格之後開始的 AutoStat。

自動化 AutoStats 判斷

若要自動化判定的 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

您可以稍後排定執行下列工作：

您就不得不白天停用對所有資料表執行更新統計資料。 -以及-
因為每個資料表的修改計數器將已被重設為 0 執行更新統計資料時，請重新啟用 AutoStat。

控制是否對資料表執行更新統計資料

這個問題，最明顯的解決方案時 AutoStat 已證明有問題，是停用自動統計資料產生，藉此保留自由在較不干擾性的時間排定更新統計資料的資料庫管理員。您可以使用 [更新統計資料的陳述式或sp_autostats預存程序來執行這項操作。更新統計資料的陳述式的語法是：

   UPDATE STATISTICS <table>...with NORECOMPUTE

Sp_autostats 預存程序的語法是：

sp_autostats <table_name>, <stats_flag>, <index_name> 其中< stats_flag >是"on"或"off"。您也可以使用sp_dboption ，若要停用自動更新統計資料或每個資料庫層級上的 [建立統計資料的發生：

sp_dboption < dbname 引數>，'自動更新統計資料，' < 上 | 關閉 >-OR- sp_dboption < dbname 引數 >，'自動產生統計資料' < 上 | 關閉 >

控制並行更新統計資料的處理序數目

目前，缺乏停用特定資料表的 AutoStat，就不可能設定正在並行執行的自動更新統計資料陳述式數目。伺服器，不過，限制為 4，每個處理器的並行更新統計資料處理程序數目。

決定當 Autostats 在正在執行

您可以使用追蹤旗標 205 報告統計資料相關的預存程序會因為 AutoStat 而重新編譯時。此追蹤旗標會寫入錯誤記錄檔的下列訊息：

重新編譯發行 1998 年 10 月 15 11:10:51.98 spid9: ProcName: sp_helpindex LineNo: 75 StmtNo: 29

當啟用追蹤旗標 205 時，下列的訊息也會更新統計資料時的 8721 的 AutoStat 訊息括號。可以區分括弧的開啟郵件，RowModCnt 值，該值將會是大於0。右括號之後更新統計資料中，會有 RowModCnt 值為0:

1998 年 10 月 15 11:38:43.68 spid8 結構描述變更： Tbl Dbid: 7 Objid: 133575514 的 RowModCnt: 60500 的 RowModLimit: 60499

索取此郵件中，"RowModCnt"是修改資料表的總數。 "RowModLimit"是臨界值，超過時，會導致資料表更新統計資料陳述式執行。您也可啟用追蹤旗標 8721，它會將傾印到錯誤記錄檔的資訊，在執行 AutoStat 時。您可以預期會看見的訊息類型的範例如下：

1998-10-14 16:22:13.21 spid13 AUTOSTATS：更新 Tbl: [作者] 資料列數： 23 遊戲外掛： 501 繫結： 500 的持續期間： 47ms UpdCount: 2

索取此郵件中，"遊戲外掛"是修改資料表的總數、「結合」修改臨界值、「工期」是完成此項目，更新統計資料的陳述式所需的時間量現況"UpdCount"更新的統計資料的計數。您也可以使用 SQL Server Profiler 來識別在執行更新統計資料的陳述式時。若要這樣做，請執行下列步驟：

在 [程式碼剖析工具] 功能表中，按一下 [工具]，然後按一下選項。
在 [一般] 索引標籤中，請移至事件，，，然後選取 [所有事件類別。
定義新的追蹤，並在 [事件] 下選取 [其他，選取 [自動更新統計資料的子事件。

注意： 如果 AutoStat 所要更新許多的統計資料，許多訊息可以寫入錯誤記錄檔中。徹底實驗這些追蹤旗標才加以用在任何生產或否則重要的伺服器上。

結構描述鎖定

SQL Server 會採用兩種類型的結構描述鎖定時它會更新資料表的統計資料, 所採取的這兩種： Sch-S: Schema Stability Lock ---------------------------- This lock ensures that a schema element, such as a table or index, will not be dropped while any session holds a schema stability lock on the schema element. Sch-M-UPD-STATS: Schema Modification Lock ----------------------------------------- This is a non-blocking lock that is used by the system to ensure that only one automatic UPDATE STATISTICS process is run against a table at any given point in time. The sp_lock stored procedure will report this lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M. 藉由執行 sp_lock 或從syslockinfo資料表中選取，您可以檢視這些鎖定。