# Estimating the Size of a Heap

You can use the following steps to estimate the amount of space that is required to store data in a heap:

Specify the number of rows that will be present in the table:

= number of rows in the table**Num_Rows**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. For more information, see Data Types (Database Engine).

= total number of columns (fixed-length and variable-length)**Num_Cols**

= total byte size of all fixed-length columns**Fixed_Data_Size**

= number of variable-length columns**Num_Variable_Cols**

= maximum byte size of all variable-length columns**Max_Var_Size**Part of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:

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

Only the integer part of this expression should be used. Discard any remainder.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:

This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a smaller percentage of the variable-length column storage space will be used, you can adjust the**Variable_Data_Size*** = 2 + (x 2) + ***Num_Variable_Cols****Max_Var_Size**value by that percentage to yield a more accurate estimate of the overall table size.**Max_Var_Size**Note

SQL Server 2005 introduces the ability to combine

**varchar**,**nvarchar**,**varbinary**, or**sql_variant**columns that cause the total defined table width to exceed 8,060 bytes. 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**columns. However, their combined widths may exceed the 8,060 byte limit in a table. For more information, see Row-Overflow Data Exceeding 8 KB.If there are no variable-length columns, set

to 0.**Variable_Data_Size**Calculate the total row size:

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

The value 4 in the formula is the row header overhead of the data row.Calculate the number of rows per page (8096 free bytes per page):

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

Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. The value 2 in the formula is for the row's entry in the slot array of the page.Calculate the number of pages required to store all the rows:

The number of pages estimated should be rounded up to the nearest whole page.**Num_Pages*** =/ ***Num_Rows****Rows_Per_Page**Calculate the amount of space that is required to store the data in the heap (8192 total bytes per page):

Heap 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

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. - Large object (LOB) values

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. It is sufficient to just add the average size of the LOB values that are expected and add that to the total heap size.

## See Also

#### Concepts

Clustered Index Design Guidelines

Creating Indexes (Database Engine)

Nonclustered Index Design Guidelines

Estimating the Size of a Table

Estimating the Size of a Clustered Index

Estimating the Size of a Nonclustered Index

#### Other Resources

Estimating the Size of a Database