重新組織與重建索引
本主題描述如何使用 SQL Server Management Studio 或 Transact-SQL，在 SQL Server 2012 中重新組織或重建片段索引。 只要對基礎資料進行插入、更新或刪除作業，SQL Server Database Engine 就會自動維護索引。 過一段時間後，這些修改就可能使索引中的資訊變成散佈於資料庫中 (片段)。 當根據索引鍵值的邏輯順序頁面，與資料檔中的實體順序不相符時，就會有片段產生。 片段化嚴重的索引可能會造成查詢效能降低並使應用程式回應變慢。
您可以重新組織或重建索引以修復索引片段。 對於在資料分割配置上建立的資料分割索引，您可以在完整的索引或在索引的單一資料分割上使用這些方法。 重建索引會先卸除再重新建立索引。 這會移除片段；根據指定的或現有的填滿因數設定壓縮頁面來收回磁碟空間，以及重新排序連續頁面中的索引資料列。 當指定 ALL 時，會在單一交易中卸除和重建資料表的所有索引。 重新組織索引只需使用極少的系統資源。 它會實際重新排序分葉層級的頁面，使它們由左至右符合分葉節點的邏輯順序，以重新組織資料表和檢視表之叢集和非叢集索引的分葉層級。 重新組織也會壓縮索引頁。 壓縮是根據現有的填滿因數值。
本主題內容
開始之前：
偵測片段
限制事項
安全性
使用下列方法檢查索引的片段：
SQL Server Management Studio
Transact-SQL
使用下列方法重新組織或重建索引：
SQL Server Management Studio
Transact-SQL
開始之前
偵測片段
決定使用重組方法的第一步是分析索引以決定片段的程度。 透過使用系統函數 sys.dm_db_index_physical_stats，您就可以在特定的索引中、在資料表或索引檢視表上的所有索引、在資料庫中的所有索引或在所有資料庫的所有索引中偵測片段。 對於資料分割索引而言，sys.dm_db_index_physical_stats 也為每個資料分割提供片段資訊。
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%) 的情況，因為重組或重建索引的成本遠遠超過移除這麼少量的片段所獲得的好處。
[!附註]
一般來說，小型索引的片段經常是無法控制的。 小型索引的頁面會儲存在混合範圍上， 混合範圍最多可由八個物件所共用，所以當重新組織或重建索引之後，小型索引中的片段可能不會減少。
限制事項
超過 128 個範圍的索引將以兩個不同的階段重建：邏輯和實體。 在邏輯階段中，索引所使用的現有配置單位將以取消配置標示，並複製和排序資料列，然後移到所建立的新配置單位以儲存重建索引。 在實體階段中，會將先前標示為取消配置的配置單位，在背景以短暫的交易實際卸除，而且不需要許多鎖定。
在重新組織索引時將無法指定索引選項。
安全性
權限
需要資料表或檢視的 ALTER 權限。 使用者必須是系統管理員 (sysadmin) 固定伺服器角色的成員，或是 db_ddladmin 和 db_owner 固定資料庫角色的成員。
.gif "搭配回到頁首連結使用的箭頭圖示")
[回到頁首]
使用 SQL Server Management Studio
若要檢查索引的片段
在 [物件總管] 中，展開包含您要檢查其索引片段之資料表的資料庫。
展開 [資料表] 資料夾。
展開要檢查其索引片段的資料表。
展開 [索引] 資料夾。
以滑鼠右鍵按一下要檢查其片段的索引，然後選取 [屬性]。
在 [選取頁面] 底下，選取 [片段]。
[片段] 頁面上提供下列資訊：
頁面飽和度
指出索引頁面的平均飽和度，以百分比表示。 100% 表示索引頁面完全飽和。 50% 表示平均每個索引頁面為半飽和。片段總計
邏輯片段百分比。 這指出索引中未依順序儲存的頁數。平均資料列大小
分葉層級資料列的平均大小。深度
索引中的層級數目，包括分葉層級。轉送的記錄
在堆積中，有指向另一個資料位置之轉送指標的記錄數目。 (此狀態發生於更新期間，在原始位置的空間不足以儲存新資料列時)。準刪除列
標示為刪除但尚未移除的資料列數目。 這些資料列將由清除執行緒在伺服器不忙碌時移除。 這個值不包含因未處理的快照集隔離交易而保留的資料列。索引類型
索引的類型。 可能的值為 [叢集索引]、[非叢集索引] 以及 [主要 XML]。 資料表也可以儲存為堆積 (無索引)，但是無法開啟此 [索引屬性] 頁面。分葉層級資料列
分葉層級資料列的數目。資料列大小上限
分葉層級資料列大小上限。資料列大小下限
最小分葉層級資料列大小。頁數
資料頁的總數。資料分割識別碼
包含索引之 B 型樹狀目錄的資料分割識別碼。版本準刪除列
因為未處理的快照集隔離交易而保留的準刪除記錄數目。
[回到頁首]
使用 Transact-SQL
若要檢查索引的片段
在 [物件總管] 中，連接到 Database Engine 的執行個體。
在標準列上，按一下 [新增查詢]。
將下列範例複製並貼到查詢視窗中，然後按一下 [執行]。
USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), 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_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 (6 row(s) affected)
如需詳細資訊，請參閱＜sys.dm_db_index_physical_stats (Transact-SQL)＞。
[回到頁首]
使用 SQL Server Management Studio
若要重新組織或重建索引
在 [物件總管] 中，展開包含您要重新組織其索引之資料表的資料庫。
展開 [資料表] 資料夾。
展開您要重新組織其索引的資料表。
展開 [索引] 資料夾。
以滑鼠右鍵按一下您要重新組織的索引，然後選取 [重新組織]。
在 [重新組織索引] 對話方塊中，確認 [要重新組織的索引] 方格中有正確索引，然後按一下 [確定]。
選取 [壓縮大型物件資料行資料] 核取方塊，可指定一併壓縮包含大型物件 (LOB) 資料的所有頁面。
按一下 [確定]。
若要重新組織資料表中的所有索引
在 [物件總管] 中，展開包含您要重新組織其索引之資料表的資料庫。
展開 [資料表] 資料夾。
展開您要重新組織其索引的資料表。
以滑鼠右鍵按一下 [索引] 資料夾，並選取 [全部重新組織]。
在 [重新組織索引] 對話方塊中，確認 [要重新組織的索引] 方格中有正確索引。 若要從 [要重新組織的索引] 方格中移除索引，請選取索引，然後按下 DELETE 鍵。
選取 [壓縮大型物件資料行資料] 核取方塊，可指定一併壓縮包含大型物件 (LOB) 資料的所有頁面。
按一下 [確定]。
若要重建索引
在 [物件總管] 中，展開包含您要重新組織其索引之資料表的資料庫。
展開 [資料表] 資料夾。
展開您要重新組織其索引的資料表。
展開 [索引] 資料夾。
以滑鼠右鍵按一下您要重新組織的索引，然後選取 [重新組織]。
在 [重建索引] 對話方塊中，確認 [要重建的索引] 方格中有正確索引，然後按一下 [確定]。
選取 [壓縮大型物件資料行資料] 核取方塊，可指定一併壓縮包含大型物件 (LOB) 資料的所有頁面。
按一下 [確定]。
[回到頁首]
使用 Transact-SQL
若要重新組織重組的索引
在 [物件總管] 中，連接到 Database Engine 的執行個體。
在標準列上，按一下 [新增查詢]。
將下列範例複製並貼到查詢視窗中，然後按一下 [執行]。
USE AdventureWorks2012; GO -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE ; GO
若要重新組織資料表中的所有索引
在 [物件總管] 中，連接到 Database Engine 的執行個體。
在標準列上，按一下 [新增查詢]。
將下列範例複製並貼到查詢視窗中，然後按一下 [執行]。
USE AdventureWorks2012; GO -- Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO
若要重建重組的索引
在 [物件總管] 中，連接到 Database Engine 的執行個體。
在標準列上，按一下 [新增查詢]。
將下列範例複製並貼到查詢視窗中，然後按一下 [執行]。 此範例會在 Employee 資料表上重建單一索引。
USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO
若要重建資料表中全部的索引
在 [物件總管] 中，連接到 Database Engine 的執行個體。
在標準列上，按一下 [新增查詢]。
將下列範例複製並貼到查詢視窗中。此範例會指定關鍵字 ALL。 這會重建與資料表相關聯的所有索引。 指定三個選項。
USE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO
如需詳細資訊，請參閱＜ALTER INDEX (Transact-SQL)＞。
[回到頁首]