troubleshooting Question

Understanding formula for estimating size of table

Avatar of grg-it
grg-it asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
11 Comments1 Solution497 ViewsLast Modified:
Hi

I was going through some pre-defined formula for estimating size of table in SQL SERVER 2005.

Can someone please explain me constant/hardcoded numbers thats used in these formulaes.

For Instance,

To calculate null bitmap, its:

1)
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )

What is 2, 7 and 8 in above formala? Detailed explanation is highly appreciated.

2)
Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

What is 2 in above formula. And there is "Num_Variable_Cols x 2".. What does this 2 mean?

3)

Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)

Why are we adding additional 2 in above formula? What does that mean?

4)

To calculate fill factor:

Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)

Here we have another 2. What does this 2 mean?

Also every page is 8KB in size and every page has header thats of 96 bytes. Question is what makes up 96 bytes
and why 96 bytes?

Thank you
Trushar
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros