mssql2005統計資訊

SQL Server 2005中的統計資料
Microsoft? SQL Server? 2005收集關於單列的統計資訊（單列統計資訊），或者一組列的統計資訊（多列統計資訊）。查詢最佳化器使用統計資訊來評估表示式的選擇性，並由此來評估一個查詢中間結果和最終結果的規模大小。好的統計資訊允許最佳化器精確地判斷不同執行計劃的成本，並選擇一個高質量的執行計劃。一個統計物件的所有相關資訊儲存在sysindexes表中一行的多個列中，統計資訊中二進位制大物件（statblob）儲存在一張內部表中。此外，可以在新的後設資料檢視sys.stats和sys.indexes中找到關於統計物件的有關資訊。

統計特性概述
SQL Server 2005在維護統計資訊方面具有許多特性。最為重要的一點就是能夠自動建立和更新統計資訊。在SQL Server 2005和SQL Server 2000中預設將啟用該功能。大約98%的SQL Server 2000安裝將啟用該功能，這也是最佳實踐。對於大多數的資料庫和應用程式而言，開發人員和管理員可以藉助自動建立和更新統計資訊的功能以提供全面且準確的關於資料的統計資訊。這允許SQL Server 2005查詢最佳化器生成一致且有效的查詢計劃，同時使開發和管理的成本維持在較低的水平。如果您希望進一步控制統計資訊的建立和更新以獲得最佳的執行計劃並管理由於統計資訊收集而產生的開銷，您可以使用手動建立和更新統計資訊的功能。

對於大吞吐量聯機事務處理應用環境而言，一個重要的新特性就是以非同步方式自動更新統計資訊。 這種方式可以顯著提高OLAP環境中的查詢響應時間。
SQL Server 2005中的統計功能允許您：
?        使用預設的抽樣比率隱式地建立和更新統計資訊。（如果開啟了自動建立和更新統計資訊功能，那麼在SELECT，INSERT，DELETE和UPDATE命令的查詢條件如WHERE和UPDATE子句中使用某一列時，將根據需要自動建立或者更新統計資訊）
?        使用期望的抽樣比率手動建立和更新統計資訊、刪除統計資訊（CREATE STATISTICS, UPDATE STATISTICS, DROP STATISTICS, CREATE INDEX, DROP INDEX）。
?        為資料庫中所有表的所有列批次建立統計資訊（sp_createstats）。
?        手動更新資料庫中所有已存在的統計資訊（sp_updatestats）。
?        列出資料庫或表中所有統計資訊物件（sp_helpstats，目錄檢視sys.stats, sys.stats_columns）。
?        顯示統計資訊物件的描述資訊（DBCC SHOW_STATISTICS）。
?        在整個資料庫級別或者單張表或單個統計物件級別啟用或禁用自動建立和更新統計資訊功能（ALTER DATABASE的AUTO_CREATE_STATISTICS和AUTO_UPDATE_STATISTICS選項，sp_autostats以及 CREATE STATISTICS和UPDATE STATISTICS的NORECOMPUTE選項）。
?        啟用或禁用非同步自動更新統計資訊（ALTER DATABASE的 AUTO_UPDATE_STATISTICS_ASYNC選項）。
此外，SQL Server Management Studio允許您透過Object Explorer檢視以圖形化方式瀏覽和管理統計資訊物件。Object Explorer在每個表物件下方的資料夾中列出所有的統計物件。

SQL Server 2005中的統計資訊有哪些新特性？
SQL Server 2005包含許多新的統計特性，這些新特性允許查詢最佳化器為各種型別的查詢選擇更佳的執行計劃，或者用於增強統計資訊的管理功能。這些增強包括：
?        字串摘要統計：為字元型別的列維護的關於字串子串頻率分佈的資訊。這些資訊可以幫助查詢最佳化器更好地評估那些使用LIKE運算子的查詢條件的選擇性。
?        非同步自動更新統計資訊：SQL Server 2005中ALTER DATABASE命令提供了新的AUTO_UPDATE_STATISTICS_ASYNC選項。預設該選項是關閉的。如果啟用該選項，SQL Server 2005將在後臺進行自動統計資訊更新，這樣查詢就不會由於統計資訊更新而被阻塞，而是繼續使用舊的統計資訊進行處理。這種方式可以顯著提高某些工作負載的響應時間。
?        計算列上的統計資訊： 可以在計算列上手動或自動地建立和更新統計資訊（SQL Server 2000僅部分支援該功能，且相關的文件也不多）
?        大物件支援：可以將資料型別為ntext，text和image，也包括新資料型別nvarchar(max), varchar(max)以及varbinary(max) 的列指定為統計列。
?        增強的統計資訊載入框架：同SQL Server 2000相比，查詢最佳化器在內部對統計資訊的載入進行了增強。如今最佳化器將載入所有必須載入的統計資訊，從而提高了最佳化的質量和效能。
?        在計算列上自動建立統計資訊功能的增強：如果查詢中包含了與某個計算列表示式等價的表示式，那麼SQL Server 2005將在需要時自動在計算列上建立統計資訊。
?        最小抽樣規模： 在收集統計資訊時最少抽樣8 M資料。
?        統計資訊數目限度的增強：每張表上允許建立統計資訊的列數增加到了2,000。此外還允許同時存在249個索引統計，即每張表上最多允許2,249個統計資訊。
?        增強的DBCC SHOW_STATISTICS輸出：如今DBCC SHOW_STATISTICS將顯示統計物件的名稱，從而避免了二義性。
?        基於列的更新計數器的自動更新統計資訊：SQL Server 2000根據表中更新的行數來決定何時更新統計資訊。如今是在列的級別上跟蹤資料變更的，這樣就可以避免在那些變更還不夠充分的列上自動更新統計資訊。
?        內部表上的統計資訊：可以在sys.internal_tables列出的所有內部表上建立統計資訊，包括XML和全文索引、服務代理佇列，以及查詢通知表。
?        DBCC SHOW_STATISTICS單一結果集輸出：DBCC SHOW_STATISTICS支援將標題、密度資訊以及直方圖作為單個結果集輸出。這樣更便於透過程式設計的方式自動處理DBCC SHOW_STATISTICS的輸出結果。
?        統計資訊最多可支援32列： 在一個統計物件中允許的列數由16列增加到32列。
?        分割槽表統計資訊：分割槽表是SQL Server 2005的新特性，並且完全支援統計資訊功能。SQL Server為每張表（而不是每個分割槽）維護直方圖資訊。
?        以並行方式進行fullscan統計資訊收集：使用fullscan方式收集統計資訊時，可以透過並行方式在無分割槽表或分割槽表上建立單個統計資訊物件。
?        當缺失統計資訊時增強的重新編譯和建立統計資訊功能：在自動建立統計資訊失敗的情況下，如果以後生成執行計劃時統計資訊是缺失的，那麼最佳化器將自動建立統計資訊並重新編譯執行計劃；因此不會持續出現統計資訊缺失的情況。更多詳細資訊請閱讀由Marathe發表的白皮書SQL Server 2005中批編譯、重新編譯，以及計劃快取的問題 。
?        增強的對空表重新編譯的邏輯和更新統計資訊功能：表中行數從0變化到大於0將導致查詢被重新編譯以及統計資訊被更新。更多詳細資訊請閱讀由Marathe發表的白皮書SQL Server 2005中批編譯、重新編譯，以及計劃快取的問題。
?        更加清晰和一致的統計資訊直方圖顯示：由於儲存直方圖之前總是先將直方圖按比例進行縮放，因此增強了DBCC SHOW_STATISTICS顯示直方圖的效果。
?        推導的資料關聯約束：透過啟用 DATE_CORRELATION_OPTIMIZATION資料庫設定，SQL Server可以維護透過外來鍵連線的兩張表的datetime列之間的關聯資訊。某些查詢可以使用這些資訊對隱含的條件進行判斷。儘管這些資訊並不直接用於查詢最佳化器進行選擇性評估或成本估算，因此從嚴格意義上來講，這些資訊並不是統計資訊，但卻與統計資訊密切相關，因為它們可以作為輔助資訊以幫助最佳化器獲得更好的查詢計劃。
?        sp_updatestats: 在SQL Server 2005中，sp_updatestats根據sys.sysindexes相容檢視中記錄的rowmodctr資訊僅對那些需要更新的統計資訊進行更新。對於那些執行在90或更高相容級別下的資料庫，sp_updatestats將保持特定索引或統計資訊物件的自動UPDATE STATISTICS配置選項。
此外統計資訊的行為還有其它一些細微的變化。特別的，sys.sysindexes表statblob列的值始終為NULL，statblob本身儲存在一張內部目錄表中。

