重新組織與重建索引Reorganize and Rebuild Indexes

本主題適用於: 是SQL Server (從 2008 開始)是Azure SQL Database是Azure SQL 資料倉儲 是平行處理資料倉儲 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

如需舊版 SQL Server 的相關內容,請參閱重新組織與重建索引For content related to previous versions of SQL Server, see Reorganize and Rebuild Indexes.

本主題描述如何使用 SQL Server 2017SQL Server 2017Transact-SQLSQL Server Management Studio ,在 Transact-SQLTransact-SQL中重新組織或重建片段索引。This topic describes how to reorganize or rebuild a fragmented index in SQL Server 2017SQL Server 2017 by using Transact-SQLSQL Server Management Studio or Transact-SQLTransact-SQL. 只要對基礎資料進行插入、更新或刪除作業, SQL Server Database EngineSQL Server Database Engine 就會自動維護索引。The SQL Server Database EngineSQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. 過一段時間後,這些修改就可能使索引中的資訊變成散佈於資料庫中 (片段)。Over time these modifications can cause the information in the index to become scattered in the database (fragmented). 當根據索引鍵值的邏輯順序頁面,與資料檔中的實體順序不相符時,就會有片段產生。Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. 片段化嚴重的索引可能會造成查詢效能降低並使應用程式回應變慢。Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

您可以重新組織或重建索引以修復索引片段。You can remedy index fragmentation by reorganizing or rebuilding an index. 對於在資料分割配置上建立的資料分割索引,您可以在完整的索引或在索引的單一資料分割上使用這些方法。For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. 重建索引會卸除和重新建立索引。Rebuilding an index drops and re-creates the index. 這會移除片段;根據指定的或現有的填滿因數設定壓縮頁面來收回磁碟空間,以及重新排序連續頁面中的索引資料列。This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. 當指定 ALL 時,會在單一交易中卸除和重建資料表的所有索引。When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. 重新組織索引所用的系統資源最少。Reorganizing an index uses minimal system resources. 它會實際重新排序分葉層級的頁面,使它們由左至右符合分葉節點的邏輯順序,以重新組織資料表和檢視表之叢集和非叢集索引的分葉層級。It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. 重新組織也會壓縮索引頁面。Reorganizing also compacts the index pages. 壓縮是以現有填滿因數值為基礎。Compaction is based on the existing fill factor value.

開始之前 Before You Begin

偵測片段 Detecting Fragmentation

決定使用重組方法的第一步是分析索引以決定片段的程度。The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. 透過使用系統函數 sys.dm_db_index_physical_stats,您就可以在特定的索引中、在資料表或索引檢視表上的所有索引、在資料庫中的所有索引或在所有資料庫的所有索引中偵測片段。By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. 對於資料分割索引而言, sys.dm_db_index_physical_stats 也為每個資料分割提供片段資訊。For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

sys.dm_db_index_physical_stats 函數傳回的結果集包含下列資料行。The result set returned by the sys.dm_db_index_physical_stats function includes the following columns.

資料行Column 描述Description
avg_fragmentation_in_percentavg_fragmentation_in_percent 邏輯片段的百分比 (索引中失序的頁面)。The percent of logical fragmentation (out-of-order pages in the index).
fragment_countfragment_count 在索引中的片段數目 (實體上為連續的分葉頁面)。The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pagesavg_fragment_size_in_pages 在索引中一個片段的頁面平均數目。Average number of pages in one fragment in an index.

在了解片段的程度後,請使用下表來決定修正片段最好的方法。After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

avg_fragmentation_in_percentavg_fragmentation_in_percent value 修正的陳述式Corrective statement
> 5% 且 < = 30%> 5% and < = 30% ALTER INDEX REORGANIZEALTER INDEX REORGANIZE
> 30%> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)ALTER INDEX REBUILD WITH (ONLINE = ON)

* 重建索引可於線上或離線執行。* Rebuilding an index can be executed online or offline. 重新組織索引則一律在線上執行。Reorganizing an index is always executed online. 若要達到與重新組織選項相似的可用性,您應該在線上重建索引。To achieve availability similar to the reorganize option, you should rebuild indexes online.

