grg-it
asked on
Understanding formula for estimating size of table
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
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
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanx for all the responses I see in here so far..
Little bit of information I found on page header and what exactly is stored in this 96 byte page header?
Page Header which is always 96-byte and cannot be changed by DBA or developer contains
information such as the page number, pointers to the previous and next pages, and the
object ID of the object to which the page belongs.
And about 3 x 32 = 96 bytes
For efficient reading, the header has three cache lines of 32 bytes each. The first 32-byte line includes the most frequently needed information about a page, such as the page type and some status information. The second 32-byte line includes less frequently needed information. Not sure about third 32-byte line.. lol
Little bit of information I found on page header and what exactly is stored in this 96 byte page header?
Page Header which is always 96-byte and cannot be changed by DBA or developer contains
information such as the page number, pointers to the previous and next pages, and the
object ID of the object to which the page belongs.
And about 3 x 32 = 96 bytes
For efficient reading, the header has three cache lines of 32 bytes each. The first 32-byte line includes the most frequently needed information about a page, such as the page type and some status information. The second 32-byte line includes less frequently needed information. Not sure about third 32-byte line.. lol
ASKER
Angell one more question on your last response:
You said:
"1)
yes, that's it. once you have at least 1 "Allow null" column in your table, you have that overhead of 2 bytes."
So will this statement hold true? If you have no 'Allow null" column(not even one) in your table you will not have overhead of 2 bytes."
If so then,
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
SHOUDL BE
Null Bitmap(Null_Bitmap) = 0
Reason I came up with this is because of what I found on BOL and somewhere I think it contradicts to
what you say for nullable bitmap. This is what BOL has to say:
"If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )"
Now if you notice over here it just assumes that a field should be fixed length. Nothing to do with whether column is defined with allow null.
More here, point 3:
http://msdn.microsoft.com/en-us/library/aa933068(SQL.80).aspx
Thanx
Trushar
You said:
"1)
yes, that's it. once you have at least 1 "Allow null" column in your table, you have that overhead of 2 bytes."
So will this statement hold true? If you have no 'Allow null" column(not even one) in your table you will not have overhead of 2 bytes."
If so then,
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
SHOUDL BE
Null Bitmap(Null_Bitmap) = 0
Reason I came up with this is because of what I found on BOL and somewhere I think it contradicts to
what you say for nullable bitmap. This is what BOL has to say:
"If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )"
Now if you notice over here it just assumes that a field should be fixed length. Nothing to do with whether column is defined with allow null.
More here, point 3:
http://msdn.microsoft.com/en-us/library/aa933068(SQL.80).aspx
Thanx
Trushar
indeed, the fixed-length columns could be stored as NULL, depending on the ANSI_PADDING setting, even if it's not a NULL that could be stored. a special side-effect-stuff...
As I understand it, SQL now *always* uses one bit per column to account for nullability, **whether the column (currently) allows null or not**.
Makes sense, really, because otherwise, if you alter a column to allow null, SQL might have to rebuild the whole table just to add one bit -- not good!
Makes sense, really, because otherwise, if you alter a column to allow null, SQL might have to rebuild the whole table just to add one bit -- not good!
ASKER
ScottPletcher said:
[As I understand it, SQL now *always* uses one bit per column to account for nullability, **whether the column (currently) allows null or not**.
Makes sense, really, because otherwise, if you alter a column to allow null, SQL might have to rebuild the whole table just to add one bit -- not good!]
Does that mean, you will always have that overhead of 2 bytes when calculating null bitmap even
if no column is set to allow null in a table?
In short, do we always have to consider this formula for calculating table size or there could be an exception?
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
[As I understand it, SQL now *always* uses one bit per column to account for nullability, **whether the column (currently) allows null or not**.
Makes sense, really, because otherwise, if you alter a column to allow null, SQL might have to rebuild the whole table just to add one bit -- not good!]
Does that mean, you will always have that overhead of 2 bytes when calculating null bitmap even
if no column is set to allow null in a table?
In short, do we always have to consider this formula for calculating table size or there could be an exception?
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
as I was not aware that this has changed, I cannot confirm/deny that.
I would say that it's logical then...
I would say that it's logical then...
ASKER
Thanx for your response. And I'm sorry but I'm still trying to understand.
1)
What you said about "2+ is to specify the fact that there are nullable columns.." I did not quite understand this.. Can you tell me what columns are treated nullable? Fixed, variable? Or any datatype that I define as 'Allow null?' is treated as nullable?
Also you said 'it needs 1 bit for every nullable column".. Why does it need 1 bit and for what?
And you also stated that +7/8 is taking care of that.. Neither did I get this part..!
2)
sql server requires 2 bytes for each VARchar column to manage the VARiable length stuff.
What is variable length stuff?
4)
What is 3 x 32 and why 3 x 32? What does 3 stand for and what does 32 stand for?
I'm just trying to understand this more in depth.
Thanx
Trushar