重新组织和重新生成索引Reorganize and Rebuild Indexes

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本文介绍了如何使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQLSQL ServerSQL Server 中重新整理或重新生成碎片索引。This article describes how to reorganize or rebuild a fragmented index in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 无论何时对基础数据执行插入、更新或删除操作,SQL Server 数据库引擎SQL Server Database Engine 都会自动修改索引。The SQL Server 数据库引擎SQL Server Database Engine automatically modifies 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, especially scan operations.

您可以通过重新组织或重新生成索引来修复索引碎片。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) 1ALTER INDEX REBUILD WITH (ONLINE = ON) 1

1 重新生成索引可以联机执行,也可以脱机执行。1 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 REORGANIZEALTER 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. 例如,如果给定索引主要用于扫描操作,则删除碎片可以提高这些操作的性能。For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. 对于主要用于查找操作的索引,性能优势不太明显。The performance benefit is less noticeable for indexes that are used primarily for seek operations. 同样,删除堆中的碎片(不包含聚集索引的表)对于非聚集索引扫描操作特别有用,但在查找操作中不起作用。Similarly, removing fragmentation in a heap (a table with no clustered index) is especially useful for nonclustered index scan operations, but has little effect in lookup operations.

通常情况下,非常低的碎片级别(小于 5%)不应通过这些命令来解决,因为删除如此少量的碎片所获得的收益始终远低于重新组织或重新生成索引的开销。Very low levels of fragmentation (less than 5 percent) should typically 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. 有关 ALTER INDEX REORGANIZEALTER INDEX REBUILD 的详细信息,请参阅 ALTER INDEX (Transact-SQL)For more information about ALTER INDEX REORGANIZE and ALTER INDEX REBUILD, refer to ALTER INDEX (Transact-SQL).

备注

重新生成或重新组织小索引不会减少碎片。Rebuilding or reorganizing small indexes often does not reduce fragmentation. 小索引的页面有关存储在混合盘区中。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 it.

索引碎片整理注意事项Index defragmentation considerations

在某些情况下,如果非聚集索引记录中包含的物理或逻辑标识符需要更改,则重新生成聚集索引将自动重新生成引用聚集键的任何非聚集索引。Under certain conditions, rebuilding a clustered index will automatically rebuild any nonclustered index that reference the clustering key, if the physical or logical identifiers contained in the nonclustered index records needs to change.

强制在表上自动重新生成所有非聚集索引的方案:Scenarios that force all nonclustered indexes to be automatically rebuilt on a table:

  • 在表上创建聚集索引Creating a clustered index on a table
  • 删除聚集索引,从而使表存储为堆Removing a clustered index, causing the table to be stored as a heap
  • 更改聚集键以包括或排除列Changing the clustering key to include or exclude columns

不需要在表上自动重新生成所有非聚集索引的方案:Scenarios that do not require all nonclustered indexes to be automatically rebuilt on a table:

  • 重新生成唯一聚集索引Rebuilding a unique clustered index
  • 重新生成非唯一聚集索引Rebuilding a non-unique clustered index
  • 更改索引架构,例如将分区方案应用于聚集索引或将聚集索引移到其他文件组Changing the index schema, such as applying a partitioning scheme to a clustered index or moving the clustered index to a different filegroup

限制和局限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. 有关区的详细信息,请参考页和区体系结构指南For more information about extents, refer to the Pages and Extents Architecture Guide.

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:Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.So although the filegroup might have free pages available, the user can still encounter error 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

对超过 1,000 个分区的表创建和重新生成非对齐索引是可能的,但不推荐。Creating and rebuilding non-aligned indexes on a table with more than 1,000 partitions is possible, but is not recommended. 这样做可能会导致性能下降,或在执行这些操作的过程中占用过多内存。Doing so may cause degraded performance or excessive memory consumption during these operations.

如果索引所在的文件组脱机或设置为只读,则无法重新组织或重新生成索引。An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. 如果指定了关键字 ALL,但有一个或多个索引位于脱机文件组或只读文件组中,该语句将失败。When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

重要

SQL ServerSQL Server 中创建或重新生成索引时,将通过扫描表中的所有行来创建或更新统计信息。When an index is created or rebuilt in SQL ServerSQL Server, statistics are created or updated by scanning all the rows in the table.

但是,从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,当创建或重新生成已分区索引时,不会通过扫描表中的所有行来创建或更新统计信息。However, starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), statistics are not created or updated 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 these statistics. 若要通过扫描表中所有行的方法获得有关已分区索引的统计信息,请使用 CREATE STATISTICSUPDATE 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.

SecuritySecurity

权限Permissions

要求具有对表或视图的 ALTER 权限。Requires ALTER permission on the table or view. 用户至少必须是以下某个角色的成员:User must be a member of at least one of the following roles:

  • db_ddladmin 数据库角色 1 db_ddladmin database role 1
  • db_owner 数据库角色 db_owner database role
  • sysadmin 服务器角色 sysadmin server role

1db_ddladmin 数据库角色是最低权限角色1db_ddladmin database role is the least privileged.

使用 SQL Server Management StudioSQL Server Management Studio 检查索引碎片Check index fragmentation using SQL Server Management StudioSQL 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.

    分区 ID:包含索引的 B 树的分区 ID。Partition ID 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-SQLTransact-SQL 检查索引碎片Check index fragmentation using Transact-SQLTransact-SQL

检查索引的碎片To check the fragmentation of an index

下面的示例查找 AdventureWorks 数据库中 HumanResources.Employee 表内所有索引的平均碎片百分比。The following example Find the average fragmentation percentage of all indexes in the HumanResources.Employee table in the AdventureWorks database.

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;

上一语句返回如下的结果集。The previous statement returns 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_statsFor more information, see sys.dm_db_index_physical_stats.

使用 SQL Server Management StudioSQL Server Management Studio 删除碎片Remove fragmentation using SQL Server Management StudioSQL 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-SQLTransact-SQL 删除碎片Remove fragmentation using Transact-SQLTransact-SQL

重新组织碎片索引To reorganize a fragmented index

下面的示例识别 AdventureWorks 数据库中 HumanResources.Employee 表内的 IX_Employee_OrganizationalLevel_OrganizationalNode 索引。The following example reorganizes the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table in the AdventureWorks database.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
   ON HumanResources.Employee
   REORGANIZE
;

重新组织表中的所有索引To reorganize all indexes in a table

下面的示例识别 AdventureWorks 数据库中 HumanResources.Employee 表内的所有索引。The following example Reorganize all indexes on the HumanResources.Employee table in the AdventureWorks database.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE
;

重新生成碎片索引To rebuild a fragmented index

下面的示例对 AdventureWorks 数据库中的 Employee 表重新生成一个索引。The following example rebuilds a single index on the Employee table in the AdventureWorks database.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

重新生成表中的所有索引To rebuild all indexes in a table

下面的示例使用 ALL 关键字重新生成所有与 AdventureWorks 数据库中的表关联的索引。The following example rebuilds all indexes associated with the table in the AdventureWorks database using the ALL keyword. 其中指定了三个选项。Three options are specified.

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

有关详细信息,请参阅 ALTER INDEXFor more information, see ALTER INDEX.

自动索引和统计信息管理Automatic index and statistics management

利用自适应索引碎片整理等解决方案,自动管理一个或多个数据库的索引碎片整理和统计信息更新。Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. 此过程根据碎片级别以及其他参数,自动选择是重新生成索引还是重新组织索引,并使用线性阈值更新统计信息。This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

另请参阅See Also