章立民研究室

統計資訊

所有的 索引都會擁有統計資訊（Statistical Information），統計資訊是用來描述索引中之鍵值的辨識度與分佈狀況。於處理查詢時，查詢最佳化工具（Query Optimizer）會使用統計資訊來決定該採用資料表掃瞄或是利用某一個索引來提取資料，也就是說，統計資訊能夠幫助SQL Server決定出一個最佳的查詢策略，以便以最有效率的方式執行查詢。

統計資訊是查詢最佳化工具最佳化查詢的依據，統計資訊的精確度愈 高，最佳化的效果愈好。每當您建立索引時， SQL Server 會自動替索引之鍵值欄中的資料建立統計資訊，並將它儲存起來，然而問題是，隨著資料表被新增、修改、與刪除資料記錄，索引之鍵值欄中的資料亦會跟著異動， 這將使得統計資訊成為過時，而造成查詢最佳化工具所做出的查詢策略不是最正確的。別緊張，SQL Server 會視資料表的異動情況「自動」去更新統計資訊，不過為了避免因更新統計資訊的頻率過高而造成系統效率低落，SQL Server 並不是資料表一有更新索引之鍵值欄的異動時，就去更新統計資訊。實際的做法是，資料異動的頻率與總量將決定統計資訊何時會被自動更新。比方說，如果資料表 擁有 10,000 筆資料記錄，而其中有 2,000 筆資料記錄的鍵值欄被修改，2,000 筆資料記錄顯然佔整個資料表極大的比重，所以此時統計資訊就會自動被更新；但是如果資料表擁有 10,000,000 筆資料記錄，則就算有 1,000 筆資料記錄的鍵值欄被修改，也會因為 1,000 筆資料記錄佔整個資料表極小的比重，使得 SQL Server 不會自動去更新統計資訊。一般來說，大約只要有 20% 的資料記錄被修改，SQL Server 就會自動更新統計資訊。

請注意：
您於 Transact-SQL 查詢編輯器中以圖形方式顯示某一個查詢的執行計劃時，若發現資料表的名稱以紅色文字顯示，表示其索引的統計資訊已經過時或遺失了。

以人為方式建立統計資訊

如前所述，SQL Server 會自動替索引的鍵值欄建立與維護統計資訊。事實上，SQL Server 允許您以人為方式自行替資料表中一個或一個以上的欄位建立統計資訊，而且不管這些欄位是否為索引的鍵值欄皆可。或許您會問，這樣做的目的何在呢？原因如下所示：

■ 大家必須瞭解一個事實，如果您建立的是一個多重鍵值索引，則 SQL Server 只會替欄位組合中的第一個欄位建立與維護統計資訊。如果您想替多重鍵值索引之第一個鍵值欄以外的欄位建立統計資訊，必須自行以人為方式來建立。

比方說，如果您替 Name 與 PhoneNumber 兩欄位的組合建立一個多重鍵值索引，則 SQL Server 只會替 Name 欄位建立統計資訊，如果您想替 PhoneNumber 欄位建立統計資訊，必須以人為方式來完成。

■ 於實務系統中，我們不可能替每一個欄位建立索引，但是如果某一個未建立索引的欄位經常使用於查詢中，應該替此欄位建立統計資訊。

縱上所述可以瞭解，自行替特定的欄位建立統計資訊，無非是希望在不增加系統負荷與降低索引之維護成本的前提下，讓查詢最佳化工具較有機會建立一個較佳的執行計劃。

欲以人為方式建立統計資訊，請使用語法如下的 CREATE STATISTICS 陳述式：

CREATE STATISTICS statistics_name
ON { table∣view } ( column [ ,... ] )
  [ WITH [ FULLSCAN│SAMPLE number { PERCENT∣ROWS } [ , ] ]
    [ NORECOMPUTE ]
  ] [ ; ]

