Solved

SQL Sizing

Posted on 2004-09-16
10
279 Views
Last Modified: 2008-01-16
CREATE TABLE ADDEBTP (
  ADP3CG  CHAR     (10)     NOT NULL,
  ADEZSW  CHAR     (1)      NOT NULL,
  ADDGNE  NUMERIC  (2)      NOT NULL,
  ADDHNE  NUMERIC  (2)      NOT NULL,
  ADMUTA  CHAR     (20)     NOT NULL,
  ADMVTA  VARCHAR  (216)            ,
  ADMWTA  VARCHAR  (32)             ,
  ADE3SW  CHAR     (1)      NOT NULL,
  ADE4SW  CHAR     (1)      NOT NULL ) ON [PRIMARY]  



CREATE INDEX ADDEBTV2_IX ON ADDEBTP (
  ADP3CG,
  ADEZSW,
  ADDHNE) ON [Index]

could you please help me calculate the table size for expected rows of 3000000
please break it down step by step before arriving to the answer with the fomular use for each step
put into configuration that the fill factor is 100%

Thanks
0
Comment
Question by:gbemisola04082004
  • 5
  • 2
10 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 12079683


1.      Specify the number of rows present in the table:
Number of rows in the table =3000000 -- Num_Rows
2.      If there are fixed-length and variable-length columns in the table definition, 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.
Number of columns = 9 -- Num_Cols
Sum of bytes in all fixed-length columns =43 -- Fixed_Data_Size
Number of variable-length columns =2 -- Num_Variable_Cols
Maximum size of all variable-length columns = 216 -- Max_Var_Size
3.      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 )
Only the integer portion of the above expression should be used; discard any remainder.
4.      If there are variable-length columns in the table, determine how much space is used to store the columns within the row:
Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
(Variable_Data_Size) = 2+2x2 +216=222
If there are no variable-length columns, set Variable_Data_Size to 0.
This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall table size.
5.      Calculate the row size:
Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4
                     (Row_Size) = 43                      +  222                      + 0                 +4=269
The final value of 4 represents the data row header.
6.      Calculate the number of rows per page (8096 free bytes per page):
Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)
(Rows_Per_Page) = ( 8096 ) /(269 +2)=30
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.
7.      If a clustered index is to be created on the table, calculate the number of reserved free rows per page, based on the fill factor specified. For more information, see Fill Factor. If no clustered index is to be created, specify Fill_Factor as 100.
Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)
(Free_Rows_Per_Page)=8096x ((100-100)/100)/(30+2)=3
The fill factor used in the calculation is an integer value rather than a percentage.
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.
8.      Calculate the number of pages required to store all the rows:
Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)
                 (Num_Pages) =3000000/(30-3)= 111111
 
The number of pages estimated should be rounded up to the nearest whole page.
9.      Calculate the amount of space required to store the data in a table (8192 total bytes per page):
Table size (bytes) = 8192 x Num_Pages
Bytes =8192x111111=910222222Bytes =88889 kB=87MB
                                                                                                                                                                              see also:
step-by-step Estimating the Size of a Table
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp 
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 12079698
my post above can have errors please check\review\recalculate to be sure for right result
0
 

Author Comment

by:gbemisola04082004
ID: 12096483
Eugene , thank for the result .I need your clrification on  (Number 7  the free row per page )the row _size on the below formula should be (269+2)

but in your calculation you make it (30+2)

could you please clarify this bit for me please

Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)
(Free_Rows_Per_Page)=8096x ((100-100)/100)/(30+2)=3
 
i think  the formular for free_rows_per_page, the  row _size should be  (269 +2 ) not (30 +2) according to the calculation below for row_size

 Calculate the row size:
Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4
                     (Row_Size) = 43                      +  222                      + 0                 +4=269


(Free_Rows_Per_Page)=8096x ((100-100)/100)/(30+2)=3


I will appriciate if you can let me know
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 42

Expert Comment

by:EugeneZ
ID: 12097747
I'r right
looks like typo from me
:'-(

30 is rows_per_page but Row-Size

Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)
(Rows_Per_Page) = ( 8096 ) /(269 +2)=30
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 12097785
num_pages=100000
Table size (bytes) = 8192 x Num_Pages
Bytes =8192x100000=819200000Bytes =800000 kB=781.25 MB ?
Anyway verify

                                                                                     
0
 

Author Comment

by:gbemisola04082004
ID: 12100793
how do you calculat e your num_pages to be 100000

do you round it up or what

0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 12117128
yes rounded see #8

8.     Calculate the number of pages required to store all the rows:
Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)
       
 
The number of pages estimated should be rounded up to the nearest whole page.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 36
sql 2014,  lock limit 5 32
Sql server insert 13 31
return table in table valued function  using dynamic sql, SQlServer 2008r2 5 18
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question