Estimating Conventional Index Page Size

To estimate the number of index pages

Add up the total widths of the indexed column or columns. This value is referred to as colsize. For a nonfragmented index, add 5 to colsize to obtain keysize, the actual size of a key in the index. For a fragmented index, add 9 to colsize.
Calculate the expected proportion of unique entries to the total number of rows. This value is referred to as propunique. If the index is unique or rows contain very few duplicate values, use 1 for propunique. If a significant proportion of entries are duplicates, divide the number of unique index entries by the number of rows in the table to obtain a fractional value for propunique. If the resulting value for propunique is less than .01, use .01 in the calculations that follow.
Estimate the size of a typical index entry with one of the following formulas, depending on whether the table is fragmented or not:
1. For nonfragmented tables, use the following formula:
```
entrysize = keysize * propunique + 5
```
2. For fragmented tables, use the following formula:
```
entrysize = keysize * propunique + 9
```
Estimate the number of entries per index page with the following formula:
```
pagents = trunc(pagefree/entrysize)
```
pagefree

is the page size minus the page header (24 bytes).

The trunc() function notation indicates that you should round down to the nearest integer value.
Estimate the number of leaf pages with the following formula:
```
leaves = ceiling(rows/pagents)
```
The ceiling() function notation indicates that you should round up to the nearest integer value; rows is the number of rows that you expect to be in the table.
Estimate the number of branch pages at the second level of the index with the following formula:
```
branches₀ = ceiling(leaves/pagents)
```
If the value of branches₀ is greater than 1, more levels remain in the index. To calculate the number of pages contained in the next level of the index, use the following formula:
```
branches_n+1 = ceiling(branches_n/pagents)
```
branches_n

is the number of branches for the last index level that you calculated.

branches_n+1

is the number of branches in the next level.
Repeat the calculation in step 7 for each level of the index until the value of branches_n+1 equals 1.
Add up the total number of pages for all branch levels calculated in steps 6 through 8. This sum is referred to as branchtotal.
Use the following formula to calculate the number of pages in the compact index:
```
compactpages = (leaves + branchtotal)
```
To incorporate the fill factor into your estimate for index pages, use the following formula:
```
indexpages = 100 * compactpages / FILLFACTOR
```
The default value of FILLFACTOR is 90. To decrease the size of the index and make index pages compact, specify a higher FILLFACTOR. To allow room for expansion in each index page but increase the size of the index, specify a lower FILLFACTOR.

As rows are deleted and new ones are inserted, the number of index entries in a page varies. This method for estimating index pages yields a conservative (high) estimate for most indexes. For a more precise value, build a large test index with real data and check its size with the onutil CHECK INDEX utility.