Estimate table size (3)-estimate the size of a table without clustered Indexes

Estimate the size of a table without clustered Indexes

The following steps can be used to estimate the space required to store data on tables without clustered indexes and any additional non-clustered indexes.

1. The space used to compute the stored data.

2. Calculate the space used to store each additional non-clustered index.

3. Summarize the calculated values.

For each calculation, you must specify the number of rows that will appear in the table. The number of rows in the table will have a direct impact on the table size:

Number of rows in the table = num_rows

Space used for data storage

To calculate the space used to store data, see estimate the table size.

Write down the calculated value:

Space used to store data = data_space_used

Computing space used to store each additional non-clustered Index

The following steps can be used to estimate the size of a single non-clustered index on a table without clustered indexes.

1. If the index definition contains fixed-length and variable-length columns, calculate the space occupied by each of the two groups of columns in the index row. The column size depends on the data type and length. For more information, see data types.

Number of columns in the index key = num_key_cols

Total Bytes in all fixed-length key columns = fixed_key_size

Variable Length columns in the index key = num_variable_key_cols

Maximum value of all variable-length key columns = max_var_key_size

2. If the index contains a fixed-length column, part of the index row is retained for the vacant space graph. Calculation size:

Index vacant map (index_null_bitmap) = 2 + (num_key_cols + 7)/8)

Use only the integer part in the above expression, and remove the remaining part.

3. If the index contains variable-length columns, determine the space required to store these columns in the index row:

Total Variable Length Column size (variable_key_size) = 2 + (num_variable_key_cols X 2) + max_var_key_size

If no Variable Length Column exists, set variable_key_size to 0.

This formula assumes that all variable-length key columns are fully filled. If the expected ratio of storage space occupied by a variable-length key column is low, you can adjust the ratio to get a more accurate estimate of the entire index size.

4. Calculate the index row size:

Total index row size (index_row_size) = fixed_key_size + variable_key_size + index_null_bitmap + 1 + 8

5. Next, calculate the number of indexes per page (8096 available bytes per page ):

Number of indexes per page (index_rows_per_page) = (8096)/(index_row_size + 2)

Because index rows cannot span pages, the number of index rows per page should be rounded down to the nearest integer.

6. The number of rows of available indexes retained on each leaf-level page is calculated based on the fill factor specified for the non-clustered index. For more information, see fill factor.

Number of available index lines per leaf page (free_index_rows_per_page) = 8096X(100-fill_factor)/100 )/
Index_row_size

The fill factor used in the calculation is an integer, not a percentage.

Because index rows cannot span pages, the number of index rows per page should be rounded down to the nearest integer.

7. Next, calculate the number of pages required for all index rows at each level of the storage index:

Page number (level 1) (num_pages_level_0) = num_rows/(index_rows_per_page-free_index_rows_per_page)

Page number (level 1) (num_pages_level_1) = num_pages_level_0/index_rows_per_page

Repeat the second calculation.NThe number of pages calculated in the level is divided by index_rows_per_page until the specifiedN(Num_pages_level _N) The number of pages is equal to 1 (root page ). For example, to calculate the number of pages required for the second index:

Page number (level 1) (num_pages_level_2) = num_pages_level_1/index_rows_per_page

For each level, the expected page number should be rounded up to the nearest integer.

Summarize the number of pages required for each index level:

Total number of pages (num_index_pages) = num_pages_level_0 + num_pages_level_1 + num_pages_level_2 +... + num_pages_level _N

8. Calculate the size of the clustered index (each page contains 8192 bytes ):

Non-clustered index size (in bytes) = 8192 x num_index_pages

Calculate the table size

Calculate the table size:

Total table size (in bytes) = data_space_used + nonclustered index size +...N