Estimate the size of the table with the aggregation index
The following steps can be used to estimate the space required to store data on a table with aggregation indexes and any additional non-aggregated indexes.
Calculate the space used to store data.
Calculate the space used in the storage aggregation index.
Calculate the space used for each additional non-aggregated index.
Summary calculate the resulting value.
For each calculation, you must specify the number of rows that will appear in the table. The number of rows in the table has a direct impact on the size of the table:
Number of rows in the table = Num_Rows
Calculate space used by stored data
For more information on how to calculate the space used by the stored data, see the size of the estimated table.
Make a note of the value obtained:
Space used by storing data = DATA_SPACE_USED
Calculate the space used by the storage aggregation index
The following steps can be used to estimate the space required to store the aggregation index.
The aggregation index definition may include a fixed length and a variable length column. In order to estimate the size of the aggregated index, you need to specify the space occupied by each of the two sets of columns in the cable.
Number of columns in the index key = Num_ckey_cols
All bytes in all fixed length key columns = fixed_ckey_size
Variable length column number in the index key = Num_variable_ckey_cols
Maximum value of all variable length keys = max_var_ckey_size
If there is a fixed length column in the aggregated index, a part of the cable will be retained for the space map. Calculate the size:
Index vacant map (Cindex_null_bitmap) = 2 ((Num_ckey_cols 7) / 8)
Use only the integer portions in the above expressions to remove the rest.
If there is a variable length column in the index, determine the space required to use these columns in the storage profile:
Variable length column total size (variable_ckey_size) = 2 (Num_Variable_ckey_cols x 2) max_var_ckey_size
If there is no variable length column, set the variable_ckey_size to 0.
This formula assumes that all variable length key columns are full of 100%. If the proportion of the storage space occupied by the expected variable length key column is low, the result can be adjusted in accordance with this ratio to draw a more accurate estimate of the entire index size.
Calculate the cable size:
Index Ring Total Size (CINDEX_ROW_SIZE) = Fixed_ckey_size variable_ckey_size cindex_null_bitmap 1 8
Next, calculate the number of rows per page (8096 available bytes per page):
Number of indexes per page (CINDEX_ROWS_PER_PAGE) = (8096) / (CINDEX_ROW_SIZE 2)
Since the index line cannot be cross-page, the index row of each page should go down to the closest integer.
Next, calculate the number of pages required for all of the indexes of each level of the store index.
Page (0th) (NUM_PAGES_CLEVEL_0) = (DATA_SPACE_USED / 8192) / CINDEX_ROWS_PER_PAGE
Page (1st) (NUM_PAGES_CLEVEL_1) = NUM_PAGES_CLEVEL_0 / CINDEX_ROWS_PER_PAGE
Repeat the second calculation, divide the number of pages calculated from the previous N-stage divided by CINDEX_ROWS_PER_PAGE until the specified Num_Pages_Clevel_n) page number is equal to 1 (index root page). For example, to calculate the number of pages required for the second index level:
Page (number 2) (NUM_PAGES_CLEVEL_2) = NUM_PAGES_CLEVEL_1 / CINDEX_ROWS_PER_PAGE For each level, the expected page should be rounded up to the closest integer.
Summary Storage Each of the index levels:
Total page (NUM_CINDEX_PAGES) = NUM_PAGES_CLEVEL_0 NUM_PAGES_CLEVEL_1 NUM_PAGES_CLVEL_2 ... NUM_PAGES_CLEVEL_N
Calculate the size of the aggregated index (there are 8192 bytes per page):
Gathering Index Size (bytes) = 8192 x Num_cindex_pages
Calculate the space used for each additional non-aggregation index
The following steps can be used to estimate the amount of space required to store each additional non-gather index.
Non-aggregated index definitions may include fixed length and variable length columns. In order to estimate the size of the non-concentrated index, the space occupied by each of these two sets of columns in the cable is required.
Number of columns in the index key = Num_Key_COLS
All bytes in all fixed length key columns = fixed_key_size
Variable length column number of index keys = Num_variable_key_cols
Maximum value of all variable length keys = max_var_key_size
If there is a fixed length column in the index, a part of the cable will be retained for the space map. Calculate the size:
Index vacant map (index_null_bitmap) = 2 ((Num_Key_COLS 7) / 8)
Use only the integer portions in the above expressions to remove the rest.
If there is a variable length column in the index, determine the space required to use these columns in the storage profile:
Variable length column of total size (variable_key_size) = 2 (Num_Variable_Key_COLS X 2) max_var_key_size
If there is no variable length column, set the variable_key_size to 0.
This formula assumes that all variable length key columns are full of 100%. If the proportion of the storage space occupied by the expected variable length key column is low, the result can be adjusted in accordance with this ratio to draw a more accurate estimate of the entire index size.
Calculate non-leaf-level cables:
Non-leaf-level cable Total size (NL_INDEX_ROW_SIZE) = fixed_key_size variable_key_size index_null_bitmap 1 8
Calculate the number of non-leaf-level cable per page:
Number of non-leaf-level cables per page (NL_INDEX_ROWS_PER_PER_PAGE) = (8096) / (nl_index_row_size 2)
Since the index line cannot be cross-page, the index row of each page should go down to the closest integer.
Calculate the leaf-level cable size:
Leaf-level cable Total size (index_row_size) = CINDEX_ROW_SIZE FIXED_KEY_SIZE VARIABLE_KEY_SIZE INDEX_NULL_BITMAP 1
The last value 1 indicates the architecture of the index. CINDEX_ROW_SIZE is the total size of the index line of the collected index key.
Calculate the number of leaf-level cables per page:
Leaf-level cable extortion per page (index_rows_per_page) = (8096) / (index_row_size 2)
Since the index line cannot be cross-page, the index row of each page should go down to the closest integer.
The number of available indexes reserved per page is calculated based on the filling factor specified for the non-aggregated index. For more information, see the Fill Factor.
The available index line per page (free_index_rows_per_page) = 8096 x (100 - Fill_Factor) / 100) / index_row_size calculation is an integer value, not percentage.
Since the index line cannot be cross-page, the index row of each page should go down to the closest integer.
Calculate the number of pages required for all indexes of each level of the store index:
Page (0th) (NUM_PAGES_LEVEL_0) = NUM_ROWS / (INDEX_ROWS_PER_PAGE - Free_INDEX_ROWS_PER_PAGE)
Page (1 level) (NUM_PAGES_LEVEL_1) = NUM_PAGES_LEVEL_0 / NL_INDEX_ROWS_PER_PAGE
Repeat the second calculation, divide the number of pages calculated from the previous N-stage divided by NL_INDEX_ROWS_PER_PAGE until the specified Num_pages_Level_n) page number is equal to 1 (root page).
For example, to calculate the number of pages required for the second and third index levels:
Number of data pages (number 2) (NUM_PAGES_LEVEL_2) = NUM_PAGES_LEVEL_1 / NL_INDEX_ROWS_PER_PAGE
Number of data pages (number 3) (NUM_PAGES_LEVEL_3) = NUM_PAGES_LEVEL_2 / NL_INDEX_ROWS_PER_PAGE
For each level, the expected pages should be rounded up to the closest integer.
Summary Storage Each of the index levels:
Total page (NUM_INDEX_PAGES) = NUM_PAGES_LEVEL_0 NUM_PAGES_LEVEL_1 NUM_PAGES_LEVEL_2 ... NUM_PAGES_LEVEL_N
Calculate the size of the non-aggregated index:
Non-aggregated index size (bytes) = 8192 x Num_index_pages
Calculate the size of the table
Calculate the size of the table:
Total size (bytes) = DATA_SPACE_USED Clustered Index Size NonClustered Index Size ... N
