The following example calculates the size of a nonclustered index on the 9,000,000-row table used in "Calculating the Size of a 9,000,000-Row Table with a Clustered Index," earlier in this article. There are two keys, one fixed- and one variable-length. The table contains:
The five steps previously discussed were used to ascertain the index size.
Calculate the index row size.
9 Overhead
+ 4 Sum of length of fixed-length keys
+ 20 Sum of length of variable-length keys
+ 2 Number of variable-length keys + 1
———---
35 Subtotal
+ 1 (Subtotal / 256) + 1
———---
36 Size of Leaf Index Row
Calculate the number of leaf pages in the index.
2,016/36 = 56 nonclustered leaf rows per page
9,000,000/56 = 160,715 leaf pages
Calculate the size of the nonleaf rows.
36 + 4 = 40 size of nonleaf index row
Calculate the number of nonleaf pages.
(2,016/40) — 2 = 48 nonleaf index rows per page
160,715/48 = 3,348 index pages, level 1
3,348/48 = 69 index pages, level 2
69/48 = 2 index pages, level 3
2/48 = 1 index page, level 4
Calculate the total number of nonleaf index pages.
Totals | Pages | Rows |
Level 4 (root) | 1 | 2 |
Level 3 | 2 | 69 |
Level 2 | 69 | 3,348 |
Level 1 | 3,348 | 160,715 |
Level 0 (leaf) | 160,715 | 9,000,000 |
——— | ||
Total number of 2K pages | 164,135 | |