通过

联机索引操作准则

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 预览版中的 SQL 数据库

执行联机索引操作时，请按照下列指南进行：

  • 如果基础表包含下列大型对象 (LOB) 数据类型： image、 ntext和 text，则必须脱机创建、重新生成或删除聚集索引。

  • 如果表具有使用 LOB 数据类型的列，但在索引定义中这些列既不作为键列也不作为包含列，则可以联机创建非唯一非聚集索引。

  • 无法为本地临时表联机创建、重新生成或删除索引。 全局临时表的索引则没有此限制。

  • 可以通过使用 RESUMABLE 或 ALTER INDEX 的子句作为一个可恢复操作来启动联机索引操作。 可恢复的索引操作可以在发生意外故障、数据库故障转移或执行 ALTER INDEX PAUSE 命令后重启，并从中断点继续。

Note

在 Microsoft SQL Server 的各版本中均不提供联机索引操作。 有关 SQL Server 各个版次支持的功能列表，请参阅 SQL Server 2022 的各个版次及其支持的功能。

下表显示了可以联机执行的索引操作、从这些联机操作中排除的索引以及可恢复的索引限制。 其中还包括其他限制。

联机索引操作 排除的索引 其他限制
ALTER INDEX REBUILD 禁用的聚集索引或禁用的索引视图

XML 索引

对本地临时表的索引		 当表中包含不能执行联机操作的索引时，指定关键字 ALL 可能会导致操作失败。

有关重新生成禁用索引的其他限制。 有关详细信息，请参阅 “禁用索引和约束”。
CREATE INDEX XML 索引

视图的初始唯一聚集索引

对本地临时表的索引
CREATE INDEX WITH DROP_EXISTING 禁用的聚集索引或禁用的索引视图

对本地临时表的索引

XML 索引
DROP INDEX 禁用的索引

XML 索引

非聚集索引

对本地临时表的索引		 无法在一条语句中指定多个索引。
ALTER TABLE ADD CONSTRAINT（PRIMARY KEY 或 UNIQUE） 对本地临时表的索引

聚集索引		 每次只允许一个子句。 例如，无法在同一个 PRIMARY KEY 语句中添加和删除UNIQUE 或 ALTER TABLE 约束。
ALTER TABLE DROP CONSTRAINT（PRIMARY KEY 或 UNIQUE） 聚集索引

在进行联机索引操作时，无法修改、截断或删除该底层表。

在创建或删除聚集索引时指定的联机选项设置（ON 或 OFF）适用于任何必须重新生成的非聚集索引。 例如，如果聚集索引是使用联机 CREATE INDEX WITH DROP_EXISTING, ONLINE = ON构建的，则所有关联的非聚集索引也会联机重新创建。

联机创建或重新生成 UNIQUE 索引时，索引生成器和并发用户事务可能会尝试插入相同的键，从而违反唯一性。 如果在源表中的原始行移至新的索引之前，用户输入的行插入到了新的索引（目标），则联机索引操作将失败。

虽然并不常见，但联机索引操作在与数据库更新进行交互时会因为用户或应用程序的活动而导致死锁。 在这些罕见情况下，用户或应用程序活动被选为死锁受害者。

只有在创建多个新的非聚集索引或重新组织非聚集索引时，才能对同一个表或视图执行并发联机索引 DDL 操作。 同一时间执行的所有其他联机索引操作都将失败。 例如，对同一个表联机重新生成现有的索引时，不能联机创建新的索引。

如果索引包含大对象类型的列，并且相同的事务在联机索引操作开始之前进行了数据修改，则无法进行联机操作。 若要解决此问题，请将联机索引操作移到事务外部，或将其移动到同一事务中任何数据修改之前。

磁盘空间注意事项

联机索引作需要比脱机索引作更多的磁盘空间。

  • 在执行索引创建和索引重新生成操作期间，要生成（或重新生成）的索引需要额外的空间。 通常，此额外空间与索引占用的当前空间相同，但可能会更大或更小，具体取决于当前索引或重新生成索引中使用的压缩。

  • 此外，临时映射索引也需要磁盘空间。 此临时索引是在联机索引操作（创建、重新生成或删除聚集索引）中使用的。

  • 删除联机聚集索引与创建（或重新生成）联机聚集索引需要的空间一样多。

有关详细信息，请参阅索引 DDL 操作的磁盘空间要求。

性能注意事项

尽管联机索引作允许并发用户更新活动，但如果更新活动繁重，索引作可能需要更长的时间。 通常，无论并发更新活动的级别如何，联机索引操作都将比相应的脱机索引操作更慢。

由于源结构和目标结构都在联机索引作期间保持，因此插入、更新和删除事务的资源使用量可能会增加一倍。 这会导致在索引操作过程中性能降低和使用较多的资源，尤其是 CPU 时间。 联机索引操作将完整记入日志。

尽管建议联机操作，但仍应评估环境和特定的需要是否满足。 脱机执行索引操作可能是最好的。 这样做可能会在操作过程中限制用户对数据的访问，但是操作将更快地完成并使用较少的资源。

在运行 SQL Server 2016（13.x）及更高版本的多处理器计算机上，索引作可以使用并行性来执行与索引语句关联的扫描和排序作。 可以使用 MAXDOP 索引选项来控制联机索引作的并行度。 通过这种方式，可以在并发用户的资源间平衡索引操作所使用的资源。 有关详细信息，请参阅 配置并行索引作。 有关支持并行索引操作的 SQL Server 版本的更多信息，请参阅“SQL Server 2022 各个版本及其支持的功能”。

由于共享 （S） 锁或架构修改 （Sch-M） 锁保留在索引作的最后阶段，因此在显式用户事务（如 BEGIN TRANSACTION ... COMMIT 块）中运行联机索引作时要小心。 这样做会导致在事务结束之前保留锁，从而可能会阻止其他工作负荷。

如果禁用了索引页锁（使用ALLOW_PAGE_LOCKS = OFF方式），那么在运行MAXDOP大于1时进行联机索引重建可能会增加索引碎片。 有关详细信息，请参阅 工作方式：联机索引重新生成 - 可能造成碎片增加。

事务日志注意事项

脱机或联机执行的大规模索引作可以生成大量的事务日志。 这是因为已完全记录脱机和联机索引重新生成操作。 为确保索引操作可以回滚，事务日志在索引操作完成前无法截断，但该日志可以在索引操作过程中进行备份。

因此，事务日志必须具有足够的空间来存储索引操作事务和所有的并发用户事务，以满足索引操作过程的需要。 有关详细信息，请参阅索引操作的事务日志磁盘空间。

如果启用了 加速数据库恢复（ADR）， 联机索引作不会导致事务日志增长过高。

持久性版本存储注意事项

如果启用了 ADR，则在联机创建或重新生成大型索引时，永久性版本存储 (PVS) 的大小可能会显著增加。 确保数据库有足够的可用空间，以便 PVS 增长。 有关更多信息，请参阅 监控和诊断加速数据库恢复问题。

可恢复索引注意事项

SQL Server 的索引选项 RESUMABLE和 CREATE INDEX 自 SQL Server 2017（14.x）开始引入，而ALTER INDEX和ALTER INDEX自 SQL Server 2019（15.x）开始引入，适用于 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例。 有关详细信息，请参阅 CREATE INDEX 和 ALTER INDEX。

若要使用该 RESUMABLE 选项，还必须使用 ONLINE 该选项。 执行可恢复索引创建或重新生成时，以下准则适用：

  • 你可以更好地掌控索引维护时段的管理、规划和扩展。 为适应维护时段，可多次暂停并重启索引创建或重新生成操作。

  • 可以从索引创建或重新生成失败（例如数据库故障转移或磁盘空间不足）进行恢复，而无需从头开始重新执行索引操作。

  • 索引操作暂停时，原始索引和新创建的索引都需要磁盘空间，并且都需要在 DML 操作期间更新。

  • 不支持 SORT_IN_TEMPDB = ON 选项。

  • 不支持禁用的索引。

Tip

可恢复索引操作不需要大型事务，允许在此操作期间频繁日志截断，并避免大型日志增长。 恢复和完成索引作所需的数据存储在数据库的数据文件中。

通常，可恢复和非可恢复联机索引作之间没有性能差异。 对于可恢复CREATE INDEX，存在一个常量开销，可能会导致较小数据表的操作明显变慢。

暂停可恢复索引操作时

  • 对于大多数读取工作负荷，性能下降并不显著。
  • 对于更新密集型工作负荷，可能会因工作负荷细节而出现一些吞吐量下降。

通常情况下，可恢复和非可恢复的联机索引创建或重新生成之间没有碎片整理质量差异。

当联机索引操作被暂停时，任何需要在包含该已暂停索引的表上获取表级独占 (X) 锁的事务都会失败。 例如，这种情况可能发生在 INSERT ... WITH (TABLOCK) 操作中。 在这种情况下，会出现错误 10637：

Cannot perform this operation on '<object name>' with ID (<object ID>) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

若要解决错误 10637，请从事务中删除 TABLOCK 提示，或取消暂停索引操作，并等待它完成，然后再尝试事务。

联机默认选项

可以通过设置 ELEVATE_ONLINE 或 ELEVATE_RESUMABLE 数据库范围的配置，将联机和可恢复索引操作设置为数据库级别的默认选项。 使用这些默认选项，可以避免意外启动脱机索引作，使表或索引在运行时无法访问。 这两个选项都会导致数据库引擎自动使某些索引操作提升到联机或可断续执行。

可以将任一选项设置为FAIL_UNSUPPORTED或 WHEN_SUPPORTEDOFF。 可以为ELEVATE_ONLINE和ELEVATE_RESUMABLE设置不同的值。 有关详细信息，请参阅 ALTER DATABASE SCOPED CONFIGURATION。

ELEVATE_ONLINE 和 ELEVATE_RESUMABLE 均仅适用于分别支持联机和可恢复语法的 DDL 语句。 例如，如果尝试创建 ELEVATE_ONLINE = FAIL_UNSUPPORTEDXML 索引，该作将脱机运行，因为 XML 索引不支持该 ONLINE 选项。 这些选项仅影响未指定 ONLINE 或 RESUMABLE 选项而提交的 DDL 语句。 例如，通过提交带有 ONLINE = OFF 或 RESUMABLE = OFF 的语句，用户可替代 FAIL_UNSUPPORTED 设置并以脱机和/或不可恢复的方式运行语句。

Note

ELEVATE_ONLINE 并且 ELEVATE_RESUMABLE 不适用于 XML 索引操作。

相关内容