定義
我們先定義以下這些與SQL Server 2005統計資訊相關的術語：
?        statblob：二進位制大物件統計資訊。該物件儲存在一個內部目錄檢視sys.sysobjvalues中。
?        字串摘要：對字串列的子串的頻率分佈進行彙總的一種統計資訊，用於幫助評估LIKE謂詞的選擇性。字串列的摘要資訊儲存在statblob中。
?        sysindexes：sys.sysindexes目錄檢視，包含了表和索引的相關資訊。
?        謂詞：結果為true或者false的判定條件。謂詞出現在資料庫查詢的WHERE或者JOIN子句中。
?        選擇性：滿足謂詞的行數佔輸入資料集行數的比例。更復雜的選擇性評估可用於估算連線、DISTINCT以及其它操作產生的行數。例如，SQL Server 2005估算AdventureWorks資料庫的“Sales.SalesOrderHeader.OrderID = 43659”謂詞的選擇性是1/31465 = 0.00003178。
?        基數評估：估算結果集的大小。例如：如果表T包含100,000行記錄，查詢包含了形如T.a=10的選擇謂詞，直方圖顯示T.a=10的選擇性是10%，那麼查詢要考慮的表T中滿足條件的行數，即基數評估就是10% * 100,000 = 10,000。
?        LOB: 大物件（text, ntext, image, varchar(max), nvarchar(max), varbinary(max) 型別的值）。

 

透過執行下面的語句可以在資料庫級別禁用自動建立統計資訊功能
ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF
同樣地，執行下面的語句在資料庫級別啟用自動建立統計資訊功能
ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON
建議您將該選項設定為ON。只有當您需要解決某些效能問題，例如需要為某些表指定不同於預設值的抽樣比率時，才禁用該選項。

預設情況下，當執行CREATE STATISTICS命令或者自動建立統計資訊時，是透過對資料集抽樣的方式來建立統計資訊。CREATE INDEX總是掃描整個資料集，因此最初建立的索引統計資訊是沒有抽樣的（相當於fullscan）。CREATE STATISTICS命令允許您設定抽樣規模，在WITH子句中要麼指定fullscan，要麼指定掃描資料或行數的百分比。後者作為近似值來處理。也可以在UPDATE STATISTICS命令中指定WITH RESAMPLE來繼承原有的抽樣規模。這一點對於那些在某些列有索引（最初透過fullscan統計建立的）而其他一些列上只有統計資訊（最初透過SAMPLE統計建立的）的表來說是十分重要的。在UPDATE STATISTICS上使用resample選項將為索引維持fullscan統計，為其餘的列維持抽樣統計。

對於小型表至少需要抽樣8MB的資料。如果一張表開始時很小，您使用預設的抽樣比率進行抽樣，此後表的大小增長超過了8MB，那麼當您使用resample選項更新統計資訊時，您得到的同樣還是fullscan。如果您希望預設的抽樣比率隨著表的規模而變化，則應該避免使用resample。

resample抽樣比率是在前一次統計資訊計算時根據已抽樣行數和表中總行數的函式進行計算的。由於真實的抽樣比率可能因抽樣隨機性的特點而變化，因此對於非完全掃描抽樣來說，resample只是近似於上一次的抽樣比率。如果希望反覆一致地進行抽樣，在使用UPDATE STATISTICS之前顯式地指定相同的抽樣比率而不是使用resample。

dbcc show_statistics命令在Rows Sampled heading下面顯示抽樣規模。自動建立的統計資訊，或者自動更新的統計資訊（將在下一部分描述）總是進行預設的抽樣。預設的抽樣比率是一個表規模的慢速增長函式，這樣即使是十分大型的表也可以相對快速地收集統計資訊。

當建立和更新統計資訊時，查詢最佳化其必須選擇一種存取路徑來收集統計資訊。存取路徑可以包含堆、聚簇索引，或者非聚簇索引。對於抽樣的統計資訊，最佳化器儘量避免那些對統計資訊首列進行物理排序的存取路徑，這樣做有助於提供更隨機化的抽樣，也因此導致更加精確的統計資訊。對於那些沒有對統計資訊健值作排序的存取路徑（如果存在這樣的存取路徑），則選擇其中成本最低的一種，這就是最精確的索引或者堆。對於fullscan統計，由於存取路徑的排序順序對於統計資訊的準確性無關緊要，因此成本最低的存取路徑將被選中。