這些值提供概略方針,讓您判斷應該在 ALTER INDEX REORGANIZE 和 ALTER INDEX REBUILD 之間切換的時間點。These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. 不過,實際的值可能隨各種狀況而異。However, the actual values may vary from case to case. 請務必嘗試不同的值,以判斷適合您環境的最佳臨界值。It is important that you experiment to determine the best threshold for your environment. 您不應該使用上述任何命令來處理片段層級過低 (低於 5%) 的情況,因為重組或重建索引的成本遠遠超過移除這麼少量的片段所獲得的好處。Very low levels of fragmentation (less than 5 percent) should not be addressed by either of these commands because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the cost of reorganizing or rebuilding the index.

注意

一般來說,小型索引的片段經常是無法控制的。In general, fragmentation on small indexes is often not controllable. 小型索引的頁面有時候會儲存在混合範圍上,The pages of small indexes are sometimes stored on mixed extents. 混合範圍最多可由八個物件所共用,所以當重新組織或重建索引之後,小型索引中的片段可能不會減少。Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

限制事項 Limitations and Restrictions

  • 超過 128 個範圍的索引將以兩個不同的階段重建:邏輯和實體。Indexes with more than 128 extents are rebuilt in two separate phases: logical and physical. 在邏輯階段中,索引所使用的現有配置單位將以取消配置標示,並複製和排序資料列,然後移到所建立的新配置單位以儲存重建索引。In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted, then moved to new allocation units created to store the rebuilt index. 在實體階段中,會將先前標示為取消配置的配置單位,在背景以短暫的交易實際卸除,而且不需要許多鎖定。In the physical phase, the allocation units previously marked for deallocation are physically dropped in short transactions that happen in the background, and do not require many locks.

  • 在重新組織索引時將無法指定索引選項。Index options cannot be specified when reorganizing an index.

  • ALTER INDEX REORGANIZE 陳述式需要包含索引的資料檔案具有可用的空間,因為作業只能配置在相同檔案上的暫存工作頁面,而不是檔案群組內的其他檔案。The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space available, because the operation can only allocate temporary work pages on the same file, not another file within the filegroup. 因此,雖然檔案群組可能有可用的可用頁面,但是使用者仍然可能會遇到錯誤 1105「'PRIMARY' 檔案群組已滿,無法在資料庫 <資料庫名稱> 中為物件 <索引名稱.<資料表名稱> 配置空間。」So although the filegroup might have free pages available, the user can still encounter error 1105 "Could not allocate space for object <index name>.<table name> in database <database name> because the 'PRIMARY' filegroup is full."

  • 您可以對包含超過 1,000 個分割區的資料表,建立及重建不以資料表為準的索引,但不予支援。Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. 此做法可能會導致在作業期間效能降低或耗用過多記憶體。Doing so may cause degraded performance or excessive memory consumption during these operations.

注意

SQL Server 2012SQL Server 2012開始,並不會在建立或重建資料分割索引之後掃描資料表中所有的資料列建立統計資料。Starting with SQL Server 2012SQL Server 2012, statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. 反之,查詢最佳化工具會使用預設的採樣演算法來產生統計資料。Instead, the query optimizer uses the default sampling algorithm to generate statistics. 如果要在掃描資料表中所有資料列時取得分割區索引的統計資料,請使用 CREATE STATISTICS 或 UPDATE STATISTICS 搭配 FULLSCAN 子句。To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

安全性 Security

Permissions Permissions

需要資料表或檢視表的 ALTER 權限。Requires ALTER permission on the table or view. 使用者必須是 系統管理員 固定伺服器角色的成員,或是 db_ddladmindb_owner 固定資料庫角色的成員。User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

使用 SQL Server Management Studio Using SQL Server Management Studio

