通过重新组织或重新生成索引来解决索引碎片问题Resolve index fragmentation by reorganizing or rebuilding indexes

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

本文介绍索引碎片整理的方法及其对查询性能的影响。This article describes how index defragmentation occurs and discusses its impact on query performance. 一旦确定索引的碎片数量,就可以在你选择的工具中运行 Transact-SQL 命令,或使用 SQL Server Management Studio,以便通过重新组织索引重新生成索引来对索引进行碎片整理。Once you determine the amount of fragmentation that exists for an index, you can defragment an index by either reorganizing an index or rebuilding an index by running Transact-SQL commands in your tool of choice or by using SQL Server Management Studio.

索引碎片概述Index fragmentation overview

什么是索引碎片,为什么我应该关注它:What is index fragmentation and why should I care about it:

  • 当索引包含的页中,索引中的逻辑排序(基于索引中的键值)与索引页中的物理排序不匹配时,就存在碎片。Fragmentation exists when indexes have pages in which the logical ordering within the index, based on the key value of the index, does not match the physical ordering inside the index pages.
  • 无论何时对基础数据执行插入、更新或删除操作,数据库引擎Database Engine 都会自动修改索引。The 数据库引擎Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. 例如,在表中添加行可能会导致拆分行存储索引中的现有页,以腾出空间来插入新键值。For example, the addition of rows in a table may cause existing pages in rowstore indexes to split to make room for the insertion of new key values. 随着时间的推移,这些修改可能会导致索引中的信息分散在数据库中(含有碎片)。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.
  • 大量碎片式索引可能会降低查询性能,因为需要额外 I/O 来查找索引指向的数据。Heavily fragmented indexes can degrade query performance because additional I/O is required to locate data to which the index points. 较多的 I/O 会导致应用程序响应缓慢,特别是在涉及扫描操作时。More I/O causes your application to respond slowly, especially when scan operations are involved.

检测碎片量Detecting the amount of fragmentation

决定使用哪种索引碎片整理方法的第一步是分析索引以确定碎片程度。The first step in deciding which index defragmentation method to use is to analyze the index to determine the degree of fragmentation. 对于行存储索引和列存储索引,检测碎片的方式不同。You detect fragmentation differently for rowstore indexes and columnstore indexes.

备注

删除大量数据之后检查索引或堆碎片尤其重要。It's especially important to review index or heap fragmentation after large amounts of data are deleted. 对于堆,如果频繁进行更新,则可能也需要检查碎片以避免前推记录激增。For heaps, if there are frequent updates, it may also be needed to review fragmentation to avoid proliferation of forwarding records. 有关堆的详细信息,请参阅堆(没有聚集索引的表)For more information about heaps, see Heaps (Tables without Clustered Indexes).

检测行存储索引中的碎片Detecting fragmentation of rowstore indexes

通过使用 sys.dm_db_index_physical_stats,可以检测特定索引中的碎片、表或索引视图上的所有索引、某个数据库中的所有索引或所有数据库中的所有索引。By using 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 sys.dm_db_index_physical_stats 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.

知道碎片程度后,可以使用下表确定删除碎片的最佳方法:INDEX REORGANIZEINDEXAfter the degree of fragmentation is known, use the following table to determine the best method to remove the fragmentation: INDEX REORGANIZE or INDEX.

avg_fragmentation_in_percentavg_fragmentation_in_percent value 修复语句Corrective statement
> 5% 且 < = 30% 1> 5% and < = 30% 1 ALTER INDEX REORGANIZEALTER INDEX REORGANIZE
> 30% 1> 30% 1 ALTER INDEX REBUILD WITH (ONLINE = ON) 2ALTER INDEX REBUILD WITH (ONLINE = ON) 2

1 这些值提供了一个大致指导原则,用于确定应在 ALTER INDEX REORGANIZEALTER INDEX REBUILD 之间进行切换的点。1 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 may not be 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.

2 重新生成索引可以联机执行,也可以脱机执行。2 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. 有关详细信息,请参阅 INDEX联机执行索引操作For more information, see INDEX and Perform Index Operations Online.

无需对碎片小于 5% 的索引进行碎片整理,因为删除如此少量碎片的获益几乎始终远低于重新组织或重新生成索引所产生的 CPU 成本。Indexes with fragmentation of less than 5 percent do not need to be defragmented because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the CPU cost incurred to reorganize or rebuild the index. 此外,重新生成或重新组织小型行存储索引通常不会减少实际的碎片。Also, rebuilding or reorganizing small rowstore indexes generally does not reduce actual fragmentation. 直到 SQL Server 2014 (12.x)SQL Server 2014 (12.x)(含),SQL Server 数据库引擎SQL Server Database Engine 都使用混合盘区分配空间。Up to, and including, SQL Server 2014 (12.x)SQL Server 2014 (12.x), the SQL Server 数据库引擎SQL Server Database Engine allocates space using mixed extents. 因此,小索引的页面有时存储在混合盘区中。Therefore, 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. 还可参阅有关重新生成行存储索引的注意事项See also Considerations specific to rebuilding rowstore indexes. 有关盘区的详细信息,请参阅页和盘区体系结构指南For more information about extents, see the Pages and Extents Architecture Guide.

检测列存储索引中的碎片Detecting fragmentation of columnstore indexes

通过使用 sys.dm_db_column_store_row_group_physical_stats,可以确定索引中已删除行所占的百分比,这可以合理地度量列存储索引的行组中的碎片。By using sys.dm_db_column_store_row_group_physical_stats, you can determine the percentage of deleted rows in an index, which is a reasonable measure for fragmentation in a rowgroup of a columnstore index. 使用此类信息,可以计算特定索引、表中所有索引、数据库中所有索引或所有数据库中全部索引上的碎片。Use this information to compute the fragmentation in a specific index, all indexes on a table, all indexes in a database, or all indexes in all databases.

sys.dm_db_column_store_row_group_physical_stats 返回的结果集包含以下列:The result set returned by sys.dm_db_column_store_row_group_physical_stats includes the following columns:

Column 说明Description
total_rowstotal_rows 以物理方式存储在行组中的行数。Number of rows physical stored in the row group. 对于压缩行组,这包括标记为已删除的行。For compressed row groups, this includes the rows that are marked as deleted.
deleted_rowsdeleted_rows 以物理方式存储在压缩行组中且标记为要删除的行数。Number of rows physically stored in a compressed row group that are marked for deletion. 对于增量存储中的行组,值为 0。0 for row groups that are in the delta store.

通过此公式使用此返回的信息计算索引碎片:Use this information returned to compute index fragmentation using this formula:

100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

知道索引碎片程度后,可以使用下表确定删除碎片的最佳方法:INDEX REORGANIZEINDEXAfter the degree of index fragmentation is known, use the following table to determine the best method to remove the fragmentation: INDEX REORGANIZE or INDEX.

“计算的碎片(%)”值computed fragmentation in percent value 适用于版本Applies to version 修复语句Corrective statement
> = 20%> = 20% SQL Server 2012 (11.x)SQL Server 2012 (11.x) SQL Server 2014 (12.x)SQL Server 2014 (12.x)and SQL Server 2014 (12.x)SQL Server 2014 (12.x) ALTER INDEX REBUILDALTER INDEX REBUILD
> = 20%> = 20% SQL Server 2016 (13.x)SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) ALTER INDEX REORGANIZEALTER INDEX REORGANIZE

使用 Transact-SQLTransact-SQL 检查行存储索引的碎片的具体步骤To check the fragmentation of a rowstore index using Transact-SQLTransact-SQL

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

SELECT a.object_id, object_name(a.object_id) AS TableName,
    a.index_id, name AS IndedxName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID (N'AdventureWorks2016_EXT')
        , OBJECT_ID(N'HumanResources.Employee')
        , NULL
        , NULL
        , NULL) AS a
INNER JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id;
GO

上一语句返回如下的结果集。The previous statement returns a result set similar to the following.

object_id   TableName    index_id    IndexName                                             avg_fragmentation_in_percent
----------- ------------ ----------- ----------------------------------------------------- ------------------------------
1557580587  Employee     1           PK_Employee_BusinessEntityID                          0
1557580587  Employee     2           IX_Employee_OrganizationalNode                        0
1557580587  Employee     3           IX_Employee_OrganizationalLevel_OrganizationalNode    0
1557580587  Employee     5           AK_Employee_LoginID                                   66.6666666666667
1557580587  Employee     6           AK_Employee_NationalIDNumber                          50
1557580587  Employee     7           AK_Employee_rowguid                                   0

(6 row(s) affected)

有关详细信息,请参阅 sys.dm_db_index_physical_statsFor more information, see sys.dm_db_index_physical_stats.

使用 Transact-SQL 检查列存储索引的碎片的具体步骤To check the fragmentation of a columnstore index using Transact-SQL

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

SELECT i.object_id,
    object_name(i.object_id) AS TableName,
    i.index_id,
    i.name AS IndexName,
    100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'
FROM sys.indexes AS i  
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups
    ON i.object_id = CSRowGroups.object_id
    AND i.index_id = CSRowGroups.index_id
WHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY object_name(i.object_id), i.name;

上一语句返回如下的结果集。The previous statement returns a result set similar to the following.

object_id   TableName                   index_id    IndexName                       Fragmentation
----------- --------------------------- ----------- ------------------------------- ---------------
114099447   FactResellerSalesXL_CCI     1           IndFactResellerSalesXL_CCI      0

(1 row(s) affected)

使用 SQL Server Management Studio 检查索引碎片Check index fragmentation using SQL Server Management Studio

备注

Management StudioManagement Studio 不能用于计算 SQL Server 中列存储索引的碎片,并且不能用于计算 Azure SQL 数据库中任何索引的碎片。cannot be used to compute fragmentation of columnstore indexes in SQL Server and cannot be used to compute fragmentation of any indexes in Azure SQL Database. 使用上面的 Transact-SQLTransact-SQL 示例Use the preceding Transact-SQLTransact-SQL example.

  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:

Value 说明Description
页填充度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.
分区 IDPartition ID 包含该索引的 B 树的分区 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.

重新生成或重新组织索引,对索引进行碎片整理Defragmenting indexes by rebuilding or reorganizing the index

使用以下方法之一对碎片索引进行碎片整理:You defragment a fragmented index by using one of the following methods:

  • 索引重组Index reorganization
  • 索引重新生成Index rebuild

备注

对于在分区方案的基础之上生成的已分区索引,可以对完整索引或索引的单个分区使用下列方法之一:For partitioned indexes built on a partition scheme, you can use either of the following methods on a complete index or a single partition of an index.

重新组织索引Reorganize an index

重新组织索引使用的系统资源最少,并且是联机操作。Reorganizing an index uses minimal system resources and is an online operation. 也就是说,不保留长期阻塞性表锁,且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续进行。This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

  • 对于行存储索引数据库引擎Database Engine 通过以物理方式重新排序叶级别页,以匹配叶节点逻辑顺序(从左到右),从而对表和视图中的聚集索引和非聚集索引的叶级别进行碎片整理。For rowstore indexes, the 数据库引擎Database Engine defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order of the leaf nodes (left to right). 重新组织还会根据索引的填充因子值压缩索引页。Reorganizing also compacts the index pages based on the index's fill factor value. 若要查看填充因子设置,请使用 sys.indexesTo view the fill factor setting, use sys.indexes. 有关语法示例,请参阅示例:行存储重新组织For syntax examples, see Examples: Rowstore reorganize.

  • 如果使用列存储索引,则在一段时间内插入、更新和删除数据后,增量存储可能最终会有多个小行组。When using columnstore indexes, the delta store may end up with multiple small rowgroups after inserting, updating, and deleting data over time. 重新组织列存储索引会强制将所有行组转到列存储中,然后将行组合并为行数更多的更少行组。Reorganizing a columnstore index forces all of the rowgroups into the columnstore, and then combines the rowgroups into fewer rowgroups with more rows. 重新组织操作还将删除已从列存储中删除的行。The reorganize operation also removes rows that have been deleted from the columnstore. 重新组织最初需要额外的 CPU 资源来压缩数据,这可能降低整体系统性能。Reorganizing initially requires additional CPU resources to compress the data, which may slow overall system performance. 但是,一旦压缩数据后,查询性能就会提高。However, as soon as the data is compressed, query performance improves. 有关语法示例,请参阅示例:列存储重新组织For syntax examples, see Examples: ColumnStore reorganize.

重新生成索引Rebuild an index

重新生成索引将会删除并重新创建索引。Rebuilding an index drops and re-creates the index. 重新生成操作可以联机或脱机执行,具体取决于索引类型和 数据库引擎Database Engine 版本。Depending on the type of index and 数据库引擎Database Engine version, a rebuild operation can be done online or offline. 有关 T-SQL 语法,请参阅 ALTER INDEX REBUILDFor the T-SQL syntax, see ALTER INDEX REBUILD

  • 对于行存储索引,重新生成操作会:删除碎片;根据指定或现有的填充因子设置来压缩页,从而回收磁盘空间;还会在连续页中重新排序索引行。For rowstore indexes, rebuilding 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. 不必预先删除外键约束。Foreign key constraints do not have to be dropped in advance. 重新生成具有 128 个区或更多区的索引时,数据库引擎Database Engine延迟实际的页释放及其关联的锁,直到事务提交。When indexes with 128 extents or more are rebuilt, the 数据库引擎Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. 有关语法示例,请参阅示例:行存储重新组织For syntax examples, see Examples: Rowstore reorganize.

  • 对于列存储索引,重新生成操作会:删除碎片;将所有行移到列存储中;以物理方式删除已在逻辑上从表中删除的行,从而回收磁盘空间。For columnstore indexes, rebuilding removes fragmentation, moves all rows into the columnstore, and reclaims disk space by physically deleting rows that have been logically deleted from the table.

    提示

    SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起,通常不需要重新生成列存储索引,因为 REORGANIZE 以联机操作形式在后台执行重新生成的基本操作。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), rebuilding the columnstore index is usually not needed since REORGANIZE performs the essentials of a rebuild in the background as an online operation.

    有关语法示例,请参阅示例:列存储重新生成For syntax examples, see Examples: ColumnStore rebuild.

权限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 数据库角色 1db_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 删除碎片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

备注

有关使用 Transact-SQLTransact-SQL 重新生成或重新组织索引的更多示例,请参阅 ALTER INDEX 示例:列存储索引ALTER INDEX 示例:行存储索引For more examples about using Transact-SQLTransact-SQL to rebuild or reorganize indexes, see ALTER INDEX Examples: Columnstore Indexes and ALTER INDEX Examples: Rowstore Indexes.

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

下面的示例重新组织 AdventureWorks2016 数据库中 HumanResources.Employee 表内的 IX_Employee_OrganizationalLevel_OrganizationalNode 索引。The following example reorganizes the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table in the AdventureWorks2016 database.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

下面的示例重新组织 AdventureWorksDW2016 数据库中 dbo.FactResellerSalesXL_CCI 表内的 IndFactResellerSalesXL_CCI 列存储索引。The following example reorganizes the IndFactResellerSalesXL_CCI columnstore index on the dbo.FactResellerSalesXL_CCI table in the AdventureWorksDW2016 database.

-- This command will force all CLOSED and OPEN rowgroups into the columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

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

下面的示例重新组织 AdventureWorks2016 数据库中 HumanResources.Employee 表内的所有索引。The following example reorganizes all indexes on the HumanResources.Employee table in the AdventureWorks2016 database.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

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

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

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

下面的示例使用 ALL 关键字重新生成所有与 AdventureWorks2016 数据库中的表关联的索引。The following example rebuilds all indexes associated with the table in the AdventureWorks2016 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 INDEX (Transact-SQL)For more information, see ALTER INDEX (Transact-SQL).

自动索引和统计信息管理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.

有关重新生成行存储索引的注意事项Considerations specific to rebuilding rowstore indexes

如果非聚集索引记录中包含的物理或逻辑标识符需要更改,重新生成聚集索引会自动重新生成任何引用聚集键的非聚集索引。Rebuilding a clustered index automatically rebuilds any nonclustered index that reference the clustering key, if the physical or logical identifiers contained in the nonclustered index records need to change.

以下场景强制自动在表上重新生成所有行存储非聚集索引:The following scenarios force all rowstore nonclustered indexes on a table to be automatically rebuilt:

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

以下场景不需要在表上自动重新生成所有行存储非聚集索引:The following scenarios do not require all rowstore 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

重要

如果索引所在的文件组脱机或设置为只读,则无法重新组织或重新生成索引。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.

当索引重新生成发生时,物理介质必须有足够的空间来存储索引的两个副本。While an index rebuild occurs, the physical media must have enough space to store two copies of the index. 在重新生成完成后,数据库引擎Database Engine 会删除原始索引。When the rebuild is finished, the 数据库引擎Database Engine deletes the original index.

如果使用 ALTER INDEX 语句指定了 ALL,表中的关系索引(包括聚集索引和非聚集索引)和 XML 索引都会进行重新组织。When ALL is specified with the ALTER INDEX statement, relational indexes, both clustered and nonclustered, and XML indexes on the table are reorganized.

有关重新生成列存储索引的注意事项Considerations specific to rebuilding a columnstore index

重新生成列存储索引时,数据库引擎Database Engine 会从原始列存储索引(包括增量存储)中读取所有数据。When rebuilding a columnstore index, the 数据库引擎Database Engine reads all data from the original columnstore index, including the delta store. 它将数据合并到新的行组中,并且将行组压缩到列存储中。It combines the data into new rowgroups, and compresses the rowgroups into the columnstore. 数据库引擎Database Engine 通过以物理方式删除已在逻辑上从表中删除的行,对列存储进行碎片整理。The 数据库引擎Database Engine defragments the columnstore by physically deleting rows that have been logically deleted from the table. 删除的字节会在磁盘上回收。The deleted bytes are reclaimed on the disk.

备注

如果使用 Management StudioManagement Studio 重新组织列存储索引,会将 COMPRESSED 行组合并在一起,但不会强制将所有行组压缩到列存储中。Reorganizing a columnstore index using Management StudioManagement Studio will combine COMPRESSED rowgroups together, but does not force all rowgroups to be compressed into the columnstore. 将压缩 CLOSED 行组,但不会将 OPEN 行组压缩到列存储中。CLOSED rowgroups will be compressed but OPEN rowgroups will not be compressed into the columnstore. 若要强制压缩所有行组,请使用下面Transact-SQLTransact-SQL 示例。To forcibly compress all rowgroups, use the Transact-SQLTransact-SQL example below.

备注

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,tuple-mover 通过后台合并任务获得帮助,该任务会自动压缩较小的已存在一段时间(由内部阈值确定)的 OPEN 增量行组,或者合并已从中删除大量行的 COMPRESSED 行组。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the tuple-mover is helped by a background merge task that automatically compresses smaller OPEN delta rowgroups that have existed for some time as determined by an internal threshold, or merges COMPRESSED rowgroups from where a large number of rows has been deleted. 随着时间的推移,这会提高列存储索引的质量。This improves the columnstore index quality over time.
有关列存储术语和概念的详细信息,请参阅列存储索引:概述For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

重新生成分区,而不是整个表Rebuild a partition instead of the entire table

  • 如果索引很大,并且在重新生成期间需要足够的磁盘空间来存储索引的额外副本,则重新生成整个表将很费时间。Rebuilding the entire table takes a long time if the index is large, and it requires enough disk space to store an additional copy of the index during the rebuild. 通常仅需要重新生成最近使用的分区。Usually it is only necessary to rebuild the most recently used partition.
  • 对于已分区的表,您不需要重新生成整个列存储索引,因为碎片仅可能在最近修改的分区中出现。For partitioned tables, you do not need to rebuild the entire columnstore index because fragmentation is likely to occur in only the partitions that have been modified recently. 事实表和大型的维度表通常已分区,以便对表的特定块执行备份和管理操作。Fact tables and large dimension tables are usually partitioned in order to perform backup and management operations on chunks of the table.

在执行了大量 DML 操作后重新生成分区Rebuild a partition after heavy DML operations

重新生成某一分区将会对该分区进行碎片整理,并且缩小磁盘存储空间。Rebuilding a partition defragments the partition and reduces disk storage. 重新生成操作会从列存储中删除标记为要删除的所有行,并将所有行组从增量存储移到列存储中。Rebuilding deletes all rows from the columnstore that are marked for deletion and moves all rowgroups from the delta store into the columnstore. 增量存储中可以有多个行组,它们的行数少于一百万。There can be multiple rowgroups in the delta store that have less than one million rows.

在加载数据后重新生成分区Rebuild a partition after loading data

