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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

可以使用以下步骤估计在堆中存储数据所需的空间量:You can use the following steps to estimate the amount of space that is required to store data in a heap:

  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 total byte size of all variable-length columns

  3. 保留行中称为 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 this expression should be used. 而放弃所有余数。Discard any remainder.

  4. 计算可变长度数据的大小: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_Size Variable_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-length 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.

    备注

    你可以组合 varcharnvarcharvarbinarysql_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. 对于 varchar、nvarchar、varbinary 或 sql_variant 列,每列的长度仍不得超过 8,000 字节 。The length of each one of these columns must still fall within the limit of 8,000 bytes for a varchar, nvarchar, varbinary, or sql_variant column. 但是,表中这些列的组合宽度可超过 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.

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

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

    公式中的值 4 是数据行的行标题开销。The value 4 in the formula is the row header overhead of the data row.

  6. 下一步,计算每页的行数(每页有 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.

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

    Num_Pages = Num_Rows / Rows_Per_Page Num_Pages = Num_Rows / Rows_Per_Page

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

  8. 计算在堆中存储数据所需的空间量(每页的总字节为 8192):Calculate the amount of space that is required to store the data in the heap (8192 total bytes per page):

    堆大小(字节)= 8192 x Num_PagesHeap size (bytes) = 8192 x Num_Pages

此计算不考虑以下因素: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)textntextxmlimage 值所用的空间量的算法非常复杂。The algorithm to determine exactly how much space will be used to store the LOB data types varchar(max), varbinary(max), nvarchar(max), text, ntextxml, and image values is complex. 只添加所期望的 LOB 值的平均大小就足够了,然后将其添加至总的堆大小中。It is sufficient to just add the average size of the LOB values that are expected and add that to the total heap size.

  • 压缩Compression

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

  • 稀疏列Sparse columns

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

另请参阅See Also

堆(没有聚集索引的表) Heaps (Tables without Clustered Indexes)
描述的聚集索引和非聚集索引 Clustered and Nonclustered Indexes Described
创建聚集索引 Create Clustered Indexes
创建非聚集索引 Create Nonclustered Indexes
估计表的大小 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 Database