Estimating the Size of a Table with a Clustered Index
The following steps can be used to estimate the amount of space required to store the data and any additional nonclustered indexes on a table that has a clustered index:
- Calculate the space used to store data.
- Calculate the space used to store the clustered index.
- Calculate the space used to store each additional nonclustered index.
- Sum the values calculated.
For each calculation, specify the number of rows that will be present in the table. The number of rows in your table will have a direct effect on the size of your table:
Number of rows in the table = Num_Rows
Calculate the Space Used to Store Data
For more information about how to calculate the space used to store data, see Estimating the Size of a Table.
Note the value calculated:
Space used to store data = Data_Space_Used
Calculate the Space Used to Store the Clustered Index
The following steps can be used to estimate the amount of space required to store the clustered index:
- A clustered index definition can include fixed-length and variable-length columns. To estimate the size of the clustered index, you must specify the space each of these groups of columns occupies within the index row:
Number of columns in index key = Num_CKey_Cols
Sum of bytes in all fixed-length key columns = Fixed_CKey_Size
Number of variable-length columns in index key = Num_Variable_CKey_Cols
Maximum size of all variable-length key columns = Max_Var_CKey_Size
- If there are fixed-length columns in the clustered index, a portion of the index row is reserved for the null bitmap. Calculate its size:
Index Null Bitmap (CIndex_Null_Bitmap) = 2 + (( Num_CKey_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.
- If there are variable-length columns in the index, determine how much space is used to store the columns within the index row:
Total size of variable length columns (Variable_CKey_Size) = 2 + (Num_Variable_CKey_Cols x 2) + Max_Var_CKey_Size
If there are no variable-length columns, set Variable_CKey_Size to 0.
This formula assumes that all variable-length key columns are 100 percent full. If you anticipate that a lower percentage of the variable-length key column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall index size.
- Calculate the index row size:
Total index row size (CIndex_Row_Size) = Fixed_CKey_Size + Variable_CKey_Size + CIndex_Null_Bitmap + 1 + 8
- Calculate the number of index rows per page (8096 free bytes per page):
Number of index rows per page (CIndex_Rows_Per_Page) = ( 8096 ) / (CIndex_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.
- Calculate the number of pages required to store all the index rows at each level of the index.
Number of pages (level 0) (Num_Pages_CLevel_0) = (Data_Space_Used / 8192) / CIndex_Rows_Per_Page
Number of pages (level 1) (Num_Pages_CLevel_1) = Num_Pages_CLevel_0 / CIndex_Rows_Per_Page
Repeat the second calculation, dividing the number of pages calculated from the previous level n by CIndex_Rows_Per_Page until the number of pages for a given level n (Num_Pages_CLevel_n) equals one (index root page). For example, to calculate the number of pages required for the second index level:
Number of pages (level 2) (Num_Pages_CLevel_2) = Num_Pages_CLevel_1 / CIndex_Rows_Per_Page
For each level, the number of pages estimated should be rounded up to the nearest whole page.
Sum the number of pages required to store each level of the index:
Total number of pages (Num_CIndex_Pages) = Num_Pages_CLevel_0 + Num_Pages_CLevel_1 +
Num_Pages_CLevel_2 + ... + Num_Pages_CLevel_n - Calculate the size of the clustered index (8192 total bytes per page):
Clustered index size (bytes) = 8192 x Num_CIndex_Pages
Calculate the Space Used to Store Each Additional Nonclustered Index
The following steps can be used to estimate the amount of space required to store each additional nonclustered index:
- A nonclustered index definition can include fixed-length and variable-length columns. To estimate the size of the nonclustered index, you must calculate the space each of these groups of columns occupies within the index row:
Number of columns in index key = Num_Key_Cols
Sum of bytes in all fixed-length key columns = Fixed_Key_Size
Number of variable-length columns in index key = Num_Variable_Key_Cols
Maximum size of all variable-length key columns = Max_Var_Key_Size
- If there are fixed-length columns in the index, a portion of the index row is reserved for the null bitmap. Calculate its size:
Index Null Bitmap (Index_Null_Bitmap) = 2 + (( Num_Key_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.
- If there are variable-length columns in the index, determine how much space is used to store the columns within the index row:
Total size of variable length columns (Variable_Key_Size) = 2 + (Num_Variable_Key_Cols x 2) + Max_Var_Key_Size
If there are no variable-length columns, set Variable_Key_Size to 0.
This formula assumes that all variable-length key columns are 100 percent full. If you anticipate that a lower percentage of the variable-length key column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall index size.
- Calculate the nonleaf index row size:
Total nonleaf index row size (NL_Index_Row_Size) = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 + 8
- Calculate the number of nonleaf index rows per page:
Number of nonleaf index rows per page (NL_Index_Rows_Per_Page) =
( 8096 ) / (NL_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.
- Calculate the leaf index row size:
Total leaf index row size (Index_Row_Size) = CIndex_Row_Size + Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1
The final value of 1 represents the index row header. CIndex_Row_Size is the total index row size for the clustered index key.
- Calculate the number of leaf level index rows per page:
Number of leaf level index rows per page (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.
- Calculate the number of reserved free index rows per page based on the fill factor specified for the nonclustered index. For more information, see Fill Factor.
Number of free index rows per page (Free_Index_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / Index_Row_Size
The fill factor used in the calculation is an integer value rather than a percentage.
Because index rows do not span pages, the number of index rows per page should be rounded down to the nearest whole row.
- Calculate the number of pages required to store all the index rows at each level of the index:
Number of pages (level 0) (Num_Pages_Level_0) = Num_Rows / (Index_Rows_Per_Page - Free_Index_Rows_Per_Page)
Number of pages (level 1) (Num_Pages_Level_1) = Num_Pages_Level_0 / NL_Index_Rows_Per_Page
Repeat the second calculation, dividing the number of pages calculated from the previous level n by NL_Index_Rows_Per_Page until the number of pages for a given level n (Num_Pages_Level_n) equals one (root page).
For example, to calculate the number of pages required for the second and third index levels:
Number of data pages (level 2) (Num_Pages_Level_2) = Num_Pages_Level_1 / NL_Index_Rows_Per_Page
Number of data pages (level 3) (Num_Pages_Level_3) = Num_Pages_Level_2 / NL_Index_Rows_Per_Page
For each level, the number of pages estimated should be rounded up to the nearest whole page.
Sum the number of pages required to store each level of the index:
Total number of pages (Num_Index_Pages) = Num_Pages_Level_0 + Num_Pages_Level_1 +Num_Pages_Level_2 + ... + Num_Pages_Level_n
- Calculate the size of the nonclustered index:
Nonclustered index size (bytes) = 8192 x Num_Index_Pages
Calculate the Size of the Table
Calculate the size of the table:
- Total table size (bytes) = Data_Space_Used + Clustered index size + Nonclustered index size + ...n