若要檢查索引的片段To check the fragmentation of an index

  1. 在 [物件總管] 中,展開包含您要檢查其索引片段之資料表的資料庫。In Object Explorer, Expand the database that contains the table on which you want to check an index’s fragmentation.

  2. 展開 [資料表] 資料夾。Expand the Tables folder.

  3. 展開要檢查其索引片段的資料表。Expand the table on which you want to check an index’s fragmentation.

  4. 展開 [索引] 資料夾。Expand the Indexes folder.

  5. 以滑鼠右鍵按一下要檢查其片段的索引,然後選取 [屬性]Right-click the index of which you want to check the fragmentation and select Properties.

  6. [選取頁面]底下,選取 [片段]Under Select a page, select Fragmentation.

    [片段] 頁面上提供下列資訊:The following information is available on the Fragmentation page:

    頁面飽和度Page fullness
    指出索引頁面的平均飽和度,以百分比表示。Indicates average fullness of the index pages, as a percentage. 100% 表示索引頁面完全飽和。100% means the index pages are completely full. 50% 表示平均每個索引頁面為半飽和。50% means that, on average, each index page is half full.

    片段總計Total fragmentation
    邏輯片段百分比。The logical fragmentation percentage. 這指出索引中未依順序儲存的頁數。This indicates the number of pages in an index that are not stored in order.

    平均資料列大小Average row size
    分葉層級資料列的平均大小。The average size of a leaf level row.

    深度Depth
    索引中的層級數目,包括分葉層級。The number of levels in the index, including the leaf level.

    轉送的記錄Forwarded records
    在堆積中,有指向另一個資料位置之轉送指標的記錄數目。The number of records in a heap that have forward pointers to another data location. (此狀態發生於更新期間,原始位置的空間不足以儲存新資料列時)。(This state occurs during an update, when there is not enough room to store the new row in the original location.)

    準刪除列Ghost rows
    標示為刪除但尚未移除的資料列數目。The number of rows that are marked as deleted but not yet removed. 這些資料列將由清除執行緒在伺服器不忙碌時移除。These rows will be removed by a clean-up thread, when the server is not busy. 這個值不包含因未處理的快照集隔離交易而保留的資料列。This value does not include rows that are being retained due to an outstanding snapshot isolation transaction.

    索引類型Index type
    索引的類型。The type of index. 可能的值為 [叢集索引][非叢集索引]以及 [主要 XML]Possible values are Clustered index, Nonclustered index, and Primary XML. 資料表也可以儲存為堆積 (無索引),但是無法開啟此 [索引屬性] 頁面。Tables can also be stored as a heap (without indexes), but then this Index Properties page cannot be opened.

    分葉層級資料列Leaf-level rows
    分葉層級資料列的數目。The number of leaf level rows.

    資料列大小上限Maximum row size
    分葉層級資料列大小上限。The maximum leaf-level row size.

    資料列大小下限Minimum row size
    最小分葉層級資料列大小。The minimum leaf-level row size.

    頁面Pages
    資料頁的總數。The total number of data pages.

    資料分割識別碼Partition ID
    包含索引之 B 型樹狀目錄的資料分割識別碼。The partition ID of the b-tree containing the index.

    版本準刪除列Version ghost rows
    因為未處理的快照集隔離交易而保留的準刪除記錄數目。The number of ghost records that are being retained due to an outstanding snapshot isolation transaction.

使用 Transact-SQL Using Transact-SQL

若要檢查索引的片段To check the fragmentation of an index

  1. [物件總管]中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute.

    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  
    

    上面的陳述式可能會傳回與下例類似的結果集。The statement above might return a result set similar to the following.

    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)For more information, see sys.dm_db_index_physical_stats (Transact-SQL).

使用 SQL Server Management Studio Using SQL Server Management Studio