語法中的 statistics_name 用來指定所要建立之統計資訊的名稱；table∣view 引數係指定其欄位要建立統計資訊之資料表或檢視表的名稱。請特別注意，這個檢視表必須要有叢集索引，才能讓 SQL Server 建立統計資料。( column [ ,... ] )  引數則是指定要建立統計資訊之欄位的名稱，如果要替多個欄位建立統計資訊，請將它們以逗號分隔，但是請注意，SQL Server 不允許您替 xml 資料型別的欄位建立統計資訊，但會特別替 char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text 與 ntext 資料型別的欄位建立「字串摘要」統計資訊，來讓查詢陳述式有使用 LIKE 運算子時，有更好的查詢效率。

統計資訊其實是去分析資料表的資料記錄，然後歸納其結果，關鍵在於，要去分析多少筆資料記錄呢？基本上，您有下列兩種選擇：

■ 如果您希望去分析資料表的所有資料記錄，請加入關鍵字 FULLSCAN。分析所有的資料記錄雖然能取得最完整且正確的統計資訊，但是需要較長的時間來完成。

■ 另外一種做法，則是從資料表隨機取樣多少筆資料記錄或多少百分比的資料記錄來分析。如果您要隨機取樣特定筆數的資料記錄，請加入引數 SAMPLE number ROWS（例如：SAMPLE 20 ROWS）；如果您要隨機取樣特定百分比的資料記錄，請加入引數 SAMPLE number PERCENT（例如：SAMPLE 20 PERCENT）。

如果您沒有指定要使用 SAMPLE 或 FULLSCAN 的作法時，SQL Server 會自動去計算一個要取樣分析的筆數是多少。

關鍵字 NORECOMPUTE 用來設定 SQL Server 是否要自動去更新您自行建立的統計資訊。如果您沒有加入關鍵字 NORECOMPUTE，則爾後欄位內容若有異動，SQL Server 會自動去更新統計資訊；如果您加入關鍵字NORECOMPUTE，則即使爾後欄位內容有異動，SQL Server 也不會去更新統計資訊。建議您除非必要，請勿加入關鍵字 NORECOMPUTE。

最後要提醒大家，只有資料表的擁有者有權使用 CREATE STATISTICS 陳述式替資料表建立統計資訊，而且不論資料表是否擁有資料記錄，皆可執行此項作業。

程式範例 1

以下的程式碼表示從資料表 Vfpman 隨機取樣 20% 的資料記錄來替 Name 與 Tele 欄位建立一個名為 NameTele 的統計資訊：

CREATE STATISTICS NameTele
  ON Vfpman (Name, Tele)
  WITH SAMPLE 20 PERCENT;

程式範例 2

以下的程式碼表示從資料表 Customers 取樣所有的資料記錄來替 CompanyName 與 ContactName 欄位建立一個名為 CCName 的統計資訊，而且設定 SQL Server 不去自動維護與更新此一統計資訊：

CREATE STATISTICS CCName
  ON Customers (CompanyName, ContactName)
  WITH FULLSCAN, NORECOMPUTE;

以人為方式更新統計資訊

不論是 SQL Server 自動替索引之鍵值欄建立的統計資訊，或是您自行以 CREATE STATISTICS 陳 述式建立的統計資訊，SQL Server 都會自動去維護與更新。如前所述，當SQL Server 發現資料表有較大程度的異動時，便會自動去更新統計資訊，問題在於，SQL Server 自動去更新統計資訊時，係隨機取樣部分資料記錄來分析，然而在某些狀況下，隨機取樣部分資料記錄可能無法很正確地歸納出資料的特性。如果您想要自行控制於 更新統計資訊時，必須取樣多少筆資料記錄，則應採取人為方式來更新統計資訊。此外，在我們針對資料表執行 TRUNCATE TABLE 陳述式，亦或是索引之鍵值欄中的資料被大量新增、修改、與刪除之後，想要確實立即更新統計資訊，亦需以人為方式來完成。

請注意：
如果資料表的大小在 8 MB 之下，SQL Server 於自動更新統計資訊時，會取樣資料表的所有資料記錄。

一般來說，如果您決定要自行維護與更新某一個欄位或索引的統計資訊，通常會先設定當欄位或索引之鍵值欄有任何異動，SQL Server 不會自動去更新統計資訊。欲設定 SQL Server 不自動去更新統計資訊，可視情況採用下列方法：

