Calculating the Size of a 9,000,000 Row Table with a Clustered Index

Calculating the Size of a 9,000,000-Row Table with a Clustered Index

The following example calculates the size (in 2K pages) of the data and clustered index for a table containing:

9,000,000 rows
Sum of bytes in fixed-length columns = 100
Sum of bytes in two variable-length columns = 50
Clustered key, fixed-length = 4 bytes

Follow these steps:

Calculate the data row size:



4	(Overhead)
+ 100	Sum of bytes in all fixed-length columns
+ 50	Sum of bytes in all variable-length columns
___
154	Subtotal

154	Subtotal
+ 1	(Subtotal / 256) 1 (Overhead)
+ 3	Number of variable-length columns 1
+ 2	(Overhead)
___
160	Data row size

Calculate the number of data pages:
2016/160 = 12 Data rows per page
9,000,000/12 = 750,000 Data pages
Calculate the size of clustered index rows:



5	Overhead
+ 4	Sum of bytes in the fixed-length index keys
___
9	Clustered index row size

Calculate the number of clustered index pages:
(2016 / 9) - 2 = 222 Clustered index rows per page
750,000 / 222 = 3378 Index pages (Level 0)
3378 / 222 = 15 Index pages (Level 1)
15 / 222 = 1 Index page (Level 2)
Calculate the total number of pages:



Totals:	Pages	Rows
Level 2 (root)	1	15
Level 1	15	3,378
Level 0	3,378	750,000
Data	750,000	9,000,000
	¾----
Total number of 2K pages	753,394