估计非聚集索引的大小

按照以下步骤估计存储非聚集索引所需的空间量：

1. 计算用于步骤 2 和 3 的变量。

2. 计算用于将索引信息存储在非聚集索引叶级别的空间。

3. 计算用于在非聚集索引的非叶级别中存储索引信息的空间。

4. 计算值总数。

步骤 1. 计算步骤 2 和 3 中使用的变量

可以使用以下步骤计算用于估计存储索引上限所需的空间量的变量。

1. 指定表中将存在的行数：

   Num_Rows = 表中的行数

2. 在索引键中指定固定长度和可变长度列的数目，并计算其存储所需的空间：

   索引的键列可以包括固定长度和可变长度列。 若要估计内部级别索引行大小，请计算这些列组在索引行中占用的空间。 列的大小取决于数据类型和长度规范。

   Num_Key_Cols = 键列的总数（固定长度和可变长度）

   Fixed_Key_Size = 所有固定长度键列的总字节大小

   Num_Variable_Key_Cols = 可变长度键列数

   Max_Var_Key_Size = 所有可变长度键列的最大字节大小

3. 考虑索引非统一时所需的数据行定位符：

   如果非聚集索引不统一，则数据行定位符将与非聚集索引键组合在一起，为每个行生成唯一键值。

   如果非聚集索引位于堆上，则数据行定位符为堆 RID。 这是 8 个字节的大小。

   Num_Key_Cols = Num_Key_Cols + 1

   = Num_Variable_Key_ColsNum_Variable_Key_Cols + 1

   Max_Var_Key_Size = Max_Var_Key_Size + 8

   如果非聚集索引超过聚集索引，则数据行定位符是聚类分析键。 必须与非聚集索引键组合的列是聚集键中尚未存在于非聚集索引键列中的列。

   = Num_Key_ColsNum_Key_Cols + 非聚集索引键列集中的聚集键列数（如果聚集索引非统一）

   Fixed_Key_Size = Fixed_Key_Size + 不在非聚集索引键列集中的固定长度聚类键列的总字节大小

   = Num_Variable_Key_ColsNum_Variable_Key_Cols + 非聚集索引键列集中不包括的可变长度聚类键列数（如果聚集索引为非唯一索引则加1）

   Max_Var_Key_Size = Max_Var_Key_Size + 可变长度聚集键列的最大字节大小，不在非聚集索引键列集中（如果聚集索引不是唯一索引，则加 4）

4. 行的一部分（称为 null 位图）可以保留以管理列可为 null 性。 计算其大小：

   如果索引键中有可以为 null 的列，包括步骤 1.3 中所述的任何必要聚集键列，则索引行的一部分将保留为 null 位图。

   Index_Null_Bitmap = 2 + （索引行中的列数 + 7） / 8）

   只应使用上一个表达式的整数部分。 放弃任何余数。

   如果没有可以为 null 的键列，请将 Index_Null_Bitmap 设置为 0。

5. 计算可变长度数据大小：

   如果索引键中有可变长度的列，包括任何必要的聚集索引键列，请确定用于在索引行中存储列的空间：

   Variable_Key_Size = 2 + （Num_Variable_Key_Cols x 2） + Max_Var_Key_Size

   添加到 Max_Var_Key_Size 的字节用于跟踪每个变量列。此公式假定所有可变长度列都已满 100%。 如果预计将使用较小百分比的可变长度列存储空间，则可以按该百分比调整 Max_Var_Key_Size 值，从而更准确地估计整个表大小。

   如果没有可变长度的列，请将 Variable_Key_Size 设置为 0。

6. 计算索引行大小：

   = Index_Row_SizeFixed_Key_Size + + Variable_Key_SizeIndex_Null_Bitmap + 1 （索引行的行标题开销） + 6 （对于子页 ID 指针）

7. 计算每页索引行数（每页 8096 个可用字节）：

   Index_Rows_Per_Page = 8096 / （Index_Row_Size + 2）

   由于索引行不跨页，因此每个页的索引行数应向下舍入到最接近的整行。 公式中的 2 表示页面槽数组中行的一个条目。

步骤 2. 计算用于在叶级别存储索引信息的空间

可以使用以下步骤来估计存储索引叶级别所需的空间量。 需要步骤 1 中保留的值才能完成此步骤。

1. 在叶级别指定固定长度和可变长度列的数目，并计算其存储所需的空间：

   注释

   可以通过在索引键列之外增加非键列，来扩展非聚集索引。 这些附加列只存储于非聚集索引的叶子节点级别。 有关详细信息，请参阅 Create Indexes with Included Columns。

   注释

   可以合并varchar、nvarchar、varbinary或sql_variant列，这可能导致定义的表宽度总数超过 8,060 字节。 这些列中的每一列的长度仍然必须限制在 8,000 字节以内（对于 varchar、varbinary 或 sql_variant 列），而对于 nvarchar 列则限制在 4,000 字节以内。 但是，它们的组合宽度可能超过表中的 8,060 字节限制。 这也适用于包含列的非聚集索引叶行。

   如果非聚集索引不包含任何包含的列，请使用步骤 1 中的值，包括步骤 1.3 中确定的任何修改：

   Num_Leaf_Cols = Num_Key_Cols

   固定叶片大小 = 固定键大小

   Num_Variable_Leaf_Cols = Num_Variable_Key_Cols

   最大变量叶大小 = 最大变量键大小

   如果非聚集索引中确实包含了列，则将适当的值添加到步骤 1 的数值中，包括步骤 1.3 中的任何修改。 列的大小取决于数据类型和长度规范。 有关详细信息，请参阅 数据类型（Transact-SQL）。

   Num_Leaf_Cols = Num_Key_Cols + 包含列数

   Fixed_Leaf_Size = Fixed_Key_Size + 固定长度包含列的总字节大小

   Num_Variable_Leaf_Cols = Num_Variable_Key_Cols + 包含的可变长度列数

   Max_Var_Leaf_Size = Max_Var_Key_Size + 可变长度列包括的最大字节大小

2. 考虑数据行定位符：

   如果非聚集索引不统一，则已在步骤 1.3 中考虑了数据行定位符的开销，无需进行其他修改。 请转至下一步。

   如果非聚集索引是唯一的，则必须在叶级别的所有行中考虑数据行定位符。

   如果非聚集索引位于堆上，则数据行定位符为堆 RID（大小 8 字节）。

   = Num_Leaf_ColsNum_Leaf_Cols + 1

   Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + 1

   = Max_Var_Leaf_SizeMax_Var_Leaf_Size + 8

   如果非聚集索引基于聚集索引构建，则数据行定位符是聚集键。 必须与非聚集索引键组合的列是聚集键中尚未存在于非聚集索引键列中的列。

   = Num_Leaf_ColsNum_Leaf_Cols + 不在非聚集索引键列集中的聚簇键列数（如果聚簇索引是非唯一的，则加 1）

   Fixed_Leaf_Size = Fixed_Leaf_Size + 非聚集索引键列集中固定长度聚类分析键列的数目

   Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + 非聚集索引键列集中的可变长度聚类分析键列数（如果聚集索引不是非聚集索引，则为 1）

   = Max_Var_Leaf_SizeMax_Var_Leaf_Size + 不在非聚集索引键列集中变量长度聚类键列的字节大小（如果聚集索引是非唯一的，则加4字节）

3. 计算空值位图的大小：

   Leaf_Null_Bitmap = 2 + （（Num_Leaf_Cols + 7） / 8）

   只应使用上一个表达式的整数部分。 放弃任何余数。

4. 计算可变长度数据大小：

   如果索引键中有可变长度的列，包括步骤 2.2 中所述的任何必要的聚集键列，请确定这些列在索引行中存储所需的空间：

   Variable_Leaf_Size = 2 + （Num_Variable_Leaf_Cols x 2） + Max_Var_Leaf_Size

   添加到 Max_Var_Key_Size 的字节用于跟踪每个变量列。此公式假定所有可变长度列都已满 100%。 如果预计将使用较小百分比的可变长度列存储空间，则可以按该百分比调整 Max_Var_Leaf_Size 值，从而更准确地估计整个表大小。

   如果没有可变长度的列， 请将Variable_Leaf_Size 设置为 0。

5. 计算索引行大小：

   叶行大小 = + 固定叶大小 + + 可变叶大小 + + 叶空位图 + 1 （用于索引行行标题开销） + 6 （用于子页面 ID 指针）

6. 计算每页索引行数（每页 8096 个可用字节）：

   Leaf_Rows_Per_Page = 8096 / （Leaf_Row_Size + 2）

   由于索引行不跨页，因此每个页的索引行数应向下舍入到最接近的整行。 公式中的 2 用于页面槽数组中行的条目。

7. 根据指定的 填充因子 计算每页保留的免费行数：

   Free_Rows_Per_Page = 8096 x （（100 - Fill_Factor） / 100） / （Leaf_Row_Size + 2）

   计算中使用的填充因子是整数值，而不是百分比。 由于行不跨页，因此每页的行数应向下舍入到最接近的整行。 随着填充因子的增长，每个页面上将存储更多的数据，并且页面将减少。 公式中的 2 用于页面槽数组中行的条目。

8. 计算存储所有行所需的页数：

   Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)

   估计的页数应向上舍入到最接近的整页。

9. 计算索引的大小（每页 8192 个总字节）：

   Leaf_Space_Used = 8192 x Num_Leaf_Pages

步骤 3. 计算用于在非叶级别中存储索引信息的空间

按照以下步骤估计存储索引的中间级别和根级别所需的空间量。 需要步骤 2 和 3 中保留的值才能完成此步骤。

1. 计算索引中非叶子节点的层数：

   非叶级别 = 1 + 对数 Index_Rows_Per_Page （Num_Leaf_Pages / Index_Rows_Per_Page)

   将此值向上取整为最接近的整数值。 此值不包括非聚集索引的叶级别。

2. 计算索引中非叶子页面的数量：

   Num_Index_Pages = ∑级别 (Num_Leaf_Pages/Index_Rows_Per_Page^级别)，其中 1 <= 级别 <= Levels

   将每个求和结果向上舍入到最接近的整数。 作为简单的示例，请考虑一个索引，其中Num_Leaf_Pages = 1000，以及Index_Rows_Per_Page = 25。 叶级别上方的第一个索引级别存储 1000 个索引行，每个叶页有一个索引行，每个页可以容纳 25 个索引行。 这意味着需要 40 页来存储这 1000 个索引行。 索引的下一级别必须存储 40 行。 这意味着它需要 2 页。 索引的最终级别必须存储 2 行。 这意味着它需要 1 页。 这会生成 43 个非叶节点索引页。 在前面的公式中使用这些数字时，结果如下所示：

   非叶等级 = 1 + log25 (1000 / 25) = 3

   Num_Index_Pages = 1000/（25³）+ 1000/（25²） + 1000/（25¹） = 1 + 2 + 40 = 43，这是示例中所述的页数。

3. 计算索引的大小（每页 8192 个总字节）：

   Index_Space_Used = 8192 x Num_Index_Pages

步骤 4. 计算值总数

前两个步骤中获取的值总数：

非聚集索引大小（字节）= Leaf_Space_Used + Index_Space_used

此计算不考虑以下事项：

• 分区

  分区的空间开销很小，但计算起来较为复杂。 包括这一点并不重要。

• 分配页

  用于跟踪分配给堆的页面的至少有一个 IAM 页，尽管空间开销很小，但没有算法可以准确计算将使用多少个 IAM 页。

• 大型对象 （LOB） 值

  用于确定将有多少空间用于存储 LOB 数据类型varchar(max)、varbinary(max)、nvarchar(max)、text、ntext和xmlimage值的算法很复杂。 只需将预期的 LOB 值的平均大小添加起来，然后乘以 Num_Rows，再将结果加到非聚集索引的总大小中。

• 压缩

  无法预先计算压缩索引的大小。

• 稀疏列

  有关稀疏列的空间要求的信息，请参阅 “使用稀疏列”。

另请参阅

描述的聚集索引和非聚集索引
创建非聚集索引
创建聚集索引
估计表的大小
估计聚集索引的大小
估计堆的大小
估计数据库的大小