本主题介绍如何使用 SQL Server Management Studio 或 Transact-SQL 在 SQL Server 2014 中重新组织或重新生成碎片索引。 每当对基础数据执行插入、更新或删除作时，SQL Server 数据库引擎都会自动维护索引。 随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（碎片）。 当索引的页面中基于键值的逻辑排序与数据文件内的物理排序不一致时，即存在碎片化。 大量碎片索引可能会降低查询性能，并导致应用程序响应缓慢。
可以通过重新组织或重建索引来修复索引碎片。 对于基于分区方案构建的分区索引，可以在完整索引或索引的单个分区上使用这些方法之一。 重新生成索引将会删除并重新创建索引。 这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。 指定 ALL 时，将删除表上的所有索引，并在单个事务中重新生成。 使用最少系统资源重新组织索引。 通过对叶级页以物理方式重新排序，使之与叶节点的从左到右的逻辑顺序相匹配，进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。 重新组织还会压缩索引页。 压缩基于现有的填充因子值。
本主题内容
准备工作：
若要检查索引的碎片化状态，请使用：
若要重新组织或重新生成索引，请使用：
在您开始之前
检测碎片化
确定要使用的碎片整理方法的第一步是分析索引以确定碎片程度。 通过使用系统函数 sys.dm_db_index_physical_stats，可以检测特定索引、表或索引视图上的所有索引、数据库中的所有索引或所有数据库中的所有索引的碎片。 对于分区索引，sys.dm_db_index_physical_stats还提供了每个分区的碎片化信息。
sys.dm_db_index_physical_stats函数返回的结果集包含以下列。
| 列 | DESCRIPTION |
|---|---|
| 平均碎片百分比 | 逻辑碎片的百分比（索引中的无序页）。 |
| fragment_count | 索引中的片段数（物理连续叶页）。 |
| 平均碎片大小（以页为单位） | 索引中一个片段中的平均页数。 |
已知碎片程度后，请使用下表确定更正碎片的最佳方法。
| avg_fragmentation_in_percent 值 | 更正声明 |
|---|---|
| > 5% 和 < = 30% | ALTER INDEX REORGANIZE |
| > 30% | ALTER INDEX REBUILD WITH （ONLINE = ON） 1 |
1 重新生成索引可以联机或脱机执行。 重新组织索引始终联机执行。 若要实现类似于重新组织选项的可用性，应联机重新生成索引。
小窍门
这些值提供了一个粗略的准则，用于确定您应该何时在 ALTER INDEX REORGANIZE 和 ALTER INDEX REBUILD 之间切换。 实际值可能因情况而异。 请务必尝试确定环境的最佳阈值。 例如，如果给定索引主要用于扫描作，则删除碎片可以提高这些作的性能。 对于主要用于查找作的索引，性能优势不太明显。 同样，删除堆中的碎片（没有聚集索引的表）对于非聚集索引扫描作特别有用，但在查找作中影响不大。
通常不应通过上述任一命令解决非常低的碎片级别（小于 5%），因为删除此类少量碎片的好处几乎总是大大超过重新组织或重新生成索引的成本。
注释
重新生成或重新组织小型索引往往不会减少碎片化。 小型索引页有时存储在混合扩展区上。 混合区段可由最多 8 个对象共享，因此在重新组织或重建之后，小型索引中的碎片可能不会减少。
索引碎片整理注意事项
在某些情况下，如果非聚集索引记录中包含的物理或逻辑标识符需要更改，重新生成聚集索引将自动重新生成引用聚类键的任何非聚集索引。
强制在表上自动重新生成所有非聚集索引的方案：
- 在表上创建聚集索引
- 删除聚集索引，导致表存储为堆
- 更改聚集键以便包含或排除特定的列
不需要在表上自动重新生成所有非聚集索引的方案：
- 重新生成唯一聚集索引
- 重新生成非唯一聚集索引
- 更改索引架构，例如将分区方案应用到聚集索引或将聚集索引移到其他文件组
局限性与限制
超过 128 个区段的索引在两个阶段中重建：逻辑和物理阶段，这两个阶段是分开的。 在逻辑阶段，索引使用的现有分配单元标记为解除分配，数据行被复制并排序，然后移动到创建用于存储重新生成的索引的新分配单元。 在计算机事务处理的物理阶段，已标记为解除分配的分配单元会在后台短事务中被物理删除，并且无需大量锁。 有关盘区的详细信息，请参阅 页面和盘区体系结构指南。
该 ALTER INDEX REORGANIZE 语句要求包含索引的数据文件具有可用空间，因为该作只能在同一文件上分配临时工作页，而不能在文件组中分配另一个文件。 因此，尽管文件组可能有可用的免费页面，但用户仍可能会遇到错误 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 多个分区的表上创建和重新生成不一致的索引，但不建议这样做。 这样做可能会导致性能下降，或在执行这些操作的过程中占用过多内存。
如果索引所在的文件组处于脱机状态或设置为只读，则无法重新组织或重新生成索引。 指定关键字 ALL 并且一个或多个索引处于脱机或只读文件组中时，该语句将失败。
安全
权限
要求具有对表或视图的 ALTER 权限。 用户必须是 sysadmin 固定服务器角色的成员或 db_ddladmin 和 db_owner 固定数据库角色的成员。
使用 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)。