若要重新組織或重建索引To reorganize or rebuild an index

  1. 在 [物件總管] 中,展開包含您要重新組織其索引之資料表的資料庫。In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.

  2. 展開 [資料表] 資料夾。Expand the Tables folder.

  3. 展開您要重新組織其索引的資料表。Expand the table on which you want to reorganize an index.

  4. 展開 [索引] 資料夾。Expand the Indexes folder.

  5. 以滑鼠右鍵按一下您要重新組織的索引,然後選取 [重新組織]Right-click the index you want to reorganize and select Reorganize.

  6. [重新組織索引] 對話方塊中,確認 [要重新組織的索引] 方格中有正確索引,然後按一下 [確定]In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.

  7. 選取 [壓縮大型物件資料行資料] 核取方塊,可指定一併壓縮包含大型物件 (LOB) 資料的所有頁面。Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.

  8. 按一下 [確定]Click OK.

若要重新組織資料表中的所有索引To reorganize all indexes in a table

  1. 在 [物件總管] 中,展開包含您要重新組織其索引之資料表的資料庫。In Object Explorer, Expand the database that contains the table on which you want to reorganize the indexes.

  2. 展開 [資料表] 資料夾。Expand the Tables folder.

  3. 展開您要重新組織其索引的資料表。Expand the table on which you want to reorganize the indexes.

  4. 以滑鼠右鍵按一下 [索引] 資料夾,並選取 [全部重新組織]Right-click the Indexes folder and select Reorganize All.

  5. [重新組織索引] 對話方塊中,確認 [要重新組織的索引]方格中有正確索引。In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. 若要從 [要重新組織的索引] 方格中移除索引,請選取索引,然後按下 DELETE 鍵。To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key.

  6. 選取 [壓縮大型物件資料行資料] 核取方塊,可指定一併壓縮包含大型物件 (LOB) 資料的所有頁面。Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.

  7. 按一下 [確定]Click OK.

若要重建索引To rebuild an index

  1. 在 [物件總管] 中,展開包含您要重新組織其索引之資料表的資料庫。In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.

  2. 展開 [資料表] 資料夾。Expand the Tables folder.

  3. 展開您要重新組織其索引的資料表。Expand the table on which you want to reorganize an index.

  4. 展開 [索引] 資料夾。Expand the Indexes folder.

  5. 以滑鼠右鍵按一下您要重新組織的索引,並選取 [重建]。Right-click the index you want to reorganize and select Rebuild.

  6. [重建索引] 對話方塊中,確認 [要重建的索引] 方格中有正確索引,然後按一下 [確定]In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK.

  7. 選取 [壓縮大型物件資料行資料] 核取方塊,可指定一併壓縮包含大型物件 (LOB) 資料的所有頁面。Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.

  8. 按一下 [確定].Click OK.

使用 Transact-SQL Using Transact-SQL

若要重新組織重組的索引To reorganize a defragmented index

  1. [物件總管]中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute.

    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  
    

若要重新組織資料表中的所有索引To reorganize all indexes in a table

  1. [物件總管]中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;   
    GO  
    -- Reorganize all indexes on the HumanResources.Employee table.  
    ALTER INDEX ALL ON HumanResources.Employee  
    REORGANIZE ;   
    GO  
    

若要重建重組的索引To rebuild a defragmented index

  1. [物件總管]中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 此範例會在 Employee 資料表上重建單一索引。The example rebuilds a single index on the Employee table.

    USE AdventureWorks2012;
    GO
    ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
    REBUILD;
    GO
    
    USE AdventureWorks2012;
    GO
    ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
    REBUILD;
    GO
    

若要重建資料表中全部的索引To rebuild all indexes in a table

  1. [物件總管]中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 將下列範例複製並貼入查詢視窗中。此範例會指定關鍵字 ALLCopy and paste the following example into the query The example specifies the keyword ALL. 這會重建與資料表相關聯的所有索引。This rebuilds all indexes associated with the table. 指定三個選項。Three options are specified.

    USE AdventureWorks2012;
    GO
    ALTER INDEX ALL ON Production.Product
    REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
                  STATISTICS_NORECOMPUTE = ON);
    GO
    
    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)For more information, see ALTER INDEX (Transact-SQL).

另請參閱See Also

SQL Server 索引設計指南SQL Server Index Design Guide