SQL Server Profiler可以監視自動的統計資訊建立。Auto Stats事件屬於Performance跟蹤事件組. 當定義跟蹤時，還要同時選中Auto Stats的IntegerData，Success和ObjectID列。一旦捕獲到AutoStats事件，Integer Data包含了在指定表上建立或者更新的統計資訊的數目， Object ID為表的ID，TextData（預設包含在跟蹤定義中）包含了建立或者更新統計資訊的列名，再加上Updated: 或者Created: 字首。Success包含了Auto Stats操作成功或者失敗的標記。  需要特別指出的是，Success有三個可能的值：


有時您可能還會觀察到AutoStats事件但並沒有與之相關的統計資訊建立或更新操作發生。該事件的產生是由於auto update statistics選項被關閉，或者由於查詢引用的表上產生了大量的變更，由於查詢結構和外健約束的存在從而導致最佳化器從查詢計劃中移除所有對該表的引用。
DROP STATISTICS用於刪除統計資訊，但是不可以刪除作為建立索引的副產品而自動生成的統計資訊。這種統計資訊只有當刪除索引時才會被刪除。

在SQL Server 2005中維護統計資訊
當您在表中進行了一系列的插入，刪除和更新後，統計資訊可能無法反映出特定列或索引的真實資料分佈情況。如果SQL Server查詢最佳化器需要表中某一列的統計資訊，該表在上次建立或更新統計資訊後經歷了大量的更新活動，那麼SQL Server就會透過抽樣列值的方式自動更新統計資訊。（使用auto update statistics）。統計資訊的自動更新是由查詢最佳化器觸發的，或者透過執行一個編譯好的執行計劃而被觸發，並且只更新那些在查詢中被引用的列的子集。如果AUTO_UPDATE_STATISTCS_ASYNC為OFF，那麼在編譯查詢之前更新統計資訊，如果AUTO_UPDATE_STATISTCS_ASYNC為ON，則非同步更新統計資訊。

當一個查詢首次被編譯時，如果查詢最佳化器需要一個特定的統計資訊物件並且該物件存在，該統計資訊物件將被更新如果統計資訊物件已經過時。當執行一個查詢且該查詢的執行計劃在快取中，那麼執行計劃所依賴的統計資訊將被檢查已確認是否已經過時。如果是，執行計劃將從快取中移除，統計資訊在重新編譯查詢時被更新。如果執行計劃所依賴的統計資訊改變了，那麼執行計劃也將從快取中移除。

SQL Server 2005根據列更新計數器（colmodctrs）的變化決定是否更新統計資訊。

以下情況可以認定一個統計資訊物件已經過時：
?        如果在一張普通表上定義統計資訊，那麼該統計資訊將過時，如果：
1.        表的大小從0行增長到>0行。
2.        當收集統計資訊時，表中行數為500行或更少，此後統計資訊物件中首列的colmodctr的變更超過了500。
3.        當收集統計資訊時，表中含有500行以上的記錄，此後統計資訊物件中首列的colmodctr變更超過了500 + 收集統計資訊時表中行數的20%。
?        如果在一張臨時表上定義統計資訊，那麼在前面描述的情況下統計資訊也將過時。
表變數根本沒有統計資訊。

可以在不同的級別上關閉前面描述的auto update statistics特性。
?        在資料庫級別，使用下面的語句關閉自動更新統計資訊：
        ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF
?        在表級別，使用UPDATE STATISTICS命令或者CREATE STATISTICS命令的NORECOMPUTE選項關閉自動更新統計資訊。
?        使用sp_autostats顯示和修改表、索引、或者統計資訊物件的自動更新設定。
同樣地，使用ALTER DATABASE，UPDATE STATISTICS或者sp_autostats可以重新啟用自動更新統計資訊。
SQL Server 2005在資料庫、表、索引或者統計資訊物件級別維護自動更新統計資訊的設定。當某人使用sp_autostats命令修改了某張表上所有物件的該項設定時，SQL Server透過單獨修改表上每個統計資訊物件和索引的設定來實現。不存在直接記錄整張表自動更新統計資訊設定為ON或OFF的後設資料。
下表顯示結合不同的資料庫、表、索引設定的效果：


在資料庫級別設定的OFF是無法透過在統計資訊物件級別將自動更新統計資訊選項設定為ON來改寫的。
自動更新統計資訊始終透過對錶或索引按預設的抽樣比率進行抽樣來完成。要想顯式地設定抽樣比率，執行CREATE或者UPDATE STATISTICS。

更新統計資訊包含在與建立統計資訊相同的SQL Profiler事件中。