重新组织和重新生成索引
本主题介绍如何使用 SQL Server Management Studio 或 Transact-SQL 在 SQL Server 2012 中重新组织或重新生成碎片索引。 无论何时对基础数据执行插入、更新或删除操作，SQL Server 数据库引擎都会自动维护索引。 随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。 当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。 碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。
您可以通过重新组织或重新生成索引来修复索引碎片。 对于基于分区方案生成的已分区索引，可以在完整索引或索引的单个分区上使用下列方法之一。 重新生成索引将会删除并重新创建索引。 这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。 如果指定 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%）不应通过这些命令来解决，因为删除如此少量的碎片所获得的收益始终远低于重新组织或重新生成索引的开销。
.gif "注意") 注意 |
|---|
通常，小索引中的碎片是不可控制的。 小索引的页面存储在混合区中。 混合区最多可由八个对象共享，因此在重新组织或重新生成小索引之后可能不会减少该索引中的碎片。 |
限制和局限
带有多于 128 个区的索引通过两个单独的阶段重新生成：逻辑阶段和物理阶段。 在逻辑阶段，索引使用的现有分配单元被标记为取消分配，数据行被复制并排序，然后移动到为存储重新生成的索引而创建的新分配单元中。 在物理阶段，先前标记为取消分配的分配单元在发生在后台的短事务中被物理删除，而且不需要很多锁。
重新组织索引时不能指定索引选项。
安全性
权限
要求对表或视图具有 ALTER 权限。 用户必须是 sysadmin 固定服务器角色的成员，或者是 db_ddladmin 和 db_owner 固定数据库角色的成员。
.gif "用于“返回首页”链接的箭头图标")
[返回页首]
使用 SQL Server Management Studio
检查索引的碎片
在“对象资源管理器”中，展开其中包含您要检查索引碎片的表的数据库。
展开**“表”**文件夹。
展开您要检查索引碎片的表。
展开**“索引”**文件夹。
右键单击要检查碎片的索引，然后选择**“属性”**。
在**“选择页”下，选择“碎片”**。
**“碎片”**页将提供以下信息：
页填充度
指示索引页的平均填充率（以百分比表示）。 100% 表示索引页完全填充。 50% 表示每个索引页平均填充一半。碎片总计
逻辑碎片百分比。 用于指示索引中未按顺序存储的页数。平均行大小
叶级行的平均大小。深度
索引中的级别数（包括叶级别）。前推记录数
堆中具有指向另一个数据位置的转向指针的记录数。 （在更新过程中，如果在原始位置存储新行的空间不足，将会出现此状态。）虚影行数
标记为已删除，但尚未移除的行数。 当服务器不忙时，将通过清除线程移除这些行。 此值不包括由于某个快照隔离事务未完成而保留的行。索引类型
索引的类型。 可能的值包括**“聚集索引”、“非聚集索引”和“主 XML”**。 表也可以存储为堆（不带索引），但此后将无法打开此“索引属性”页。叶级行数
叶级行的数目。最大行大小
叶级行最大大小。最小行大小
叶级行最小大小。页
数据页总数。分区 ID
包含该索引的 B 树的分区 ID。建立虚影行版本
由于某个快照隔离事务未完成而保留的虚影记录的数目。
[返回页首]
使用 Transact-SQL
检查索引的碎片
在**“对象资源管理器”**中，连接到数据库引擎实例。
在标准菜单栏上，单击**“新建查询”**。
将以下示例复制并粘贴到查询窗口中，然后单击**“执行”**。
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
重新组织碎片索引
在**“对象资源管理器”**中，连接到数据库引擎实例。
在标准菜单栏上，单击**“新建查询”**。
将以下示例复制并粘贴到查询窗口中，然后单击**“执行”**。
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
重新组织表中的所有索引
在**“对象资源管理器”**中，连接到数据库引擎实例。
在标准菜单栏上，单击**“新建查询”**。
将以下示例复制并粘贴到查询窗口中，然后单击**“执行”**。
USE AdventureWorks2012; GO -- Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO
重新生成碎片索引
在**“对象资源管理器”**中，连接到数据库引擎实例。
在标准菜单栏上，单击**“新建查询”**。
将以下示例复制并粘贴到查询窗口中，然后单击**“执行”**。 该示例在 Employee 表中重新生成单个索引。
USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO
重新生成表中的所有索引
在**“对象资源管理器”**中，连接到数据库引擎实例。
在标准菜单栏上，单击**“新建查询”**。
复制以下示例并将其粘贴到查询中，该示例指定了 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)。
[返回页首]