重新组织和重新生成索引，来自联机丛书
无论何时对基础数据执行插入、更新或删除操作，SQL Server 数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。有关详细信息，请参阅此 Microsoft 网站。
您可以通过重新组织索引或重新生成索引来修复索引碎片。对于基于分区方案生成的已分区索引，可以在完整索引或索引的单个分区上使用下列方法之一。
检测碎片
决定使用哪种碎片整理方法的第一步是分析索引以确定碎片程度。通过使用系统函数 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%）不应通过这些命令来解决，因为删除如此少量的碎片所获得的收益始终远低于重新组织或重新生成索引的开销。
 注意： |
|---|
| 通常，小索引中的碎片是不可控制的。小索引的页面存储在混合区中。混合区最多可由八个对象共享，因此在重新组织或重新生成小索引之后可能不会减少该索引中的碎片。有关混合区的详细信息，请参阅页和区。
|
示例
下面的示例查询 sys.dm_db_index_physical_stats 动态管理函数以返回 Production.Product 表的所有索引的平均碎片。通过使用上面的表，建议的解决方法是重新组织 PK_Product_ProductID 并重新生成其他索引。
 复制代码 | |
|---|---|
USE AdventureWorks2008R2;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
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_Product_ProductID 15.076923076923077 2 AK_Product_ProductNumber 50.0 3 AK_Product_Name 66.666666666666657 4 AK_Product_rowguid 50.0 (4 row(s) affected) |
|
重新组织索引
若要重新组织一个或多个索引，可以使用带 REORGANIZE 子句的 ALTER INDEX 语句。此语句可以替代 DBCC INDEXDEFRAG 语句。若要重新组织已分区索引的单个分区，可以使用 ALTER INDEX 的 PARTITION 子句。
重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序（从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。使页有序可以提高索引扫描的性能。索引在分配给它的现有页内重新组织，而不会分配新页。如果索引跨多个文件，将一次重新组织一个文件，不会在文件之间迁移页。
重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于sys.indexes目录视图中的填充因子值。
重新组织进程使用最少的系统资源。而且，重新组织是自动联机执行的。该进程不持有长期阻塞锁，所以不会阻止运行查询或更新。
索引碎片不太多时，可以重新组织索引。请参阅上面的表，了解有关碎片的指导原则。不过，如果索引碎片非常多，重新生成索引则可以获得更好的结果。
大型对象数据类型压缩
重新组织索引时，除了重新组织一个或多个索引外，默认情况下还将压缩聚集索引或基础表中包含的大型对象数据类型 (LOB)。数据类型 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 都是大型对象数据类型。压缩此数据可以改善磁盘空间使用情况：
- 重新组织指定的聚集索引将压缩该聚集索引的叶级别（数据行）包含的所有 LOB 列。
- 重新组织非聚集索引将压缩该索引中属于非键（包含性）列的所有 LOB 列。
- 如果指定 ALL，将重新组织与指定的表或视图相关联的所有索引，并压缩与聚集索引、基础表或带有包含列的非聚集索引相关联的所有 LOB 列。
- 如果 LOB 列不存在，则忽略 LOB_COMPACTION 子句。
重新生成索引
重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。
可以使用下列方法重新生成聚集索引和非聚集索引：
- 带 REBUILD 子句的 ALTER INDEX。此语句将替换 DBCC DBREINDEX 语句。
- 带 DROP_EXISTING 子句的 CREATE INDEX。
每个方法执行的功能都相同，但如下表所示，也都各有优缺点需要考虑。
| 功能 | ALTER INDEX REBUILD | CREATE INDEX WITH DROP_EXISTING |
|---|---|---|
|
可以通过添加或删除键列、更改列顺序或更改列排序顺序来更改索引定义。* |
否 |
是** |
|
可以设置或修改索引选项。 |
是 |
是 |
|
可以在单个事务中重新生成多个索引。 |
是 |
否 |
|
可以联机重新生成大部分索引类型，而不会阻止运行查询或更新。 |
是 |
是 |
|
已分区索引可以重新分区。 |
否 |
是 |
|
可以将索引移动到另一个文件组中。 |
否 |
是 |
|
需要额外的临时磁盘空间。 |
是 |
是 |
|
重新生成聚集索引的操作将重新生成相关的非聚集索引。 |
否 除非指定关键字 ALL。 |
否 除非更改索引定义。 |
|
可以重新生成强制 PRIMARY KEY 和 UNIQUE 约束的索引，而不用删除并重新创建这些约束。 |
是 |
是 |
|
可以重新生成单个索引分区。 |
是 |
否 |
* 通过在索引定义中指定 CLUSTERED，可以将非聚集索引转换成聚集索引类型。执行此操作时必须将 ONLINE 选项设置为 OFF。不管将 ONLINE 设置成什么，都不支持从聚集索引到非聚集索引的转换。
** 如果通过使用相同的名称、列和排序顺序重新创建索引，则可以省略排序操作。重新生成操作将检查行是否在生成索引时进行了排序。
您也可以先使用 DROP INDEX 语句删除索引，然后使用一个单独的 CREATE INDEX 语句重新创建该索引，通过这种方式重新生成索引。将这些操作作为单独的语句执行有许多缺点，因此不推荐这样做。
在重新生成操作期间禁用非聚集索引以节省磁盘空间
禁用非聚集索引后，将删除索引数据行，但索引定义仍在元数据中。重新生成索引后即启用该索引。如果不禁用非聚集索引，重新生成操作需要足够的临时磁盘空间才能同时存储新旧索引。但是，通过在单独的事务中禁用并重新生成非聚集索引，禁用索引所释放的磁盘空间可重新用于随后的重新生成或其他任何操作。除用于排序的临时磁盘空间外不需要额外空间，用于排序的临时磁盘空间通常是索引大小的 20%。如果非聚集索引在主键上，将自动禁用引用 FOREIGN KEY 的任何活动约束。必须在重新生成索引后手动启用这些约束。有关详细信息，请参阅禁用索引和启用索引和约束指南。
重新生成大型索引
带有多于 128 个区的索引通过两个单独的阶段重新生成：逻辑阶段和物理阶段。在逻辑阶段，索引使用的现有分配单元被标记为取消分配，数据行被复制并排序然后移动到为存储重新生成的索引而创建的新分配单元中。在物理阶段，先前标记为取消分配的分配单元在发生在后台的短事务中被物理删除，而且不需要很多锁。有关详细信息，请参阅删除并重新生成大型对象。
设置索引选项
重新组织索引时不能指定索引选项。但是，通过使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING，可以在重新生成索引时设置下列索引选项：
|
PAD_INDEX |
DROP_EXISTING（仅 CREATE INDEX） |
|
FILLFACTOR |
ONLINE |
|
SORT_IN_TEMPDB |
ALLOW_ROW_LOCKS |
|
IGNORE_DUP_KEY |
ALLOW_PAGE_LOCKS |
|
STATISTICS_NORECOMPUTE |
MAXDOP |
| 注意： |
|---|
| 如果不需要执行排序操作，或者可以在内存中进行排序，则忽略 SORT_IN_TEMPDB 选项。
|
另外，ALTER INDEX 语句中的 SET 子句允许您设置下列索引选项，而不用重新生成索引：
|
ALLOW_PAGE_LOCKS |
IGNORE_DUP_KEY |
|
ALLOW_ROW_LOCKS |
STATISTICS_NORECOMPUTE |
有关详细信息，请参阅设置索引选项。
重新生成或重新组织索引
ALTER INDEX (Transact-SQL)
通过在一个步骤中删除并重新创建索引来重新生成索引
CREATE INDEX (Transact-SQL)
示例
A. 重新生成索引
以下示例将重新生成单个索引。
| 复制代码 | |
|---|---|
USE AdventureWorks2008R2; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO |
|
B. 重新生成表的所有索引并指定选项
下面的示例指定了 ALL 关键字。这将重新生成与表相关联的所有索引。其中指定了三个选项。
| 复制代码 | |
|---|---|
USE AdventureWorks2008R2;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
|
|
C. 通过 LOB 压缩重新组织索引
下面的示例重新组织单个聚集索引。因为该索引在叶级别包含 LOB 数据类型，所以该语句还会压缩所有包含该大型对象数据的页。请注意，不必指定 WITH (LOB_Compaction) 选项，因为默认值为 ON。
| 复制代码 | |
|---|---|
USE AdventureWorks2008R2; GO ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE ; GO |
|
