SQL Server 2000 建立統計

               SQL Server 2000 中有兩種基本語句會生成上述統計資訊：CREATE INDEX 首先會生成宣告的索引，然後，作為副產品，它會為組成索引的列組合建立一個統計集；CREATE STATISTICS 只為給定列或列的組合生成統計。 -Aio2i7L

另外，還有多種方法可以建立統計或索引，但歸根結底，每種方法都會發出上述兩個命令之一。 -Aio2i7L

使用 sp_createstats 為當前資料庫中所有使用者表的全部合格列（不包括影像和文字資料）建立統計。如果某個列已經有了直方圖，則不會為其建立新的直方圖。

使用 dbcc dbreindex 為指定資料庫中的表重新生成一個或多個索引。

在查詢分析器中，鍵入一個查詢，選擇“顯示執行計劃”(Show Execution Plan)，然後執行查詢。在顯示的任意圖示上單擊右鍵，並選擇“管理索引” (Manage Index)或“建立/更新統計資訊”(Create/Update Statistics)。

使用 Create Index 嚮導（在其他文章中說明）。
下面是對 pubs..authors 表執行 CREATE STATISTICS 命令的一個示例：-Aio2i7L

CREATE STATISTICS s1 ON authors (state, au_lname) WITH SAMPLE 50 PERCENT通常，按預設抽樣方式生成的統計最為理想。但有時，使用更大的樣本大小來生成統計（理想狀況下可以使用 fullscan）有助於查詢最佳化，例如給定列中的值分佈不均勻時（某些值頻繁出現，而其他值較少出現）。使用較大的樣本大小來生成統計，必須以建立統計時所需時間的延長為代價。-Aio2i7L

上面的命令建立一個兩列統計。在本例中，因為表太小，所以會忽略 SAMPLE 50 PERCENT 並執行完全掃描。抽樣主要用於避免過多掃描資料，並且隻影響具有 1024 或更多頁面 (8 MB) 的表和索引。-Aio2i7L

在 SQL Server 2000 中，建立索引的同時會為所有索引建立統計。SQL Server 在編譯查詢時自動建立單列統計。這些統計是為最佳化器必須估算密度或分發的列而建立的。這一規則有兩種例外情況：首先，當直接對錶執行操作所需的代價小於建立統計所需的代價時，不能為該表建立統計；其次，當伺服器過於繁忙（有大量正在進行的重要操作）時，也不能建立統計。-Aio2i7L

為避免長時間維護未使用過的統計，SQL Server 2000 會記錄那些自動建立的統計（僅包括那些不是建立索引的副產品的統計資訊）的使用時間。幾次自動更新之後，列統計會被放棄而不是被更新。如果將來需要，可以重新建立這些統計。更新統計與建立統計在代價方面並沒有實質性的差別。記錄使用時間的操作也不會影響使用者建立的統計。-Aio2i7L

透過執行 sp_dboption dbname, 'auto create statistics', 'OFF' 可以在資料庫級禁用自動建立統計的功能。-Aio2i7L

預設情況下，統計是在執行 CREATE STATISTICS 命令或自動建立統計時，透過對資料集進行抽樣而建立的。CREATE INDEX 總是會掃描整個資料集，因此最初建立的索引統計並不進行抽樣。CREATE STATISTICS 命令允許您透過在 WITH 子句中指定 FULLSCAN 或要掃描的資料百分比來設定樣本大小。後者被認為是一個近似值。在 UPDATE STATISTICS 命令上指定 WITH RESAMPLE 時也可以繼承上一個樣本大小。當既存在索引（透過 fullscan 統計方式建立），其他列（透過 sample 統計方式建立）上又有統計時，該方法尤其有用。隨後在 UPDATE STATISTICS 上使用 RESAMPLE 選項，將保持索引的 fullscan 統計和其他列的 sample 統計。-Aio2i7L

dbcc show_statistics 命令在 Rows Sampled 標題下顯示樣本大小。自動建立或更新的統計總是用預設的抽樣方式生成。預設的抽樣方式是按表大小的對數函式進行抽樣，這種抽樣方式的樣本大小增長緩慢。-Aio2i7L

SQL Server 查詢分析器也會監視自動建立統計的操作。AutoStats 事件位於 Object 跟蹤事件組中。定義該跟蹤時，也會選擇 Integer Data、Success 和 Object ID 列。一旦捕獲 AutoStats 事件，Integer Data 列將包含為給定表更新的統計數，Object ID 成為該表的 ID，而 TextData 列（預設情況下包含在跟蹤定義中）則包括列的名稱以及 Updated: 或 Created: 兩個字首之一。Success 列包含潛在的 Failure 指示資訊。在某些情況下，您可能會發現 AutoStats 事件不帶有任何建立或更新的統計。當 auto update statistics 處於關閉狀態時，會生成這類事件；或者，當表實際已經變更，而因為引用該變更表的查詢被最佳化而導致當時表中不存在任何索引或統計時，也會生成這類事件。-Aio2i7L

DROP STATISTICS 命令用於放棄統計，但不能放棄建立索引時附帶生成的統計。只有將索引放棄後，才能刪除其附帶生成的統計。-Aio2i7L

在 SQL Server 2000 中維護統計
在表中執行了一系列 INSERT、DELETE 和/或 UPDATE 查詢後，統計可能不反映給定列或索引中資料分佈的真實情況。如果某個表在上次建立或更新統計之後進行了大量的更新活動，那麼當 SQL Server 查詢最佳化器需要該表中某個特定列的統計時，SQL Server 會（使用 auto update statistics）透過對列中的值進行抽樣來自動更新統計。統計的自動更新由查詢最佳化操作觸發，並且只涉及查詢中所引用列的一個子集。SYSINDEXES 值中的 rowmodctr 列顯示了自上次建立或更新統計之後對錶執行的更改次數。對於每個索引和統計集，SYSINDEXES 中都會有一個對應的行（如果表中沒有群集的索引，則會有一個與堆相對應的行），SQL Server 2000 將分別維護每個索引和統計的更改次數（但在 SQL Server 7.0 中並非這樣）。在表中執行 INSERT、UPDATE 和 DELETE 查詢時，只會為 ID 為 0 或 1 的索引（一般，每個表中只有一個）遞增 rowmodctr 值。對於其他的統計和索引，它只顯示一個必須加到索引 0 或 1 的 rowmodctr 上的相對值，透過將這兩個值相加來得到該索引中已變更的行的真實數目。-Aio2i7L

這一邏輯使得在 INSERT、UPDATE 和 DELETE 查詢過程中，對每個表只需要維護一行中的 rowmodctr；同時，又能夠分別跟蹤每個索引中已變更的行。因此，當 auto update statistics 處於開啟狀態時，只更新與給定查詢所必需的索引和列對應的行。-Aio2i7L

查詢示例
在表 t1 中插入 506 行，然後建立非群集的索引 i1 和 i2。進行了這些操作之後，SYSINDEXES 表中會出現有趣的條目：-Aio2i7L

Name Indid rowcnt rowmodctr
t1 0 506 506
i1 2 506 -506
i2 3 506 -506

插入了另外 213 行後，計數為：-Aio2i7L

Name Indid rowcnt rowmodctr
t1 0 719 719
i1 2 506 -506
i2 3 506 -506

使用 UPDATE STATISTICS t1 (i1) 重新整理了索引 i1 的統計之後：-Aio2i7L

Name Indid rowcnt rowmodctr
t1 0 719 0
i1 2 506 0
i2 3 506 213

可以在不同的級別上將上面所說的 auto update statistics 特性關閉。 -Aio2i7L

在資料庫級別使用 sp_dboption dbname, 'auto create statistics', 'OFF'。也可以使用該 sp 來顯示特定資料庫的當前設定。

對錶、索引或統計使用 UPDATE STATISTICS 命令的 NORECOMPUTE 選項。

對錶或統計使用 CREATE STATISTICS 命令的 NORECOMPUTE 選項。

用 sp_autostats 來顯示和更改表、索引或統計的設定。
也可以使用 sp_dboption、UPDATE STATISTICS 或 sp_autostats 重新啟用自動更新統計，其方法與上述操作相似。-Aio2i7L

SQL Server 2000 在每個資料庫、每個索引和表的級別上分別儲存自動更新統計的設定。儘管您可以使用一條 CREATE STATISTICS 命令來開啟一個表中的所有統計，但必須透過更改給定表中所有統計和索引的設定來完成該操作。表 6 顯示了不同的資料庫、表和索引設定的組合效果。-Aio2i7L

表 6：不同的資料庫、表和索引設定的組合效果-Aio2i7L

資料庫設定
表/索引設定 Statistics Auto Update
對該物件是否有效
ON ON ON
ON OFF OFF
OFF ON OFF
OFF OFF OFF

自動更新統計一般透過對索引或表抽樣來執行。您可以透過手動執行 CREATE 和 UPDATE 統計來改變樣本大小。在這樣建立統計時，統計更新會被相同的 SQL 分析器事件替代。-Aio2i7L

統計和索引的檢視
通常，索引的檢視上不需要統計，這是因為：只有在將基礎表和索引的所有統計都附加到查詢操作之後，才會考慮替換查詢操作中的索引檢視。但是，有一點例外：如果使用 NOEXPAND 提示在 FROM 子句中直接引用檢視，就會使用統計。如果在不包含索引的檢視上使用 NOEXPAND 提示，則會生成錯誤並無法執行操作。-Aio2i7L

因為索引檢視上的統計的用途非常有限，所以不使用 sp_createstats 在索引的檢視上建立索引，也不使用 sp_updatestats 對其進行更新。auto update 和 auto create statistics 可以用於索引的檢視。但正如前面所說，僅當在查詢中透過 NOEXPAND 提示使用索引的檢視，並且 AUTO UPDATE 或 CREATE STATISTICS 選項處於開啟狀態時，最佳化器才需要這些統計，這些統計也才會被真正建立。您也可以在索引的檢視列上手動執行 CREATE STATISTICS，或在索引的檢視上使用 UPDATE STATISTICS 來更新列或索引統計。-Aio2i7L

總結
對於查詢最佳化器，資料庫中的資料統計是重要的輸入。預設情況下，SQL Server 自動建立和維護統計，而不需要使用者的參與。大多數 SQL Server 使用者不必更改預設值，就可以獲得最佳效能。SQL Server 提供了多個介面，用於更改預設的統計建立和維護的規則，但只有在預設規則不能達到最佳效能的特殊環境下，您才需要使用這些介面。-Aio2i7L

原文: