索引統計資料

更新： 2006 年 12 月 12 日

SQL Server 2005 可讓您建立有關資料行中值分佈的統計資訊。查詢最佳化工具透過估計使用索引的成本來評估查詢，以使用此統計資訊來決定最佳的查詢計劃。

在建立統計資料時，Database Engine會針對建立統計資料的資料行排序其值，以及根據最多 200 個資料行的值來建立*「長條圖」*並以間隔分開。長條圖可指定有多少資料列完全符合每個間隔值，有多少資料列落在間隔內、值密度的計算或是在間隔內重複值的發生率。

SQL Server 2005 推出在 char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text 以及 ntext 資料行上建立的統計資料所收集的其他資訊。此資訊稱為*「字串摘要」*，可協助查詢最佳化工具估計查詢述詞在字串模式上的選擇。字串摘要可使結果集大小的估計較為精確，並可在查詢中有 LIKE 條件時，經常可獲得較佳的查詢。這包含例如 WHERE ProductName LIKE '%Bike' 及 WHERE Name LIKE '[CS]heryl'。

附註：
如果資料行範例的摘要大於Database Engine可以維護的大小時，就不會維護字串摘要資訊。例如，在具有 85,000 個資料列的資料表上，幾乎沒有相似的字串，且每個字串中有 80 個字元的唯一 varchar(80) 資料行上，使用 WITH FULLSCAN 所建立的統計資料上，將不會維謢字串摘要。若要決定是否要儲存特定統計資料物件的字串摘要，請使用 DBCC SHOW_STATISTICS (Transact-SQL)。

自動統計資料如何運作

當您建立索引時，查詢最佳化工具會自動儲存索引資料行的統計資訊。此外，當 AUTO_CREATE_STATISTICS 資料庫選項設定為 ON 時 (預設值)，Database Engine將會自動替述詞中未使用索引的資料行建立統計資料。

若資料行中的資料變更時，索引及資料行的統計資訊即成為舊資訊，並使得查詢最佳化工具會對於如何處理查詢做出較差的決定。例如，假設您建立了一個具有索引資料行的資料表，以及 1,000 項資料列的資料，而且索引資料行中的資料都是唯一值，則查詢最佳化工具會考慮讓索引資料行以較好的方式收集查詢的資料。如果更新資料行中的資料，導致有許多重複值的話，資料行就變得不再適合用於查詢。但是，由於索引的散發統計資料是根據更新前的資料，使得查詢最佳化工具還是根據過時的散發統計資料，而將它視為合適的資料行。

附註：
當查詢的執行計劃是利用 SQL Server Management Studio 以圖形顯示時，過期或遺失的統計資訊會以警告方式 (資料表名稱為紅色) 指示。如需詳細資訊，請參閱＜顯示圖形執行計劃 (SQL Server Management Studio)＞。此外，請使用 SQL Server Profiler 來指出統計資料遺失的時間，以監視「遺失資料行統計資料」事件類別。如需詳細資訊，請參閱＜Errors and Warnings 事件類別目錄 (Database Engine)＞。

將 AUTO_UPDATE_STATISTICS 資料庫選項設定為 ON (預設值) 時，每當資料表中的資料變更時，查詢最佳化工具就會定期自動更新此統計資訊。每當查詢執行計劃中所使用的統計資料之目前統計資料測試失敗時，就會初始化統計資料更新。取樣是隨機跨越資料頁的，並從統計資料所需之資料表或資料行上最小的非叢集索引取樣。每當磁碟讀取資料頁時，會使用資料頁上的所有資料列來更新統計資訊。幾乎只要有大約 20% 的資料列變更時，就會更新統計資訊。不過，查詢最佳化工具永遠都可確保能夠取樣最基本的資料列數。小於 8 MB 的資料表永遠都是以完整掃描的方式來收集統計資料。

不分析所有的資料，而是取樣資料，可將自動統計更新的成本降到最低。在某些情況下，統計資料取樣將無法精確地描繪出資料表中的資料。當使用 UPDATE STATISTICS 陳述式中的 SAMPLE 及 FULLSCAN 子句以手動方式一個資料表一個資料表的更新統計資訊時，您可以控制取樣的資料量。FULLSCAN 子句可以設定要掃描資料表中的所有資料以收集統計資料，而 SAMPLE 子句可以用來設定要取樣的資料列百分比，或是要取樣的資料列數目。

