章立民研究室

資料片段

當那些 會影響索引的資料被修改時，資料庫中之索引的資訊便有可能分割成為片段。比方說，當新增或修改資料表的資料記錄，或是索引之鍵值欄的內容被修改，SQL Server 就必須調整索引分頁以便能容納這些異動並維護索引資料的儲存。此一索引分頁的調整也就是先前提過的分頁重分配。分頁重分配會加大資料表的大小，並增加查詢 所需的時間。

有兩個方法來管理資料片段的問題。第一個方法是先移除一個叢集索引，再重新建立它，而於重新建立時，必須指定一個填充係數。第二個方法是以單一步驟重建索引，並指定一個填充係數。

資料片段是利或弊，要視應用系統的環境而定：

■ 在一個 OLTP 環境中，資料片段是有益的，此乃因一個 OLTP 環境是以寫入作業為主。一個典型的 OLTP 系統通常會有極多的使用者同時新增與修改資料。

■ 在一個 OLAP 服務環境中，資料片段是有害的，此乃因一個典型的 OLAP 系統是以讀取資料為主，而資料過於片段會使得讀取作業花費較長的時間才能完成。

檢視資料片段情況

欲查詢某個索引的資料片段狀態，請於 SQL Server Management Studio「物件總管」中依序展開欲檢視其索引資料片段的「資料庫／資料表／索引」項目，用滑鼠選取您欲檢視的索引名稱，然後按下滑鼠右鍵選擇「屬性」指令，按一下「選取頁面」中的「片段」項目，在「片段總計」文字方塊中會列示資料片段的大小，以圖表 1 為例，表示索引分頁被填滿到 27.27% 的狀態，就是說索引之葉層分頁中失序之分頁有 27.27%。

圖表 1

一般來說，當「片段總計」文字方塊中的資料片段大小超過 30% 時，我們會建議重建索引；而當資料片段大小小於或等於 30% 時，只會進行重組索引。如果要重建或重組某個索引的資料片段，請於「物件總管」中依序展開此索引所在的「資料庫／資料表／索引」項目，接著用滑鼠右鍵按一下此索引的名稱，依照您的需要選擇「重建」或「重新組織」指令。從下一節開始，我們會有兩個小節介紹使用指令方式來重建與重組索引。

附註：
欲檢視某一個資料表之資料與索引更詳細的資料片段情況，可以使用 sys.dm_db_index_physical_stats 這個動態管理函數。

重建索引

要改善索引資料過於片段大小超過 30% 的情形很簡單，只需重建索引即可。基本上，重建索引有下列功效：

■ 藉由壓縮或擴充資料記錄來重組葉層分頁。

■ 使資料連續地相鄰排列，而去除零散片段的情況。

■ 重新計算索引統計資訊。

■ 啟用先前被停用的索引。

如果您想要重建索引，除了上一節使用圖形介面操作之外，應採用在單一步驟內即可重建索引的方式，而不該採用先刪除索引再重新建立索引的做法。欲於單一步驟內重建索引，可使用下列兩道陳述式之一：

CREATE INDEX . . . WITH DROP_EXISTING

－或－

ALTER INDEX . . . REBUILD

CREATE INDEX . . . WITH DROP_EXISTING 陳述式的用法與特性已經說明過，此處就不再浪費篇幅說明。至於 ALTER INDEX . . . REBUILD 陳述式的最大特色則是，它可以一次重新建立資料表的某一個或全部的索引，而不需每一個索引個別去重新建立，此陳述式的語法如下所示：

ALTER INDEX { index_name∣ALL }
  ON [ database_name. [ schema_name ] .∣schema_name. ]
      table_or_view_name
REBUILD
[ WITH
( { PAD_INDEX＝{ ON│OFF }
  │FILLFACTOR＝fillfactor
  │SORT_IN_TEMPDB＝{ ON│OFF }
  │IGNORE_DUP_KEY＝{ ON│OFF }
  │STATISTICS_NORECOMPUTE＝{ ON│OFF }
  │ONLINE＝{ ON│OFF }
  │ALLOW_ROW_LOCKS＝{ ON│OFF }
  │ALLOW_PAGE_LOCKS＝{ ON│OFF }
  │MAXDOP＝max_degree_of_parallelism }
   [ ,...n ] ) ]
[ ; ]

語法中的 index_name 引數用來指定您要重建其索引之資料表的名稱。許多引數我們先前已經說明過，於此不再贅述。大家必須注意，如果您要重建某一個資料表的所有索引，只需使用 index_name 引數指定資料表的名稱，而不需要加入 ALL 關鍵字。比方說，我們要重建資料庫「索引調校練習」之資料表 Vfpman 的所有索引，可執行如下的程式碼：

ALTER INDEX ALL ON Vfpman REBUILD;

當您使用使用 ALTER INDEX . . . REBUILD 陳述式時，請注意下列事項：

1. ALTER INDEX . . . REBUILD 陳述式能夠直接去重建 PRIMARY KEY 與 UNIQUE 條件約束的索引。

2. 如果您的伺服器有多顆 CPU，在執行 ALTER INDEX . . . REBUILD 時，SQL Server 會自動使用更多的 CPU 來執行重建索引作業。

3. 您可以不用手動去啟動先前被停用的索引，SQL Server 會先重建並自動啟用這些被停用的索引。

4. SQL Server僅會重建您所指定的索引，除非您使用 ALL 關鍵字指定重建所有的索引。

重新組織索引

要改善索引資料過於片段大小小於或等於 30% 的情形很簡單，只需重新組織索引即可。基本上，重新組織索引有下列功效：

■ 重新調整葉層分頁的順序，以由左至右來排列鍵值的邏輯順序。

■ 在既有的分頁中重組索引，不會將索引放置於新的分頁中。

■ 壓縮索引分頁，並移除壓縮後空的分頁，以便釋放出硬碟空間。

ALTER INDEX 陳述式的語法如下所示：

ALTER INDEX { index_name∣ALL }
  ON [ database_name. [ schema_name ] .∣schema_name. ]
      table_or_view_name
REORGANIZE
[ ; ]

比方說，我們要替資料庫「索引調校練習」之資料表 Vfpman 重新組織其索引 PK_Vfpman，可執行如下的程式碼：

ALTER INDEX PK_Vfpman ON Vfpman REORGANIZE;

附註：
重組作業會自動在線上執行，而不會發生無法存取資料庫的問題。當您重新組織索引之後，效能還是沒有改善時，請重建整個索引。