Disk Space Requirements for Index DDL OperationsDisk Space Requirements for Index DDL Operations

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

磁盘空间是创建、重新生成或删除索引时所需考虑的重要因素。Disk space is an important consideration when you create, rebuild, or drop indexes. 磁盘空间不足会降低性能,甚至导致索引操作失败。Inadequate disk space can degrade performance or even cause the index operation to fail. 本主题提供了有助于确定索引数据定义语言 (DDL) 操作所需的磁盘空间量的一般信息。This topic provides general information that can help you determine the amount of disk space required for index data definition language (DDL) operations.

不需要额外的磁盘空间的索引操作Index Operations That Require No Additional Disk Space

以下索引操作不需要额外的磁盘空间:The following index operations require no additional disk space:

  • ALTER INDEX REORGANIZE(但是需要日志空间)。ALTER INDEX REORGANIZE; however, log space is required.

  • DROP INDEX(当删除非聚集索引时)。DROP INDEX when you are dropping a nonclustered index.

  • DROP INDEX(当删除脱机聚集索引而没有指定 MOVE TO 子句并且不存在非聚集索引时)。DROP INDEX when you are dropping a clustered index offline without specifying the MOVE TO clause and nonclustered indexes do not exist.

  • CREATE TABLE(PRIMARY KEY 或 UNIQUE 约束)CREATE TABLE (PRIMARY KEY or UNIQUE constraints)

需要额外的磁盘空间的索引操作Index Operations That Require Additional Disk Space

其他所有 DDL 操作都需要在操作期间使用额外的临时磁盘空间,并需要永久磁盘空间来存储新的索引结构。All other index DDL operations require additional temporary disk space to use during the operation, and permanent disk space to store the new index structure or structures.

创建新的索引结构后,旧(源)结构和新(目标)结构在其相应的文件或文件组中都需要一定的磁盘空间。When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. 旧的结构只有在提交索引创建事务后才会释放。The old structure is not deallocated until the index creation transaction commits.

以下索引 DDL 操作将创建新的索引结构并需要额外的磁盘空间:The following index DDL operations create new index structures and require additional disk space:

  • CREATE INDEXCREATE INDEX

  • CREATE INDEX WITH DROP_EXISTINGCREATE INDEX WITH DROP_EXISTING

  • ALTER INDEX REBUILDALTER INDEX REBUILD

  • ALTER TABLE ADD CONSTRAINT(PRIMARY KEY 或 UNIQUE 约束)ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE)

  • ALTER TABLE DROP CONSTRAINT(PRIMARY KEY 或 UNIQUE)(当约束基于聚集索引时)ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE) when the constraint is based on a clustered index

  • DROP INDEX MOVE TO(仅适用于聚集索引。)DROP INDEX MOVE TO (Applies only to clustered indexes.)

进行排序所需的临时磁盘空间Temporary Disk Space for Sorting

除了源结构和目标结构所需的磁盘空间以外,还需要一定的临时磁盘空间以进行排序,除非查询优化器找到了不需要进行排序的执行计划。Besides the disk space required for the source and target structures, temporary disk space is required for sorting, unless the query optimizer finds an execution plan that does not require sorting.

如果需要进行排序,则每次创建一个新索引时都要进行排序。If sorting is required, sorting occurs one new index at a time. 例如,在单个语句中重新生成聚集索引和相关联的非聚集索引时,将逐个对索引进行排序。For example, when you rebuild a clustered index and associated nonclustered indexes within a single statement, the indexes are sorted one after the other. 因此,进行排序所需的额外的临时磁盘空间只需与操作中最大的索引一样大。Therefore, the additional temporary disk space that is required for sorting only has to be as large as the largest index in the operation. 这个最大索引几乎总是聚集索引。This is almost always the clustered index.

如果将 SORT_IN_TEMPDB 选项设置为 ON,则最大索引必须可由 tempdb容纳。If the SORT_IN_TEMPDB option is set to ON, the largest index must fit into tempdb. 虽然此选项会增加用于创建索引的临时磁盘空间量,但是如果 tempdb 与用户数据库位于不同的磁盘集上时,此选项可减少创建索引所需的时间。Although this option increases the amount of temporary disk space that is used to create an index, it may reduce the time that is required to create an index when tempdb is on a set of disks different from the user database.

如果 SORT_IN_TEMPDB 设置为 OFF(默认值),则每个索引(包括已分区索引)都将在其目标磁盘空间中进行排序;只有新的索引结构需要占用磁盘空间。If SORT_IN_TEMPDB is set to OFF (the default) each index, including partitioned indexes, is sorted in its destination disk space; and only the disk space for the new index structures is required.

有关计算磁盘空间的示例,请参阅 Index Disk Space ExampleFor an example of calculating disk space, see Index Disk Space Example.

联机索引操作所需的临时磁盘空间Temporary Disk Space for Online Index Operations

执行联机索引操作时,需要额外的临时磁盘空间。When you perform index operations online, additional temporary disk space is required.

如果联机创建、重新生成或删除了聚集索引,将创建临时非聚集索引以便把旧书签映射到新书签。If a clustered index is created, rebuilt, or dropped online, a temporary nonclustered index is created to map old bookmarks to new bookmarks. 如果将 SORT_IN_TEMPDB 选项设置为 ON,则将在 tempdb中创建此临时索引。If the SORT_IN_TEMPDB option is set to ON, this temporary index is created in tempdb. 如果 SORT_IN_TEMPDB 设置为 OFF,将使用与目标索引相同的文件组或分区方案。If SORT_IN_TEMPDB is set to OFF, the same filegroup or partition scheme as the target index is used. 临时映射索引针对表中的每行包含一个记录,其内容为新旧书签列,其中包括 uniqueifier 和记录标识符,并且仅包括两种书签中使用的任何列的单个副本。The temporary mapping index contains one record for each row in the table, and its contents is the union of the old and new bookmark columns, including uniqueifiers and record identifiers and including only a single copy of any column used in both bookmarks. 有关联机索引操作的详细信息,请参阅 联机执行索引操作For more information about online index operations, see Perform Index Operations Online.

备注

不能为 DROP INDEX 语句设置 SORT_IN_TEMPDB 选项。The SORT_IN_TEMPDB option cannot be set for DROP INDEX statements. 临时映射索引始终与目标索引创建在同一文件组或分区方案中。The temporary mapping index is always created in the same filegroup or partition scheme as the target index.

联机索引操作使用行版本控制来使索引操作不受其他事务所做的修改的影响。Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. 这就不需要对已经读取的行请求共享锁。This avoids the need for requesting share locks on rows that have been read. 在联机索引操作期间,并发的用户更新和删除操作需要一定的空间以用于 tempdb中的版本记录。Concurrent user update and delete operations during online index operations require space for version records in tempdb. 有关详细信息,请参阅 联机索引操作For more information, see Perform Index Operations Online .

索引磁盘空间示例Index Disk Space Example

索引操作的事务日志磁盘空间Transaction Log Disk Space for Index Operations

估计表的大小Estimate the Size of a Table

估计聚集索引的大小Estimate the Size of a Clustered Index

估计非聚集索引的大小Estimate the Size of a Nonclustered Index

估计堆的大小Estimate the Size of a Heap

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)DROP INDEX (Transact-SQL)

为索引指定填充因子Specify Fill Factor for an Index

重新组织和重新生成索引Reorganize and Rebuild Indexes