■ 於 CREATE INDEX 陳述式中加入關鍵字 STATISTICS_NORECOMPUTE 或指定 STATISTICS_NORECOMPUTE=OFF。

■ 於「索引屬性」視窗的「選項」選取頁面中，取消勾選核取方塊 □ 自動重新計算統計資料(A)。

■ 於「索引/索引鍵」視窗中，將「資料表設計工具」分類項目中的「重新計算統計資料」設定成否。

■ 於 CREATE STATISTICS 陳述式中加入關鍵字NORECOMPUTE。

■ 於 UPDATE STATISTICS 陳述式中加入關鍵字 NORECOMPUTE。

■ 在「資料庫屬性」視窗的「選項」選取頁面中，將「自動」分類項目中的「自動更新統計資料」與「自動建立統計資料」設定成 False。

■ 使用系統預存程序 sp_autostats。

一旦您設定 SQL Server 不會自動去更新統計資訊，就必須自行適時使用語法如下的 UPDATE STATISTICS 陳述式去更新統計資訊：

UPDATE STATISTICS { table∣view }
  [ index│(statistics_name [ ,...n ]) ]
  [ WITH [ [FULLSCAN]│SAMPLE number {PERCENT│ROWS} ] ]
  [ [ , ] [ALL│COLUMNS│INDEX ]
  [ [ , ] NORECOMPUTE ]
  ] [ ; ]

語法中的 table∣view 引數係指定您欲替其索引或欄位更新統計資訊之資料表或檢視表的名稱。如果您要更新某一個索引的統計資訊，請使用 index 引數指定此索引的名稱；如果您要更新先前使用 CREATE STATISTICS 陳述式所建立的一或多個統計資訊，請使用 (statistics_name [ ,...n ]) 引數來指定。

如果您希望更新統計資訊時，取樣資料表所有的資料記錄，請加入引數 WITH FULLSCAN；如果您希望更新統計資訊時，取樣資料表特定百分比的資料記錄，請加入引數 WITH SAMPLE number PERCENT；如果您希望更新統計資訊時，取樣資料表特定筆數的資料記錄，請加入引數 WITH SAMPLE number ROWS。值得一提的是，如果 SQL Server 發現您所要求取樣的資料記錄太少，它會自動取樣較足夠的資料記錄。

如果您並未加入 index 或 (statistics_name, [. . .n]) 引數，則可以使用 ALL│COLUMNS│INDEX 引數指定 UPDATE STATISTICS 陳述式去更新哪一類的統計資訊。如果您加入關鍵字 ALL，則所有欄位與索引的統計資訊都會被更新；如果您加入關鍵字 COLUMNS，則所有欄位的統計資訊都會被更新；如果您加入關鍵字 INDEX，則所有索引的統計資訊都會被更新。請注意，如果您並未加入 index 或 (statistics_name, [. . .n]) 引數，而且也沒有加入關鍵字 ALL、COLUMNS 或 INDEX，則所有欄位與索引的統計資訊都會被更新。

如果您加入關鍵字 NORECOMPUTE，則本此所更新的統計資訊自此之後將不會被 SQL Server 自動更新。爾後如果又希望此統計資訊會自動被更新，只需再次執行沒有加入關鍵字 NORECOMPUTE 的 UPDATE STATISTICS 陳述式，或是使用系統預存程序 sp_autostats。

範例

在以下的執行步驟中，將示範如何替資料表建立索引、替特定的欄位建立統計資訊、並以人為方式更新統計資訊。

1. 以下的程式碼替資料表 Foxman 建立兩個索引，其中 IX_Name_Tele_Foxman 索引的統計資訊會由 SQL Server 自動維護與更新，而 IX_Birthday_Foxman 索引的統計資訊則不會由 SQL Server 自動維護與更新：

CREATE INDEX IX_Name_Tele_Foxman ON Foxman(name,tele);

CREATE INDEX IX_Birthday_Foxman ON Foxman(birthday)
  WITH STATISTICS_NORECOMPUTE;

