record size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

record size

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-08-18 : 05:48:23

I have a table with 3 UNIQUEIDENTIFIER columns and one TINYINT column.
How do I calculate how much space each record takes on the harddrive?
I want to be able to predict how much space the table would take up with x amount of rows.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-18 : 06:15:37

UNIQUEIDENTIFIER = 16 bytes
Tinyint = 1 byte
+ 96 bytes for row properties

Go with the flow & have fun! Else fight the flow

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-18 : 06:54:17

Look up "Estimating the Size of a Table" in BOL. It is actually a pretty complicated process. In addition to datatype, you also need to take into account nullablity, indexes, fill factor, rows per page, etc.

Jay White

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-18 : 10:26:46

quote:
Originally posted by spirit1

UNIQUEIDENTIFIER = 16 bytes
Tinyint = 1 byte
+ 96 bytes for row properties

Go with the flow & have fun! Else fight the flow

Where'd you get 96 bytes for row properties from? That would quickly put us out of business...

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-18 : 10:29:07

I just rewrote those BOL sections to make them a little more clear. Here's the text from the SQL Server 2005 BOL:
(See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/81fd5ec9-ce0f-4c2c-8ba0-6c483cea6c75.htm if you have June CTP)

You can use the following steps to estimate the amount of space that is required to store data in a heap:

Specify the number of rows that will be present in the table:

Num_Rows = number of rows in the table

Specify the number of fixed-length and variable-length columns and calculate the space that is required for their storage:

Calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification. For more information, see Data Types (Database Engine).

Num_Cols = total number of columns (fixed-length and variable-length)

Fixed_Data_Size = total byte size of all fixed-length columns

Num_Variable_Cols = number of variable-length columns

Max_Var_Size = maximum byte size of all variable-length columns

Part of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:

Null_Bitmap = 2 + ((Num_Cols + 7) / 8)

Only the integer part of this expression should be used. Discard any remainder.

Calculate the variable-length data size:

If there are variable-length columns in the table, determine how much space is used to store the columns within the row:

Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a smaller percentage of the variable-length column storage space will be used, you can adjust the Max_Var_Size value by that percentage to yield a more accurate estimate of the overall table size.

Note SQL Server 2005 introduces the ability to combine varchar, nvarchar, varbinary, or sql_variant columns that cause the total defined table width to exceed 8,060 bytes. The length of each one of these columns must still fall within the limit of 8,000 bytes for a varchar, varbinary, or sql_variant column, and 4,000 bytes for nvarchar columns. However, their combined widths may exceed the 8,060 byte limit in a table. For more information, see Row-Overflow Data Exceeding 8 KB.

If there are no variable-length columns, set Variable_Data_Size to 0.

Calculate the total row size:

Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

The value 4 in the formula is the row header overhead of the data row.

Calculate the number of rows per page (8096 free bytes per page):

Rows_Per_Page = 8096 / (Row_Size + 2)

Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. The value 2 in the formula is for the row's entry in the slot array of the page.

Calculate the number of pages required to store all the rows:

Num_Pages = Num_Rows / Rows_Per_Page

The number of pages estimated should be rounded up to the nearest whole page.

Calculate the amount of space that is required to store the data in the heap (8192 total bytes per page):

Heap size (bytes) = 8192 x Num_Pages

This calculation does not consider the following:

Partitioning

The space overhead from partitioning is minimal, but complex to calculate. It is not important to include.

Allocation pages

There is at least one IAM page used to track the pages allocated to a heap, but the space overhead is minimal and there is no algorithm to deterministically calculate exactly how many IAM pages will be used.

Large object (LOB) values

The algorithm to determine exactly how much space will be used to store the LOB data types varchar(max), varbinary(max), nvarchar(max), text, ntext xml, and image values is complex. It is sufficient to just add the average size of the LOB values that are expected and add that to the total heap size.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Rageru
Starting Member

1 Post

Posted - 2007-02-16 : 13:19:07

What is Max_Var_Size? Is it the sum of the maximum bytes in all the variable columns, or is it the highest number of bytes in one of the columns?

If you have a table with these columns:

Int
Varchar(8)
Varchar(22)

Would Max_Var_Size be 22 or 30?

Best reguards
Rageru

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-02-19 : 17:07:23

As they all-knowing source of wisdom that is Paul Randal explained, the answer is 30.
Man, this was a balst from the past. Can't believe that it was a year and a half ago that I first asked that question.
So much has happened since then!

Kristen
Test

22859 Posts

Posted - 2007-02-19 : 17:14:49

"if you have June CTP"

"Can't believe that it was a year and a half ago"

Stick to that Programmer's-Calendar Paul (the one without any Years!)

Kristen

supersql
Yak Posting Veteran

99 Posts

Posted - 2007-08-16 : 13:31:25

say I have few tables with given row count as
Table1 5673
Table2 9876
Table3 98765

How can i calculate the size of all these 3 tables in MB.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-16 : 13:34:14

sp_spaceused

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

supersql
Yak Posting Veteran

99 Posts

Posted - 2007-08-16 : 13:45:29

not for the full table, i need space used for selected rows in a table.

directly i need to calcualte space used againast no of rows

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-16 : 13:51:19

something like:


select TotalBytes, TotalBytes/1024 as TotalKb, TotalBytes/1024/1024 as TotalMb
from
(
    select sum(c1 + c2 + cN) as TotalBytes
    (
        select sum(datalength(col1)) as c1, sum(datalength(col2)) as c2, sum(datalength(colN)) as cN
        from yourTable
        where yourCondition
    ) t1
) t2

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources