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

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

无论什么时候创建、重新生成或删除索引,在相应的文件和文件组中都需要用于存储旧(源)结构和新(目标)结构的磁盘空间。Whenever an index is created, rebuilt, or dropped, 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. 还可能需要附加临时磁盘空间以进行排序操作。Additional temporary disk space for sorting operations may also be needed. 有关详细信息,请参阅 Disk Space Requirements for Index DDL OperationsFor more information, see Disk Space Requirements for Index DDL Operations.

在本示例中,将确定创建聚集索引需要的磁盘空间。In this example, disk space requirements to create a clustered index are determined.

创建聚集索引之前假定满足以下条件:Assume the following conditions are true before creating the clustered index:

  • 现有表(堆)包含 100 万行。The existing table (heap) contains 1 million rows. 每行长度为 200 字节。Each row is 200 bytes long.

  • 非聚集索引 A 包含 100 万行。Nonclustered index A contains 1 million rows. 每行长度为 50 字节。Each row is 50 bytes long.

  • 非聚集索引 B 包含 100 万行。Nonclustered index B contains 1 million rows. 每行长度为 80 字节。Each row is 80 bytes long.

  • index create memory 选项设置为 2 MB。The index create memory option is set to 2 MB.

  • 所有现有索引和新索引都使用填充因子值 80。A fill factor value of 80 is used for all existing and new indexes. 这意味着页的 80% 是满的。This means the pages are 80 percent full.

    备注

    由于创建了聚集索引,必须重新生成这两个非聚集索引来用新聚集索引键替换行指示器。As a result of creating a clustered index, the two nonclustered indexes must be rebuilt to replace the row indicator with the new clustered index key.

脱机索引操作所用的磁盘空间计算Disk Space Calculations for an Offline Index Operation