2. 以下的程式碼係從資料表 Foxman 取樣 50% 的資料記錄來替欄位 tele 建立一個名為 Tele_Statistics 的統計資訊，並設定它不會被自動更新：

CREATE STATISTICS Tele_Statistics ON Foxman (tele)
  WITH SAMPLE 50 PERCENT,NORECOMPUTE;

3. 假設我們現在要取樣資料表 Foxman 所有的資料記錄來更新它所有既存的統計資訊，請執行下列程式碼：

UPDATE STATISTICS Foxman WITH FULLSCAN;

4. 假設我們現在要取樣資料表 Foxman 百分之五十的資料記錄來更新其索引  的統計資訊，請執行下列程式碼：

UPDATE STATISTICS Foxman IX_Birthday_Foxman
  WITH SAMPLE 50 PERCENT;

5. 假設我們現在要取樣資料表 Foxman 所有的資料記錄來更新步驟二所建立的統計資訊 Tele_Statistics，請執行下列程式碼：

UPDATE STATISTICS Foxman (Tele_Statistics)
  WITH FULLSCAN;

6. 以下的程式碼表示取樣資料表 Foxman 五百筆的資料記錄去更新索引 IX_Name_Tele_Foxman 的統計資訊，並將它設定成不會由 SQL Server 自動維護與更新：

UPDATE STATISTICS Foxman IX_Name_Tele_Foxman
  WITH SAMPLE 500 ROWS,NORECOMPUTE;

檢視統計資訊

如果您想要檢視某個索引或先前以人為方式建立之統計資訊目前的內容，請使用語法如下的 DBCC SHOW_STATISTICS 陳述式：

DBCC SHOW_STATISTICS ( 'table'∣' view', target )
[ WITH [ NO_INFOMSGS ]
  [ STAT_HEADER∣DENSITY_VECTOR∣HISTOGRAM ] [ , n ]
] [ ; ]

語法中的 'table'∣'view' 是資料表或檢視表的名稱，target 則是索引或人為方式建立之統計資訊的名稱。如果沒有加上關鍵字 WITH NO_INFOMSGS，當 DBCC SHOW_STATISTICS 陳述式執行完畢之後，您會在「結果」窗格（當工具列中的「以文字顯示結果」保持凹陷的狀態）或「訊息」窗格（當工具列中的「以方格顯示結果」保持凹陷的狀態）最後一行看到如下的訊息：

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請聯絡您的系統管理員。

加上引數 STAT_HEADER∣DENSITY_VECTOR∣HISTOGRAM 將不會顯示某些統計資料。我們的建議是不要加上這些引數，避免遺漏部分的統計資料。

比方說，我們想要檢視前一個段落替資料表 Foxman 的 Tele 欄位所建立之統計資訊 Tele_Statistics 目前的內容，請執行下列程式碼：

DBCC SHOW_STATISTICS ('foxman',Tele_Statistics);

又例如，我們想要檢視前一個段落替資料表 Foxman 所建立之索引 IX_Name_Tele_Foxman 的統計資訊內容，請執行下列程式碼：

DBCC SHOW_STATISTICS ('foxman',IX_Name_Tele_Foxman);

DBCC SHOW_STATISTICS 以文字顯示結果所傳回之統計資訊類似圖表 2 所示。

圖表 2

刪除統計資訊

如果您想要刪除先前以 CREATE STATISTICS 陳述式建立的統計資訊，請執行語法如下的 DROP STATISTICS 陳述式：

DROP STATISTICS table.statistics_name∣view.statistics_name [ ,...n ] [ ; ]

範例

以下的程式碼示範如何建立統計資訊，然後再將它們刪除：

/* 替欄位 Hire_Salary 與 Cur_Salary 建立統計資訊 */
CREATE STATISTICS Salary
  ON Foxman (Hire_Salary,Cur_Salary)
  WITH FULLSCAN,NORECOMPUTE;

/* 替欄位 Sex 建立統計資訊 */
CREATE STATISTICS Sex
  ON Foxman (sex)
  WITH FULLSCAN,NORECOMPUTE;

/* 將統計資訊 Salary 與 Sex 刪除 */
DROP STATISTICS Foxman.Salary,Foxman.Sex;