在加载数据后重新生成分区以确保将所有数据存储在列存储中。Rebuilding a partition after loading date ensures all data is stored in the columnstore. 当并发进程分别在同一时间将不到 100,000 行加载到同一分区时,分区最终可能会有多个增量存储。When concurrent processes each load less than 100,000 rows into the same partition at the same time, the partition can end up with multiple delta stores. 重新生成操作会将所有增量存储行都移到列存储中。Rebuilding moves all delta store rows into the columnstore.

有关重新组织列存储索引的注意事项Considerations specific to reorganizing a columnstore index

重新组织列存储索引时,数据库引擎Database Engine 会将每个 CLOSED 增量行组作为压缩行组压缩到列存储中。When reorganizing a columnstore index, the 数据库引擎Database Engine compresses each CLOSED delta rowgroup into the columnstore as a compressed rowgroup. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起以及在 Azure SQL 数据库Azure SQL Database 中,REORGANIZE 命令会联机执行以下额外的碎片整理优化:Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL 数据库Azure SQL Database, the REORGANIZE command performs the following additional defragmentation optimizations online:

  • 在逻辑删除了 10% 或更多行时从行组中物理移除行。Physically removes rows from a rowgroup when 10% or more of the rows have been logically deleted. 删除的字节会在物理媒体上进行回收。The deleted bytes are reclaimed on the physical media. 例如,如果具有 100 万行的压缩行组删除了 10 万行,则 SQL ServerSQL Server 会移除已删除的行,并使用 90 万行重新压缩行组。For example, if a compressed row group of 1 million rows has 100K rows deleted, SQL ServerSQL Server will remove the deleted rows and recompress the rowgroup with 900k rows. 它通过移除已删除的行来节省存储。It saves on the storage by removing deleted rows.

  • 合并一个或多个压缩行组,以将每个行组的行增加到最多 1,048,576 行。Combines one or more compressed rowgroups to increase rows per rowgroup up to the maximum of 1,048,576 rows. 例如,如果批量导入 5 批 102,400 行,则会获得 5 个压缩行组。For example, if you bulk import 5 batches of 102,400 rows you will get 5 compressed rowgroups. 如果运行 REORGANIZE,则这些行组会合并为 1 个大小为 512,000 的压缩行组。If you run REORGANIZE, these rowgroups will get merged into 1 compressed rowgroup of size 512,000 rows. 这假定不存在任何字典大小或内存限制。This assumes there were no dictionary size or memory limitations.

  • 对于逻辑上已有 10% 或更多行被删除的行组,数据库引擎Database Engine 会尝试将此行组与一个或多个行组合并。For rowgroups in which 10% or more of the rows have been logically deleted, the 数据库引擎Database Engine tries to combine this rowgroup with one or more rowgroups. 例如,行组 1 使用 500,000 行进行压缩,行组 21 使用最大值 1,048,576 行进行压缩。For example, rowgroup 1 is compressed with 500,000 rows and rowgroup 21 is compressed with the maximum of 1,048,576 rows. 行组 21 删除了 60% 的行,剩下 409,830 行。Rowgroup 21 has 60% of the rows deleted which leaves 409,830 rows. 数据库引擎Database Engine 倾向于合并这两个行组来压缩一个新行组,这个行组有 909,830 行。The 数据库引擎Database Engine favors combining these two rowgroups to compress a new rowgroup that has 909,830 rows.

在数据加载后,增量存储中可能会有多个小型行组。After performing data loads, you can have multiple small rowgroups in the delta store. 可以使用 ALTER INDEX REORGANIZE 将所有行组强制载入列存储,然后将行组合并成具有更多行的较少行组。You can use ALTER INDEX REORGANIZE to force all of the rowgroups into the columnstore, and then to combine the rowgroups into fewer rowgroups with more rows. 重新组织操作还将删除已从列存储中删除的行。The reorganize operation will also remove rows that have been deleted from the columnstore.

限制和局限Limitations and restrictions

盘区超过 128 个的行存储索引通过两个单独的阶段重新生成:逻辑阶段和物理阶段。Rowstore 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, see 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 in 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 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. Microsoft 建议,当分区数超过 1,000 时,只使用对齐索引Microsoft recommends using only aligned indexes when the number of partitions exceed 1,000.

如果索引所在的文件组脱机或设置为只读,便无法重新组织或重新生成索引。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.

统计信息:Statistics:

  • 当创建或重新生成索引后 ,将通过扫描表中的所有行来创建或更新统计信息。When an index is created or rebuilt, 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.

  • 重新组织索引后,统计信息不会更新。When an index is reorganized, statistics are not updated.

ALLOW_PAGE_LOCKS 设置为 OFF 时,无法重新组织索引。An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF.

SQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更低版本中,重新生成聚集列存储索引是一项脱机操作。Up to SQL Server 2017 (14.x)SQL Server 2017 (14.x), rebuilding a clustered columnstore index is an offline operation. 执行重新生成操作时,数据库引擎必须获取表或分区上的排他锁。The database engine has to acquire an exclusive lock on the table or partition while the rebuild occurs. 即使在使用 NOLOCK、读取已提交的照隔离 (RCSI) 或快照隔离时,数据在重新生成期间仍处于脱机状态且不可用。The data is offline and unavailable during the rebuild even when using NOLOCK, Read-committed Snapshot Isolation (RCSI), or Snapshot Isolation. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 起,可以使用 ONLINE = ON 选项重新生成聚集列存储索引。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), a clustered columnstore index can be rebuilt using the ONLINE = ON option.

对于包含有序聚集列存储索引的 Azure Synapse Analytics(以前称为 Azure Synapse AnalyticsAzure Synapse Analytics)表,ALTER INDEX REBUILD 会使用 TempDB 对数据进行重新排序。For an Azure Synapse Analytics (formerly Azure Synapse AnalyticsAzure Synapse Analytics) table with an ordered clustered columnstore index, ALTER INDEX REBUILD will re-sort the data using TempDB. 在重新生成操作期间监视 TempDB。Monitor TempDB during rebuild operations. 如果需要更多 TempDB 空间,请纵向扩展数据仓库。If you need more TempDB space, scale up the data warehouse. 完成索引重新生成之后,缩小为原空间大小。Scale back down once the index rebuild is complete.

对于包含有序聚集列存储索引的 Azure Synapse Analytics(以前称为 Azure Synapse AnalyticsAzure Synapse Analytics)表,ALTER INDEX REORGANIZE 不会对数据进行重新排序。For an Azure Synapse Analytics (formerly Azure Synapse AnalyticsAzure Synapse Analytics) table with an ordered clustered columnstore index, ALTER INDEX REORGANIZE does not re-sort the data. 要对数据重新排序,可使用 ALTER INDEX REBUILDTo resort the data use ALTER INDEX REBUILD.

使用 INDEX REBUILD 从硬件故障中恢复Using INDEX REBUILD to recover from hardware failures

在旧版 SQL ServerSQL Server 中,有时可以重新生成行存储非聚集索引,以更正由硬件故障引起的不一致问题。In earlier versions of SQL ServerSQL Server, you could sometimes rebuild a rowstore nonclustered index to correct inconsistencies caused by hardware failures. SQL Server 2008SQL Server 2008 起,仍可以通过脱机重新生成非聚集索引,修复索引和聚集索引之间的这种不一致问题。Starting with SQL Server 2008SQL Server 2008, you may still be able to repair such inconsistencies between the index and the clustered index by rebuilding a nonclustered index offline. 但是,你不能通过联机重新生成索引来纠正非聚集索引的不一致,因为联机重新生成机制会使用现有的非聚集索引作为重新生成的基础,因此仍存在不一致。However, you cannot repair nonclustered index inconsistencies by rebuilding the index online, because the online rebuild mechanism uses the existing nonclustered index as the basis for the rebuild and thus persist the inconsistency. 脱机重新生成索引有时会强制扫描聚集索引(或堆)并因此删除不一致。Rebuilding the index offline can sometimes force a scan of the clustered index (or heap) and so remove the inconsistency. 要确保从聚集索引重新生成,请删除并重新创建非聚集索引。To assure a rebuild from the clustered index, drop and recreate the nonclustered index. 与早期版本一样,建议通过从备份还原受影响的数据来从不一致状态进行恢复;但是,您可以通过脱机重新生成非聚集索引来纠正索引的不一致。As with earlier versions, we recommend recovering from inconsistencies by restoring the affected data from a backup; however, you may be able to repair the index inconsistencies by rebuilding the nonclustered index offline. 有关详细信息,请参阅 DBCC CHECKDB (Transact-SQL)For more information, see DBCC CHECKDB (Transact-SQL).

另请参阅See also