在以下步骤中,将计算在索引操作期间使用的临时磁盘空间和用于存储新索引的永久磁盘空间。In the following steps, both temporary disk space to be used during the index operation and permanent disk space to store the new indexes are calculated. 显示的计算是近似的;结果将向上舍入而且仅考虑索引叶级别的大小。The calculations shown are approximate; results are rounded up and consider only the size of index leaf level. 代字号 (~) 用来表示近似计算。The tilde (~) is used to indicate approximate calculations.

  1. 确定源结构的大小。Determine the size of the source structures.

    堆:100 万 * 200 字节 ~ 200 MBHeap: 1 million * 200 bytes ~ 200 MB

    非聚集索引 A:1 百万 * 50 字节 / 80% ~ 63 MBNonclustered index A: 1 million * 50 bytes / 80% ~ 63 MB

    非聚集索引 B:1 百万 * 80 字节 / 80% ~ 100 MBNonclustered index B: 1 million * 80 bytes / 80% ~ 100 MB

    现有结构的总大小:363 MBTotal size of existing structures: 363 MB

  2. 确定目标索引结构的大小。Determine the size of the target index structures. 假定新聚集键为 24 字节长(包含一个 uniqueifier)。Assume that the new clustered key is 24 bytes long including a uniqueifier. 两个非聚集索引的行指示器(8 字节长)将由此聚集键替换。The row indicator (8 bytes long) in both nonclustered indexes will be replaced by this clustered key.

    聚集索引:1 百万 * 200 字节 / 80% ~ 250 MBClustered index: 1 million * 200 bytes / 80% ~ 250 MB

    非聚集索引 A:1 百万 * (50 - 8 + 24) 字节 / 80% ~ 83 MBNonclustered index A: 1 million * (50 - 8 + 24) bytes / 80% ~ 83 MB

    非聚集索引 B:1 百万 * (80 - 8 + 24) 字节 / 80% ~ 120 MBNonclustered index B: 1 million * (80 - 8 + 24) bytes / 80% ~ 120 MB

    新结构的总大小:453 MBTotal size of new structures: 453 MB

    索引操作期间支持源结构和目标结构所需的总磁盘空间为 816 MB (363 + 453)。Total disk space required to support both the source and target structures for the duration of the index operation is 816 MB (363 + 453). 索引操作提交后将释放当前分配给源结构的空间。The space currently allocated to the source structures will be deallocated after the index operation is committed.

  3. 确定用于排序的附加临时磁盘空间。Determine additional temporary disk space for sorting.

    将显示在 tempdb 中排序所用的空间要求(其中 SORT_IN_TEMPDB 设置为 ON)和在目标位置排序所用的空间要求(其中 SORT_IN_TEMPDB 设置为 OFF)。Space requirements are shown for sorting in tempdb (with SORT_IN_TEMPDB set to ON) and sorting in the target location (with SORT_IN_TEMPDB set to OFF).

    1. SORT_IN_TEMPDB 设置为 ON 时, tempdb 必须有足够磁盘空间以容纳最大的索引(1 百万 * 200 字节 ~ 200 MB)。When SORT_IN_TEMPDB is set to ON, tempdb must have sufficient disk space to hold the largest index (1 million * 200 bytes ~ 200 MB). 在排序操作中没有考虑填充因子。Fill factor is not considered in the sorting operation.

      配置 index create memory 服务器配置选项 值 = 2 MB,附加磁盘空间(在 tempdb 位置)与其相同。Additional disk space (in the tempdb location) equal to the Configure the index create memory Server Configuration Option value = 2 MB.

      将 SORT_IN_TEMPDB 设置为 ON 时临时磁盘空间的总大小 ~ 202 MB。Total size of temporary disk space with SORT_IN_TEMPDB set to ON ~ 202 MB.

    2. SORT_IN_TEMPDB 设置为 OFF(默认值)时,步骤 2 中用于新索引的 250 MB 磁盘空间将用于排序。When SORT_IN_TEMPDB is set to OFF (default), the 250 MB of disk space already considered for the new index in step 2 is used for sorting.

      配置 index create memory 服务器配置选项 值 = 2 MB,附加磁盘空间(在目标位置)与其相同。Additional disk space (in the target location) equal to the Configure the index create memory Server Configuration Option value = 2 MB.

      将 SORT_IN_TEMPDB 设置为 OFF 时临时磁盘空间的总大小 = 2 MB。Total size of temporary disk space with SORT_IN_TEMPDB set to OFF = 2 MB.

使用 tempdb时,将一共需要 1018 MB (816 + 202) 来创建聚集索引和非聚集索引。Using tempdb, a total of 1018 MB (816 + 202) would be needed to create the clustered and nonclustered indexes. 虽然使用 tempdb 增加了用于创建索引的临时磁盘空间量,但是当 tempdb 与用户数据库位于不同的磁盘集上时,它可以减少创建索引所需的时间。Although using tempdb increases the amount of temporary disk space used to create an index, it may reduce the time that is required to create an index when tempdb is on a different set of disks than the user database. 有关使用 tempdb的详细信息,请参阅 用于索引的 SORT_IN_TEMPDB 选项For more information about using tempdb, see SORT_IN_TEMPDB Option For Indexes.

不使用 tempdb时,将一共需要 818 MB (816 + 2) 来创建聚集索引和非聚集索引。Without using tempdb, a total of 818 MB (816+ 2) would be needed to create the clustered and nonclustered indexes.

联机聚集索引操作所用的磁盘空间计算Disk Space Calculations for an Online Clustered Index Operation

联机创建、删除或重新生成聚集索引时,将需要附加磁盘空间以生成和维护临时映射索引。When you create, drop, or rebuild a clustered index online, additional disk space is required to build and maintain a temporary mapping index. 此临时映射索引包含表中每行的一条记录,其内容包含新旧书签列。This temporary mapping index contains one record for each row in the table, and its contents are the union of the old and new bookmark columns.

若要计算联机聚集索引操作所需的磁盘空间,请按照脱机索引操作显示的步骤操作并将结果添加到以下步骤的结果中。To calculate the disk space needed for an online clustered index operation, follow the steps shown for an offline index operation and add those results to the results of the following step.

  • 确定临时映射索引的空间。Determine space for the temporary mapping index.

    在此示例中,旧书签是堆的行 ID (RID)(8 字节),新书签是聚集键(24 字节,包含一个 uniqueifier)。In this example, the old bookmark is the row ID (RID) of the heap (8 bytes) and the new bookmark is the clustering key (24 bytes including a uniqueifier). 在新旧书签之间没有重叠的列。There are no overlapping columns between the old and new bookmarks.

    临时映射索引大小 = 1 百万 * (8 字节 + 24 字节) / 80% ~ 40 MB。Temporary mapping index size = 1 million * (8 bytes + 24 bytes) / 80% ~ 40 MB.

    必须将此磁盘空间添加到目标位置所需磁盘空间(如果 SORT_IN_TEMPDB 设置为 OFF),或添加到 tempdb (如果 SORT_IN_TEMPDB 设置为 ON)。This disk space must be added to the required disk space in the target location if SORT_IN_TEMPDB is set to OFF, or to tempdb if SORT_IN_TEMPDB is set to ON.

有关临时映射索引的详细信息,请参阅 索引 DDL 操作的磁盘空间要求For more information about the temporary mapping index, see Disk Space Requirements for Index DDL Operations.

磁盘空间摘要Disk Space Summary

下表汇总了磁盘空间计算的结果。The following table summarizes the results of the disk space calculations.

索引操作Index operation 以下结构的位置所需的磁盘空间Disk space requirements for the locations of the following structures
SORT_IN_TEMPDB = ON 时的脱机索引操作Offline index operation with SORT_IN_TEMPDB = ON 操作期间的总空间大小:1018 MBTotal space during the operation: 1018 MB

- 现有表和索引:363 MB*-Existing table and indexes: 363 MB*

-
tempdb:202 MB*tempdb: 202 MB*

- 新索引:453 MB-New indexes: 453 MB

操作后所需的总空间大小:453 MBTotal space required after the operation: 453 MB
SORT_IN_TEMPDB = OFF 时的脱机索引操作Offline index operation with SORT_IN_TEMPDB = OFF 操作期间的总空间大小:816 MBTotal space during the operation: 816 MB

- 现有表和索引:363 MB*-Existing table and indexes: 363 MB*

- 新索引:453 MB-New indexes: 453 MB

操作后所需的总空间大小:453 MBTotal space required after the operation: 453 MB
SORT_IN_TEMPDB = ON 时的联机索引操作Online index operation with SORT_IN_TEMPDB = ON 操作期间的总空间大小:1058 MBTotal space during the operation: 1058 MB

- 现有表和索引:363 MB*-Existing table and indexes: 363 MB*

-
tempdb (包含映射索引):242 MB*tempdb (includes mapping index): 242 MB*

- 新索引:453 MB-New indexes: 453 MB

操作后所需的总空间大小:453 MBTotal space required after the operation: 453 MB
SORT_IN_TEMPDB = OFF 时的联机索引操作Online index operation with SORT_IN_TEMPDB = OFF 操作期间的总空间大小:856 MBTotal space during the operation: 856 MB

- 现有表和索引:363 MB*-Existing table and indexes: 363 MB*

- 临时映射索引:40 MB*-Temporary mapping index: 40 MB*

- 新索引:453 MB-New indexes: 453 MB

操作后所需的总空间大小:453 MBTotal space required after the operation: 453 MB

*索引操作提交后将释放此空间。*This space is deallocated after the index operation is committed.

此示例没有考虑任何 tempdb 中所需的附加临时磁盘空间(用于存储并发用户更新和删除操作创建的版本记录)。This example does not consider any additional temporary disk space required in tempdb for version records created by concurrent user update and delete operations.

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

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