非同步的統計資料更新

初始化過時統計資料更新的查詢，必須在編譯和傳回結果集之前等待統計資料更新。這可能導致無法預期的查詢回應次數，並造成具有彙總逾時的應用程式失敗。

在 SQL Server 2005 中，資料庫選項 AUTO_UPDATE_STATISTICS_ASYNC 提供非統計資料的更新。當此選項設定為 ON 時，查詢不會在編譯前等待統計資料更新。而是，將逾時統計資料放置在查詢中，以利工作者執行緒在背景處理序中更新。查詢和任何其他並行查詢使用現有的逾時統計資料來立即編譯。因為更新統計資料沒有延遲，所以查詢回應時間是可預測的；不過，逾時統計資料可能會造成查詢最佳化工具選擇較沒有效率的查詢計劃。若在更新的統計資料後面所啟動的查詢已準備好，就會使用這些統計資料。這可能造成與較舊的統計資料版本相依的快取計劃重新編譯。如果同一個明確使用者交易中出現任何資料定義語言 (DDL) 陳述式，例如 CREATE、ALTER 和 DROP 陳述式，便無法進行非同步統計資料更新。

AUTO_UPDATE_STATISTICS_ASYNC 選項是在資料庫層級設定，並決定資料庫中所有統計資料的更新方法。它只適用於統計資料更新，而無法用於非同步建立統計資料。將此選項設定為 ON 是沒有用的，除非 AUTO_UPDATE_STATISTICS 也設定為 ON。AUTO_UPDATE_STATISTICS_ASYNC 選項預設為 OFF。如需設定此選項的詳細資訊，請參閱＜ALTER DATABASE (Transact-SQL)＞。

將資料庫設為 SINGLE_USER 之前，請先確定 AUTO_UPDATE_STATISTICS_ASYNC 選項是否設為 OFF。當設為 ON 時，更新統計資料的背景執行緒會取得資料庫連接，而您就無法以單一使用者模式存取資料庫。如果選項設為 ON，請執行下列工作：

1. 將 AUTO_UPDATE_STATISTICS_ASYNC 設為 OFF。
2. 查詢 sys.dm_exec_background_job_queue 動態管理檢視，檢查是否有作用中的非同步統計資料作業。
3. 如果有作用中的作業，請等待作業完成，或使用 KILL STATS JOB 手動終止作業。

最佳作法考量

當下列字元適用於您的應用程式時，您應該考慮將 AUTO_UPDATE_STATISTICS_ASYNC 選項設定為 ON：

• 遇到用戶端要求逾時，該逾時是由等待更新的統計資料之一或多個查詢所造成。
• 需要可預測的查詢回應時間，即使必須犧牲偶爾執行查詢，但查詢計劃因為過時的統計資料而較無效率之成本。

檢視非同步的更新統計資料屬性

若要檢視 AUTO_UPDATE_STATISTICS_ASYNC 選項的 ON 或 OFF 狀態，請從 sys.databases 目錄檢視選取 is_auto_update_stats_async_on 資料行。如需詳細資訊，請參閱＜sys.databases (Transact-SQL)＞。

若要檢視統計資料是在佇列中等待更新或是正在更新中，請使用 sys.dm_exec_background_job_queue 動態管理檢視。對於統計資料，object_id1 資料行會顯示資料表或檢視識別碼，而 object_id2 資料行則會顯示統計資料識別碼。請使用 sys.dm_exec_background_job_queue_stats 動態管理檢視來檢視所有背景工作佇列的彙總統計資料，例如工作要求等待執行的數目、失敗要求的數目，以及先前提交要求的彙總執行時間。

停用自動統計資料

您可以依照下列方式停用特定資料行或索引的自動統計資料產生：

• 使用 sp_autostats 系統預存程序。
• 使用 CREATE INDEX 陳述式的 STATISTICS_NORECOMPUTE 子句。
• 使用 UPDATE STATISTICS 陳述式的 NORECOMPUTE 子句。
• 使用 CREATE STATISTICS 陳述式的 NORECOMPUTE 子句。
• 使用 ALTER DATABASE 陳述式將 AUTO_CREATE_STATISTICS 與 AUTO_UPDATE_STATISTICS 資料庫選項設成 OFF。如需詳細資訊，請參閱＜設定資料庫選項＞。

