重新組織和重建索引
更新： 2006 年 7 月 17 日
只要對基礎資料進行插入、更新或刪除作業，SQL Server 2005 Database Engine 就會自動維護索引。過一段時間後，這些修改就可能使索引中的資訊變成散佈於資料庫中 (片段)。當根據索引鍵值的邏輯順序頁面，與資料檔中的實體順序不相符時，就會有片段產生。片段化嚴重的索引可能會造成查詢效能降低並使應用程式回應變慢。如需詳細資訊，請造訪 Microsoft 網站。
在 SQL Server 2005 中，您可以重新組織索引或重建索引以修復索引片段。對於在資料分割配置上建立的資料分割索引，您可以在完整的索引或在索引的單一資料分割上使用這些方法。
偵測片段
決定使用重組方法的第一步是分析索引以決定片段的程度。透過使用系統函數 sys.dm_db_index_physical_stats，您就可以在特定的索引中、在資料表或索引檢視上的所有索引、在資料庫中的所有索引或在所有資料庫的所有索引中偵測片段。對於資料分割索引而言，sys.dm_db_index_physical_stats 也為每個資料分割提供片段資訊。
計算片段的演算法用在 SQL Server 2005 中，比用在 SQL Server 2000 中來得更精確。因此，片段值會顯得比較高。例如，在 SQL Server 2000 中，如果資料表在同一範圍內有分頁 11 及分頁 13，但是沒有分頁 12，即不視為是片段的。不過，要存取這兩個分頁就必須執行兩個實體 I/O 作業，因此這在 SQL Server 2005 中算作是片段。
sys.dm_db_index_physical_stats 函數傳回的結果集包含下列資料行。
| 資料行 | 描述 |
|---|---|
avg_fragmentation_in_percent |
邏輯片段的百分比 (索引中失序的頁面)。 |
fragment_count |
在索引中的片段數目 (實體上為連續的分葉頁面)。 |
avg_fragment_size_in_pages |
在索引中一個片段的頁面平均數目。 |
在了解片段的程度後，請使用下表來決定修正片段最好的方法。
| avg_fragmentation_in_percent 值 | 修正的陳述式 |
|---|---|
> 5% 和 < = 30% |
ALTER INDEX REORGANIZE |
> 30% |
ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* 重建索引可於線上或離線執行。重新組織索引則一律在線上執行。若要達到與重新組織選項相似的可用性，您應該在線上重建索引。
這些值提供概略方針，讓您判斷應該在 ALTER INDEX REORGANIZE 和 ALTER INDEX REBUILD 之間切換的時間點。不過，實際的值可能隨各種狀況而異。請務必嘗試不同的值，以判斷適合您環境的最佳臨界值。
您不應該使用上述任何命令來處理片段層級過低 (低於 5%) 的情況，因為重組或重建索引的成本遠遠超過移除這麼少量的片段所獲得的好處。
.gif "ms189858.note(zh-tw,SQL.90).gif") 附註： |
|---|
| 一般而言，重建或重新組織小型索引通常不會減少片段。小型索引的頁面會儲存在混合範圍上。混合範圍最多會由八個物件共用，因此重新組織或重建索引之後可能不會減少小型索引中的片段。如需有關混合範圍的詳細資訊，請參閱＜頁面與範圍＞。 |
範例
下列範例會查詢 sys.dm_db_index_physical_stats 動態管理函數，以傳回 Production.Product 資料表中所有索引的平均片段。透過使用上表，建議的解決方案為重新組織 PK_Product_ProductID 和重建其他的索引。
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 目錄檢視的填滿因數值。
重新組織處理序使用最低的系統資源。另外，重新組織會自動在線上執行。處理序並沒有長期的封鎖鎖定；因此，它不會封鎖執行中的查詢或更新。
當索引並未嚴重地片段化時，請重新組織索引。請參閱片段指導方針中的表格。然而，如果索引嚴重的片段化，重建索引將可獲得較好的結果。
大型物件資料類型壓縮
除了重新組織一或多個索引之外，在重新組織索引時，預設會壓縮叢集索引或基礎資料表所包含的大型物件資料類型 (LOB)。image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 以及 xml 資料類型都是大型物件資料類型。壓縮此資料可讓您更善加利用磁碟空間：
- 組織一個指定的叢集索引將會壓縮所有包含在叢集索引分葉層級 (資料列) 的 LOB 資料行。
- 重新組織非叢集索引將會壓縮索引中所有無索引鍵 (內含) 資料行的 LOB 資料行。
- 當指定 ALL 時，會重新組織所有與指定資料表或檢視關聯的所有索引，以及壓縮所有與叢集索引、基礎資料表或具有內含資料行之非叢集索引相關聯的 LOB 資料行。
- 如果 LOB 資料行不存在，便會忽略 LOB_COMPACTION 子句。
重新建置索引
重建索引會卸除索引和建立新的索引。當這麼做時，就會移除片段，而且將因使用指定或現有的填滿因數設定來壓縮頁面而能回收磁碟空間，索引資料列將以連續的頁面記錄 (需要配置新的頁面)。這將可減少取得要求資料所需的頁面讀取數目，進而改善磁碟效能。
下列方法可用以重建叢集和非叢集索引：
- ALTER INDEX 加上 REBUILD 子句。此陳述式可以取代 DBCC DBREINDEX 陳述式。
- CREATE INDEX 加上 DROP_EXISTING 子句。
每個方法都將執行相同的函數，但有一些優點和缺點需要加以考慮，如下表所示：
| 功能 | ALTER INDEX REBUILD | CREATE INDEX WITH DROP_EXISTING |
|---|---|---|
可以在索引定義中加入或移除索引鍵資料行、變更資料行順序或變更資料行排序順序。* |
否 |
是** |
可以設定或修改索引選項。 |
是 |
是 |
在單一交易中可以重建一個以上的索引。 |
是 |
否 |
大部分的索引類型可以在不封鎖執行中查詢或更新的情況下於線上重建。 |
是 |
是 |
可以重新分割的資料分割索引。 |
否 |
是 |
索引可以移到另一個檔案群組。 |
否 |
是 |
需要額外的暫存磁碟空間。 |
是 |
是 |
重建與非叢集索引相關聯的叢集索引重建。 |
否 除非指定關鍵字 ALL。 |
否 除非變更索引定義。 |
不需卸除和重新建立條件約束，就可以重建強制使用 PRIMARY KEY 與 UNIQUE 條件約束的索引。 |
是 |
是 |
可以重建單一索引資料分割。 |
是 |
否 |
*在索引定義中指定 CLUSTERED，就可以將非叢集索引轉換成叢集索引類型。必須將 ONLINE 選項設為 OFF，才能執行此作業。不論 ONLINE 設定為何，都不支援從叢集索引轉換成非叢集索引。
**如果使用相同的名稱、資料行以及排序順序來重新建立索引，就可以省略排序作業。在建置索引時，重新建置作業會檢查資料列是否已排過序。
您也可以先使用 DROP INDEX 陳述式卸除索引以重建索引，然後再使用獨立的 CREATE INDEX 陳述式來重建它。以獨立的陳述式執行這些作業將有一些缺點，我們不建議這麼做。
停用非叢集索引以節省重建作業期間的磁碟空間
當停用非叢集索引時，就會刪除索引資料，但是索引定義仍然在中繼資料裏。在重建時索引時就會啟用它。在未停用非叢集索引時，重建作業將需要足夠的暫存磁碟空間才能同時儲存舊索引和新索引。不過，透過在個別的交易中停用和重建非叢集索引，就可以藉由停用可由後續重建或任何其他作業重複使用的索引，來提供可用的磁碟空間。除了用於排序的暫存磁碟空間 (通常是索引大小的 20%)，並不需要任何額外的空間。如果非叢集索引是在主索引鍵上，就會自動停用任何使用中和參考的 FOREIGN KEY 條件約束。這些條件約束必須在重建索引後手動啟用。如需詳細資訊，請參閱＜停用索引＞和＜啟用索引和條件約束的指導方針＞。
重建大型索引
超過 128 個範圍的索引將以兩個不同的階段重建：邏輯和實體。在邏輯階段中，索引所使用的現有配置單位將以取消配置標示，並複製和排序資料列，然後移到所建立的新配置單位以儲存重建索引。在實體階段中，會將先前標示為取消配置的配置單位，在背景以短暫的交易實際卸除，而且不需要許多鎖定。如需詳細資訊，請參閱＜卸除和重建大型物件＞。
設定索引選項
在重新組織索引時將無法指定索引選項。不過，當您使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING 重建索引時，將可設定下列索引選項：
PAD_INDEX |
DROP_EXISTING (僅 CREATE INDEX) |
FILLFACTOR |
ONLINE |
SORT_IN_TEMPDB |
ALLOW_ROW_LOCKS |
IGNORE_DUP_KEY |
ALLOW_PAGE_LOCKS |
STATISTICS_NORECOMPUTE |
MAXDOP |
| 附註： |
|---|
| 如果不需要排序作業，或者可以在記憶體中執行排序，則忽略 SORT_IN_TEMPDB 選項。 |
除此之外，ALTER INDEX 陳述式中的 SET 子句可讓您設定下列不需重建索引的索引選項：
ALLOW_PAGE_LOCKS |
IGNORE_DUP_KEY |
ALLOW_ROW_LOCKS |
STATISTICS_NORECOMPUTE |
如需詳細資訊，請參閱＜設定索引選項＞。
若要重建或重新組織索引
若要以一個步驟卸除和重新建立索引來重建索引
範例
A. 重建索引
下列範例會重建單一索引。
USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO
B. 在資料表上重建所有索引以及指定選項
下列範例指定 ALL 關鍵字。這會重建關聯於資料表的所有索引。指定三個選項。
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
C. 重新組織具有 LOB 壓縮的索引
下列範例會重新組織單一叢集索引。由於索引在分葉層級中包含 LOB 資料類型，因此，這個陳述式也會壓縮包含大型物件資料的所有頁面。請注意您不必指定 WITH (LOB_Compaction) 選項，因為預設值是 ON。
USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO
請參閱
概念
其他資源
ALTER INDEX (Transact-SQL)
決定索引的磁碟空間需求
sys.dm_db_index_physical_stats
sys.dm_db_index_operational_stats
sys.indexes (Transact-SQL)
sys.dm_db_index_usage_stats
說明及資訊
變更歷程記錄
| 版本 | 歷程記錄 |
|---|---|
2006 年 7 月 17 日 |
|
2005 年 12 月 5 日 |
|