重新組織和重建索引

偵測片段

決定使用重組方法的第一步是分析索引以決定片段的程度。透過使用系統函數 sys.dm_db_index_physical_stats，您就可以在特定的索引中、在資料表或索引檢視表上的所有索引、在資料庫中的所有索引或在所有資料庫的所有索引中偵測片段。對於資料分割索引而言，sys.dm_db_index_physical_stats 也為每個資料分割提供片段資訊。

sys.dm_db_index_physical_stats 函數傳回的結果集包含下列資料行。

在了解片段的程度後，請使用下表來決定修正片段最好的方法。

* 重建索引可於線上或離線執行。重新組織索引則一律在線上執行。若要達到與重新組織選項相似的可用性，您應該在線上重建索引。

這些值提供概略方針，讓您判斷應該在 ALTER INDEX REORGANIZE 和 ALTER INDEX REBUILD 之間切換的時間點。不過，實際的值可能隨各種狀況而異。請務必嘗試不同的值，以判斷適合您環境的最佳臨界值。

您不應該使用上述任何命令來處理片段層級過低 (低於 5%) 的情況，因為重組或重建索引的成本遠遠超過移除這麼少量的片段所獲得的好處。

範例

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

重新組織索引

若要重新組織一個或多個索引，請使用 ALTER INDEX 陳述式加上 REORGANIZE 子句。此陳述式可以取代 DBCC INDEXDEFRAG 陳述式。若要重新組織資料分割索引的單一資料分割，請使用 ALTER INDEX 的 PARTITION 子句。

在資料表和檢視上的叢集和非叢集索引的分葉層級重新組織索引片段，實際上是透過重新調整分葉層級頁面的順序，以符合分葉節點的邏輯順序 (由左至右)。將頁面依序排列將可改善索引掃描的效能。索引是在配置給索引的現有頁面內重新組織，不會再配置新的頁面。如果索引跨越一個檔案以上，會一次重新組織所有的檔案。在檔案之間，不會進行頁面的移轉。

重新組織也會壓縮索引頁面。會移除此壓縮所建立的空頁面，以提供額外可用的磁碟空間。壓縮是根據 sys.indexes 目錄檢視的填滿因數值。

重新組織處理序使用最低的系統資源。另外，重新組織會自動在線上執行。處理序並沒有長期的封鎖鎖定；因此，它不會封鎖執行中的查詢或更新。

當索引並未嚴重地片段化時，請重新組織索引。請參閱片段指導方針中的表格。然而，如果索引嚴重的片段化，重建索引將可獲得較好的結果。

大型物件資料類型壓縮

重新建置索引

重建索引會卸除索引和建立新的索引。當這麼做時，就會移除片段，而且將因使用指定或現有的填滿因數設定來壓縮頁面而能回收磁碟空間，索引資料列將以連續的頁面記錄 (需要配置新的頁面)。這將可減少取得要求資料所需的頁面讀取數目，進而改善磁碟效能。

下列方法可用以重建叢集和非叢集索引：

• ALTER INDEX 加上 REBUILD 子句。此陳述式可以取代 DBCC DBREINDEX 陳述式。

• CREATE INDEX 加上 DROP_EXISTING 子句。

每個方法都將執行相同的函數，但有一些優點和缺點需要加以考慮，如下表所示：

*在索引定義中指定 CLUSTERED，就可以將非叢集索引轉換成叢集索引類型。必須將 ONLINE 選項設為 OFF，才能執行此作業。不論 ONLINE 設定為何，都不支援從叢集索引轉換成非叢集索引。

**如果使用相同的名稱、資料行以及排序順序來重新建立索引，就可以省略排序作業。在建置索引時，重新建置作業會檢查資料列是否已排過序。

您也可以先使用 DROP INDEX 陳述式卸除索引以重建索引，然後再使用獨立的 CREATE INDEX 陳述式來重建它。以獨立的陳述式執行這些作業將有一些缺點，我們不建議這麼做。

停用非叢集索引以節省重建作業期間的磁碟空間

當停用非叢集索引時，就會刪除索引資料，但是索引定義仍然在中繼資料裏。在重建時索引時就會啟用它。在未停用非叢集索引時，重建作業將需要足夠的暫存磁碟空間才能同時儲存舊索引和新索引。不過，透過在個別的交易中停用和重建非叢集索引，就可以藉由停用可由後續重建或任何其他作業重複使用的索引，來提供可用的磁碟空間。除了用於排序的暫存磁碟空間 (通常是索引大小的 20%)，並不需要任何額外的空間。如果非叢集索引是在主索引鍵上，就會自動停用任何使用中和參考的 FOREIGN KEY 條件約束。這些條件約束必須在重建索引後手動啟用。詳細資訊請參閱＜停用索引＞和＜啟用索引和條件約束的指導方針＞。

重建大型索引

超過 128 個範圍的索引將以兩個不同的階段重建：邏輯和實體。在邏輯階段中，索引所使用的現有配置單位將以取消配置標示，並複製和排序資料列，然後移到所建立的新配置單位以儲存重建索引。在實體階段中，會將先前標示為取消配置的配置單位，在背景以短暫的交易實際卸除，而且不需要許多鎖定。如需詳細資訊，請參閱＜卸除和重建大型物件＞。

設定索引選項

在重新組織索引時將無法指定索引選項。不過，當您使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING 重建索引時，將可設定下列索引選項：

除此之外，ALTER INDEX 陳述式中的 SET 子句可讓您設定下列不需重建索引的索引選項：

如需詳細資訊，請參閱＜設定索引選項＞。

若要重建或重新組織索引

ALTER INDEX (Transact-SQL)

若要以一個步驟卸除和重新建立索引來重建索引

CREATE INDEX (Transact-SQL)

範例

A. 重建索引

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. 在資料表上重建所有索引以及指定選項

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. 重新組織具有 LOB 壓縮的索引

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

請參閱

參考

概念

其他資源