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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

您可以使用下列步骤估计存储聚集索引中的数据所需的空间大小:You can use the following steps to estimate the amount of space that is required to store data in a clustered index:

  1. 计算存储聚集索引叶级数据所用的空间。Calculate the space used to store data in the leaf level of the clustered index.

  2. 计算存储聚集索引的索引信息所用的空间。Calculate the space used to store index information for the clustered index.

  3. 对计算出的值求和。Total the calculated values.

步骤 1。Step 1. 计算在叶级别存储数据所用的空间Calculate the Space Used to Store Data in the Leaf Level

  1. 指定表中显示的行数:Specify the number of rows that will be present in the table:

    *Num_Rows _ = 表中的行数*Num_Rows _ = number of rows in the table

  2. 指定固定长度和可变长度列的数量,并计算存储所需的空间:Specify the number of fixed-length and variable-length columns and calculate the space that is required for their storage:

    计算每组列在数据行中所占据的空间。Calculate the space that each of these groups of columns occupies within the data row. 列的大小取决于数据类型和长度规定。The size of a column depends on the data type and length specification.

    Num_Cols = 总列数(固定长度和可变长度)Num_Cols = total number of columns (fixed-length and variable-length)

    Fixed_Data_Size = 所有固定长度列的总字节大小Fixed_Data_Size = total byte size of all fixed-length columns

    Num_Variable_Cols = 可变长度列数Num_Variable_Cols = number of variable-length columns

    Max_Var_Size = 所有可变长度列的最大字节大小Max_Var_Size = maximum byte size of all variable-length columns

  3. 如果聚集索引不唯一,请考虑 _uniqueifier* 列:If the clustered index is nonunique, account for the _uniqueifier* column:

    唯一标识符是可为 Null 的可变长度列。The uniqueifier is a nullable, variable-length column. 在具有非唯一键值的行中,它非 Null 而且大小为 4 个字节。It will be nonnull and 4 bytes in size in rows that have nonunique key values. 此值是索引键的一部分,用于确保每一行都具有唯一的键值。This value is part of the index key and is required to make sure that every row has a unique key value.

    Num_Cols _ = _Num_Cols*_ + 1Num_Cols _ = _Num_Cols*_ + 1

    Num_Variable_Cols = Num_Variable_Cols + 1Num_Variable_Cols = Num_Variable_Cols + 1

    Max_Var_Size = Max_Var_Size + 4Max_Var_Size = Max_Var_Size + 4

    这些修改假定所有值都不是唯一的。These modifications assume that all values will be nonunique.

  4. 保留行中称为 Null 位图的部分以管理列的为空性。Part of the row, known as the null bitmap, is reserved to manage column nullability. 计算其大小:Calculate its size:

    Null_Bitmap = 2 + ((Num_Cols + 7) / 8)Null_Bitmap = 2 + ((Num_Cols + 7) / 8)

    仅使用上述表达式中的整数部分,而放弃所有余数。Only the integer part of the previous expression should be used; discard any remainder.

  5. 计算可变长度数据的大小:Calculate the variable-length data size:

    如果表中有可变长度列,请确定在行中存储这些列需使用的空间:If there are variable-length columns in the table, determine how much space is used to store the columns within the row:

    Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_SizeVariable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

    添加到 Max_Var_Size 中的字节用于跟踪每个可变列。The bytes added to Max_Var_Size are for tracking each variable column. 此公式假设所有可变长度列均百分之百充满。This formula assumes that all variable-length columns are 100 percent full. 如果预计可变长度列占用的存储空间比例较低,则可以按照该比例调整 Max_Var_Size 值,从而对整个表大小得出一个更准确的估计。If you anticipate that a smaller percentage of the variable-length column storage space will be used, you can adjust the Max_Var_Size value by that percentage to yield a more accurate estimate of the overall table size.

    备注

    你可以组合 _*varchar**、nvarchar、varbinary 或 sql_variant 列,使定义的表的总宽度超过 8,060 字节。 You can combine _*varchar**, nvarchar, varbinary, or sql_variant columns that cause the total defined table width to exceed 8,060 bytes. 对于 varcharvarbinarysql_variant 中的每一列,其长度不能超过 8,000 字节,对于 nvarchar 列,不能超过 4,000 字节。The length of each one of these columns must still fall within the limit of 8,000 bytes for a varchar, varbinary, or sql_variant column, and 4,000 bytes for nvarchar columns. 但是,表中这些列的组合宽度可超过 8,060 字节的限制。However, their combined widths may exceed the 8,060 byte limit in a table.

    如果没有可变长度列,请将 *Variable_Data_Size _ 设置为 0。If there are no variable-length columns, set *Variable_Data_Size _ to 0.

  6. 计算总的行大小:Calculate the total row size:

    Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

    值 4 是数据行的行标题的开销。The value 4 is the row header overhead of a data row.

  7. 下一步,计算每页的行数(每页有 8096 个可用字节):Calculate the number of rows per page (8096 free bytes per page):

    Rows_Per_Page = 8096 / (Row_Size + 2)Rows_Per_Page = 8096 / (Row_Size + 2)

    因为行不跨页,所以每页的行数应向下舍入到最接近的整数。Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. 公式中的数值 2 是计算行数时引入的行大小余量。The value 2 in the formula is for the row's entry in the slot array of the page.

  8. 根据指定的 填充因子 计算每页保留的空行数:Calculate the number of reserved free rows per page, based on the fill factor specified:

    Free_Rows_Per_Page = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)Free_Rows_Per_Page = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)

    计算中使用的填充因子为整数值,而不是百分比。The fill factor used in the calculation is an integer value instead of a percentage. 因为行不跨页,所以每页的行数应向下舍入到最接近的整数。Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. 填充因子增大时,每页将存储更多的数据,因此页数将减少。As the fill factor grows, more data will be stored on each page and there will be fewer pages. 公式中的数值 2 是计算行数时引入的行大小余量。The value 2 in the formula is for the row's entry in the slot array of the page.

  9. 计算存储所有行所需的页数:Calculate the number of pages required to store all the rows:

    Num_Leaf_Pages = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page )Num_Leaf_Pages = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)

    估计的页数应向上舍入到最接近的整数。The number of pages estimated should be rounded up to the nearest whole page.

  10. 计算在叶级别中存储数据所需的空间大小(每页共有 8192 个字节):Calculate the amount of space that is required to store the data in the leaf level (8192 total bytes per page):

    Leaf_space_used = 8192 x Num_Leaf_PagesLeaf_space_used = 8192 x Num_Leaf_Pages

步骤 2.Step 2. 计算存储索引信息所用的空间Calculate the Space Used to Store Index Information

您可以使用下列步骤估计存储索引的较高级别所需的空间大小:You can use the following steps to estimate the amount of space that is required to store the upper levels of the index:

  1. 指定索引键中固定长度和可变长度列的数量,并计算存储所需的空间:Specify the number of fixed-length and variable-length columns in the index key and calculate the space that is required for their storage:

    索引键列可以包括固定长度和可变长度列。The key columns of an index can include fixed-length and variable-length columns. 若要估计内部级别索引行的大小,请计算每组列在索引行中所占据的空间。To estimate the interior level index row size, calculate the space that each of these groups of columns occupies within the index row. 列的大小取决于数据类型和长度规定。The size of a column depends on the data type and length specification.

    Num_Key_Cols = 总键列数(固定长度和可变长度)Num_Key_Cols = total number of key columns (fixed-length and variable-length)

    Fixed_Key_Size = 所有固定长度键列的总字节大小Fixed_Key_Size = total byte size of all fixed-length key columns

    Num_Variable_Key_Cols = 可变长度键列数Num_Variable_Key_Cols = number of variable-length key columns

    Max_Var_Key_Size = 所有可变长度键列的最大字节大小Max_Var_Key_Size = maximum byte size of all variable-length key columns

  2. 如果索引不唯一,则请说明所需的任意唯一标识符:Account for any uniqueifier needed if the index is nonunique:

    唯一标识符是可为 Null 的可变长度列。The uniqueifier is a nullable, variable-length column. 它将是非 Null 的,在具有非唯一索引键值的行中的大小是 4 个字节。It will be nonnull and 4 bytes in size in rows that have nonunique index key values. 此值是索引键的一部分,用于确保每一行都具有唯一的键值。This value is part of the index key and is required to make sure that every row has a unique key value.

    Num_Key_Cols = Num_Key_Cols + 1Num_Key_Cols = Num_Key_Cols + 1

    Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1

    Max_Var_Key_Size = Max_Var_Key_Size + 4Max_Var_Key_Size = Max_Var_Key_Size + 4

    这些修改假定所有值都不是唯一的。These modifications assume that all values will be nonunique.

  3. 计算 Null 位图大小:Calculate the null bitmap size:

    如果索引键中有允许为 Null 的列,则索引行的一部分将为 Null 位图保留。If there are nullable columns in the index key, part of the index row is reserved for the null bitmap. 计算其大小:Calculate its size:

    Index_Null_Bitmap = 2 + ((索引行中的列数 + 7) / 8)Index_Null_Bitmap = 2 + ((number of columns in the index row + 7) / 8)

    仅应使用上述表达式中的整数部分,Only the integer part of the previous expression should be used. 而放弃所有余数。Discard any remainder.

    如果没有可为 Null 的键列,请将 Index_Null_Bitmap 设置为 0。If there are no nullable key columns, set Index_Null_Bitmap to 0.

  4. 计算可变长度数据的大小:Calculate the variable-length data size:

    如果索引中有可变长度列,请确定在索引行中存储这些列需使用的空间:If there are variable-length columns in the index, determine how much space is used to store the columns within the index row:

    Variable_Key_Size = 2 + (Num_Variable_Key_Cols x 2) + Max_Var_Key_SizeVariable_Key_Size = 2 + (Num_Variable_Key_Cols x 2) + Max_Var_Key_Size

    添加到 Max_Var_Key_Size 中的字节用于跟踪每个可变长度列。The bytes added to Max_Var_Key_Size are for tracking each variable-length column. 此公式假设所有可变长度列均百分之百充满。This formula assumes that all variable-length columns are 100 percent full. 如果预计可变长度列占用的存储空间比例较低,则可以按照该比例调整 Max_Var_Key_Size 值,从而对整个表大小得出一个更准确的估计。If you anticipate that a smaller percentage of the variable-length column storage space will be used, you can adjust the Max_Var_Key_Size value by that percentage to yield a more accurate estimate of the overall table size.

    如果没有可变长度列,请将 Variable_Key_Size 设置为 0。If there are no variable-length columns, set Variable_Key_Size to 0.

  5. 计算索引行大小:Calculate the index row size:

    Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1(对应于索引行的行标题开销)+ 6(对应于子页 ID 指针)Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 (for row header overhead of an index row) + 6 (for the child page ID pointer)

  6. 下一步,计算每页的索引行数(每页有 8096 个可用字节):Calculate the number of index rows per page (8096 free bytes per page):

    Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

    因为索引行不能跨页,所以每页的索引行数应向下舍入到最接近的整数。Because index rows do not span pages, the number of index rows per page should be rounded down to the nearest whole row. 公式中的 2 是计算行数时引入的行大小余量。The 2 in the formula is for the row's entry in the page's slot array.

  7. 计算索引中的级别数:Calculate the number of levels in the index:

    Non-leaf_Levels = 1 + log (Index_Rows_Per_Page) (Num_Leaf_Pages / Index_Rows_Per_Page)Non-leaf_Levels = 1 + log (Index_Rows_Per_Page) (Num_Leaf_Pages / Index_Rows_Per_Page)

    将此值向上舍入到最接近的整数。Round this value up to the nearest whole number. 此值不包括聚集索引的叶级别。This value does not include the leaf level of the clustered index.

  8. 计算索引中的非叶页数:Calculate the number of non-leaf pages in the index:

    Num_Index_Pages = ∑Level * (Num_Leaf_Pages / (Index_Rows_Per_Page_ ^Level )) *Num_Index_Pages =_ ∑Level *(Num_Leaf_Pages / (Index_Rows_Per_Page_ ^Level))*_

    其中,1 <= Level <= Non-leaf_Levelswhere 1 <= Level <= Non-leaf_Levels

    将每个被加数向上舍入到最接近的整数。Round each summand up to the nearest whole number. 由于是个简单示例,请考虑使用 Num_Leaf_Pages = 1000 和 Index_Rows_Per_Page = 25 的索引。As a simple example, consider an index where Num_Leaf_Pages = 1000 and Index_Rows_Per_Page = 25. 页级别以上的第一个索引级别存储 1000 个索引行,即每个叶页一个索引行,每页可以包括 25 个索引行。The first index level above the leaf level stores 1000 index rows, which is one index row per leaf page, and 25 index rows can fit per page. 这意味着存储这 1000 个索引行需要 40 页。This means that 40 pages are required to store those 1000 index rows. 下一级索引必须存储 40 行。The next level of the index has to store 40 rows. 这意味着需要 2 页。This means it requires 2 pages. 最后一级索引必须存储 2 行。The final level of the index has to store 2 rows. 这意味着需要 1 页。This means it requires 1 page. 这就提供了 43 个非叶索引页。This gives 43 non-leaf index pages. 如果将这些数用到前面的公式中,结果如下:When these numbers are used in the previous formulas, the outcome is as follows:

    Non-leaf_Levels = 1 + log(25) (1000 / 25) = 3Non-leaf_Levels = 1 + log(25) (1000 / 25) = 3

    Num_Index_Pages = 1000/(25^3)+ 1000/(25^2) + 1000/(25^1) = 1 + 2 + 40 = 43,这是上面的示例中所述的页数。Num_Index_Pages = 1000/(25^3)+ 1000/(25^2) + 1000/(25^1) = 1 + 2 + 40 = 43, which is the number of pages described in the example.

  9. 计算索引的大小(每页总共有 8192 个字节):Calculate the size of the index (8192 total bytes per page):

    Index_Space_Used = 8192 x Num_Index_PagesIndex_Space_Used = 8192 x Num_Index_Pages

步骤 3.Step 3. 对计算出的值求和Total the Calculated Values

对从前面两个步骤中得到的值求和:Total the values obtained from the previous two steps:

群集索引大小(字节)= Leaf_Space_Used + Index_Space_usedClustered index size (bytes) = Leaf_Space_Used + Index_Space_used

此计算不考虑以下因素:This calculation does not consider the following:

  • 分区Partitioning

    分区的空间开销很小,但是计算复杂。The space overhead from partitioning is minimal, but complex to calculate. 是否包括它并不重要。It is not important to include.

  • 分配页Allocation pages

    至少有一个 IAM 页用于跟踪为堆分配的页,但是空间开销很小,并且没有算法可以精确地计算出要使用的 IAM 页数。There is at least one IAM page used to track the pages allocated to a heap, but the space overhead is minimal and there is no algorithm to deterministically calculate exactly how many IAM pages will be used.

  • 大型对象 (LOB) 值Large object (LOB) values

    精确确定存储 LOB 数据类型 _*varchar(max)**、varbinary(max)、nvarchar(max)、text、ntext、xml 和 image 值所用的空间量的算法非常复杂。 The algorithm to determine exactly how much space will be used to store the LOB data types _*varchar(max)**, varbinary(max), nvarchar(max), text, ntext, xml, and image values is complex. 只需加上所期望的 LOB 值的平均大小,再乘以 Num_Rows,然后再加上群集索引的总大小就可以了。It is sufficient to just add the average size of the LOB values that are expected, multiply by Num_Rows, and add that to the total clustered index size.

  • 压缩Compression

    无法预先计算压缩索引的大小。You cannot pre-calculate the size of a compressed index.

  • 稀疏列Sparse columns

    有关稀疏列的空间要求的信息,请参阅 Use Sparse ColumnsFor information about the space requirements of sparse columns, see Use Sparse Columns.

另请参阅See Also

描述的聚集索引和非聚集索引 Clustered and Nonclustered Indexes Described
估计表的大小 Estimate the Size of a Table
创建聚集索引 Create Clustered Indexes
创建非聚集索引 Create Nonclustered Indexes
估计非聚集索引的大小 Estimate the Size of a Nonclustered Index
估计堆的大小 Estimate the Size of a Heap
估计数据库的大小Estimate the Size of a Database