如果您指示Database Engine不要自動維護統計資料，那麼您必須手動更新統計資訊。

附註：
如果不指定 NORECOMPUTE 子句，UPDATE STATISTICS 陳述式將重新啟用自動統計更新。

手動建立和更新統計資料

藉由使用 sp_createstats 系統預存程序，可以用一個陳述式在目前資料庫之所有使用者資料表的所有適當資料行上建立統計資訊。可以使用 CREATE STATISTICS 陳述式在特定資料表或檢視資料行上建立統計資料，並使用 UPDATE STATISTICS 陳述式來更新。在資料表或檢視上可以建立與索引不相依的最大統計資料數目是 2000。適合作為索引鍵的任何資料行或資料行組合將適用於統計資料，以下例外也適合：

• 可以指定大型物件類型資料行，除了 xml 以外。可以指定 varchar(max)、nvarchar(max)、varbinary(max)、image、text 以及 ntext 類型。
• 結合資料行值允許的大小上限可以超過加諸於索引鍵值的 900 位元組限制。

當您不再需要保留或維護產生於資料行的統計資訊時，即可將資訊刪除。

手動建立統計資料可讓您建立包含多個資料行密度的統計資料。這些是資料行組合的平均重複數目。例如，一個包含 WHERE a = 7 and b = 9 子句的查詢。

同時在兩個資料行 (a、b) 手動建立統計資訊可以讓Database Engine產生較好的查詢估計，因為統計資料也包含組合資料行 a 與 b 之區分值的平均數。

若要在資料行上建立統計資訊

CREATE STATISTICS (Transact-SQL)

若要在所有使用者資料表中所有適合的資料行上建立統計資訊

sp_createstats (Transact-SQL)

若要手動更新統計資訊

UPDATE STATISTICS (Transact-SQL)

若要檢視資料表的統計資訊

DBCC SHOW_STATISTICS (Transact-SQL)

若要卸除資料行的統計資訊

DROP STATISTICS (Transact-SQL)

在將資料庫更新至 SQL Server 2005 後使用統計資料

當從 SQL Server 的舊版，將資料庫升級至 SQL Server 2005，所有舊版的統計資料都會視為過時。因此，在第一次使用時，在 AUTO_UPDATE_STATISTICS 資料庫選項下適合更新的統計資料會使用預設的取樣率來更新。此功能具有重要的優點，而且您通常不必採取任何動作。不過，如果統計資料是在 SQL Server 舊版中使用 FULLSCAN 或其他高取樣率來手動計算，或是如果所取樣的資料表大於 8 MB，且資料散發不是隨機的，就有可能造成統計資料比較不精確，但這種情形不常見。實際上，當 AUTO_UPDATE_STATISTICS 發生時，對於大於 8 MB 的資料表使用 FULLSCAN 計算統計資料，永遠都會發生取樣率減少的情況。初始統計資料的更新有可能發生在更新至 SQL Server 新版本之後的初期。

將統計資料更新至 SQL Server 2005 格式的優點在於，SQL Server 2005 特定取樣率的統計資料通常比 SQL Server 2000 及更舊版的品質還要佳。另外，SQL Server 2005 會為字元資料行建立特殊的字串摘要統計資料，如先前所述。如需 SQL Server 2005 中統計資料的詳細資訊，請參閱此 Microsoft 網站。

最佳作法考量

在大部分的情況下，更新資料庫後並不需要針對統計資料採取任何特殊的行動。然而，如果您擁有大型資料庫且對效能的要求很高，我們建議您在更新後，執行 sp_updatestats (Transact-SQL) 加上 RESAMPLE 選項。這將可保留最早的取樣率並將所有的統計資料更新至最新的格式。請注意在索引建立期間所建立的統計資料是使用 FULLSCAN 取樣率所建立。這些統計資料以及其他的 FULLSCAN 統計資料，由於 AUTO_UPDATE_STATISTICS 的關係，在更新時都使用預設的取樣率。如果您不想執行 sp_updatestats 來更新所有的統計資料，請考慮使用 UPDATE STATISTICS，在資料庫更新後，於索引及其他具有 FULLSCAN 取樣率的 FULLSCAN 統計資料上選擇性地更新統計資料。

請參閱

概念

最佳化索引

其他資源

CREATE INDEX (Transact-SQL)
查詢微調
sp_autostats (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