Estimate the size of the table without the collected index
The following steps can be used to estimate the space required to store data on the table that is not aggregated and any space required for any additional non-aggregated index.
Calculate the space used to store data.
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
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 for each additional non-aggregation index
The following steps can be used to estimate the size of a single non-aggregated index on the table without the aggregation index.
If the index definition contains a fixed length and variable length column, the space occupied by each of these two sets of columns in the cable is calculated. The size of the column depends on the data type and length description. For more information, see the data type.
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 the cable size:
Index Ring Total Size (Index_Row_Size) = fixed_key_size variable_key_size index_null_bitmap 1 8
Next, calculate the number of rows per page (8096 available bytes per page):
Number of indexes 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 leaf level page is calculated based on the filler specified for the non-aggregated index. For more information, see the Fill Factor.
The number of available indexes (free_index_rows_per_page) = 8096 x ((100 - fill_factor) / 100) / index_row_size
The filling factor used in the calculation is an integer value, not a percentage.
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 the store index:
Page (0th) (NUM_PAGES_LEVEL_0) = NUM_ROWS / (INDEX_ROWS_PER_PAGE - Free_INDEX_ROWS_PER_PAGE)
Number (NUM_PAGES_LEVEL_1) = NUM_PAGES_LEVEL_1) = NUM_PAGES_PAGE_0 / INDEX_ROWS_PER_PAGE Repeats the second calculation, the number of pages calculated from the previous N-stage divided by index_rows_per_page until the specified Num_pages_Level_n) page number is equal to 1 ( The root page). For example, to calculate the number of pages required for the second index level:
Page (number 2) (NUM_PAGES_LEVEL_2) = NUM_PAGES_LEVEL_1 / 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 aggregated index (8192 bytes per page):
Non-aggregated index size (bytes) = 8192 x Num_index_pages
Calculate the size of the table
Calculate the size of the table:
Table of the total size (bytes) = DATA_SPACE_USED NONCLUSTERED INDEX SIZE ... N
