Solved

how to calculate size of table

Posted on 2006-06-29
10
273 Views
Last Modified: 2010-05-19
Hi,
I do have 350 millions row of records in table [internalpart], so how could I calculate  how many KB,MB,GB are the data  in this table?

This table [internalpart] has column and data type and size  as below:

IDPart  INT 4
Individualid INT 4
Code1 VARCHAR 26
Code2 VRACHAR 30
Date1 SMALLDATETIME 4
Date1 SMALLDATETIME 4
0
Comment
Question by:motioneye
  • 6
  • 3
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
sp_spaceUsed 'tablename'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Use sp_spaceused with @updateUsage = true



Exec sp_spaceused 'internalpart',@updateusage = 'TRUE'
0
 

Author Comment

by:motioneye
Comment Utility
Oh yes,
I've forgotten about this sp, anyway thanks...aneeshattingal..
0
 

Author Comment

by:motioneye
Comment Utility
oh the real question is,
what is the size of single rows in my [internalpart] table??? how do I calculate this, what is I would like to know is size of single rows X row counts
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
U can make use of the following procedure
I got it from some websites

if exists (select * from sysobjects where id = object_id(N'[dbo].[calcspace]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[calcspace]
GO

create procedure CalcSpace
/************************************************************************/
/* Stored Procedure: CalcSpace */
/* Creation Date: 1999-04-11 */
/* Copyright: - */
/* Written by: Sharon Dooley */
/* */
/* Purpose: <purpose of the script> */
/* A procedure to estimate the disk space requirements of a table. */
/* Refer to Books OnLine topic "Estimating the size of a table..." */
/* for a detailed description */
/* */
/* Input Parameters: <list any input parameters> */
/* @table_name VARCHAR(30) Name of table to estimate */
/* @num_rows INT Number of rows in the table */
/* */
/* Output Parameters: <list any output parameters> */
/* - */
/* */
/* Return Status: <list any return codes> */
/* - */
/* */
/* Usage: <a sample usage statement> */
/* EXEC CalcSpace 'MyTable', 10000 */
/* */
/* Other info: <other info for this SP> */
/* The is a direct copy from the CalcSpace stored procedure made by*/
/* Sharon Dooley, 1999-04-11. The only change is the added */
/* documentation header and a small bug fix mentioned below. */
/* */
/* Updates: <this section is used to track changes to the script> */
/* Date Author Purpose */
/* 2000-07-04 Magnus Andersson Changed @sysstat from tinyint */
/* to int to prevent overflow */
/* scenario. Added documentation. */
/* */
/************************************************************************/

(@table_name varchar(30)=null,-- name of table to estimate
@num_rows int = 0) -- number of rows in the table
as

declare @msg varchar(120)

-- Give usage statement if @table_name is null

if @table_name = null or @num_rows = 0
begin
print 'Usage is:'
print ' calcspace table_name, no_of_rows'
print 'where table_name is the name of the table,'
print ' no_of_rows is the number of rows in the table,'
print ' '
return
end

declare @num_fixed_col int,
@fixed_data_size int,
@num_variable_col int,
@max_var_size int,
@null_bitmap int,
@variable_data_size int,
@table_id int,
@num_pages int,
@table_size_in_bytes int,
@table_size_in_meg real,
@table_size_in_kbytes real,
@sysstat int,
@row_size int,
@rows_per_page int,
@free_rows_per_page int,
@fillfactor int,
@num_fixed_ckey_cols int,
@fixed_ckey_size int,
@num_variable_ckey_cols int,
@max_var_ckey_size int,
@cindex_null_bitmap int,
@variable_ckey_size int,
@cindex_row_size int,
@cindex_rows_per_page int,
@data_space_used int,
@num_pages_clevel_0 int,
@num_pages_clevel_1 int,
@num_pages_clevel_x int,
@num_pages_clevel_y int,
@Num_CIndex_Pages int,
@clustered_index_size_in_bytes int,
@num_fixed_key_cols int,
@fixed_key_size int,
@num_variable_key_cols int,
@max_var_key_size int,
@index_null_bitmap int,
@variable_key_size int,
@nl_index_row_size int,
@nl_index_rows_per_page int,
@index_row_size int,
@index_rows_per_page int,
@free_index_rows_per_page int,
@num_pages_level_0 int,
@num_pages_level_1 int,
@num_pages_level_x int,
@num_pages_level_y int,
@num_index_pages int,
@nonclustered_index_size int,
@total_num_nonclustered_index_pages int,
@free_cindex_rows_per_page int,
@tot_pages int

-- initialize variables
select @num_fixed_col =0,
@fixed_data_size =0,
@num_variable_col =0,
@max_var_size =0,
@null_bitmap =0,
@variable_data_size =0,
@table_id =0,
@num_pages =0,
@table_size_in_bytes =0,
@table_size_in_meg =0,
@table_size_in_kbytes =0,
@sysstat =0,
@row_size =0,
@rows_per_page =0,
@num_fixed_ckey_cols =0,
@fixed_ckey_size =0,
@num_variable_ckey_cols =0,
@max_var_ckey_size =0,
@cindex_null_bitmap =0,
@variable_ckey_size =0,
@cindex_row_size =0,
@cindex_rows_per_page =0,
@data_space_used =0,
@num_pages_clevel_0 =0,
@num_pages_clevel_1 =0,
@Num_CIndex_Pages =0,
@clustered_index_size_in_bytes =0,
@num_fixed_key_cols =0,
@fixed_key_size =0,
@num_variable_key_cols =0,
@max_var_key_size =0,
@index_null_bitmap =0,
@variable_key_size =0,
@nl_index_row_size =0,
@nl_index_rows_per_page =0,
@index_row_size =0,
@index_rows_per_page =0,
@free_index_rows_per_page =0,
@num_pages_level_0 =0,
@num_pages_level_1 =0,
@num_pages_level_x =0,
@num_pages_level_y =0,
@num_index_pages =0,
@nonclustered_index_size =0,
@total_num_nonclustered_index_pages =0,
@free_cindex_rows_per_page =0,
@tot_pages =0

set nocount on

--*********************************************
-- MAKE SURE TABLE EXISTS
--*********************************************

select @sysstat = sysstat,
@table_id = id
from sysobjects where name = @table_name

if @sysstat & 7 not in (1,3)
begin
select @msg = 'I can''t find the table '+@table_name
print @msg
return
end

--*********************************************
-- ESTIMATE SIZE OF TABLE
--*********************************************

-- get total number and total size of fixed-length columns

select @num_fixed_col = count(name),
@fixed_data_size = sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=0
)

if @num_fixed_col= 0 --@fixed_data_size is null. change to 0
select @fixed_data_size=0

-- get total number and total maximum size of variable-length columns

select @num_variable_col=count(name),
@max_var_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=1
)
if @num_variable_col= 0 --@max_var_size is null. change to 0
select @max_var_size=0

-- get portion of the row used to manage column nullability

select @null_bitmap=2+((@num_fixed_col+7)/8)

-- determine space needed to store variable-length columns
-- this assumes all variable length columns will be 100% full
if @num_variable_col = 0
select @variable_data_size=0
else
select @variable_data_size = 2 + (@num_variable_col *2 )+ @max_var_size

-- get row size

select @row_size= @fixed_data_size +
@variable_data_size +
@null_bitmap + 4 -- 4 represents the data row header


-- get number of rows per page

select @rows_per_page = (8096) / (@row_size+2)

-- 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.
-- If no clustered index is to be created, specify Fill_Factor as 100.

select @fillfactor = 100 -- initialize it to the maximum
select @free_rows_per_page = 0 --initialize to no free rows/page
select @fillfactor=OrigFillFactor
from sysindexes
where id = @table_id and indid=1 -- indid of 1 means the index is clustered

if @fillfactor<>0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the data pages in this section
select @free_rows_per_page=8096 * ((100-@fillfactor)/100)/@row_size

-- get number of pages needed to store all rows

select @num_pages = ceiling(convert(dec,@num_rows) / (@rows_per_page-@free_rows_per_page))

-- get storage needed for table data

select @data_space_used=8192*@num_pages


--*********************************************
-- COMPUTE SIZE OF CLUSTERED INDEX IF ONE EXISTS
--*********************************************

-- create a temporary table to contain columns in clustered index. System table
-- sysindexkeys has a list of the column numbers contained in the index

select colid into #col_list
from sysindexkeys where id= @table_id and indid=1 -- indid=1 means clustered

if (select count(*) from #col_list) >0 -- do the following only if clustered index exsists
begin
-- get total number and total maximum size of fixed-length columns in clustered index

select @num_fixed_ckey_cols=count(name),
@fixed_ckey_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select * from #col_list)

if @num_fixed_ckey_cols= 0 --@fixed_ckey_size is null. change to 0
select @fixed_ckey_size=0

-- get total number and total maximum size of variable-length columns in clustered index

select @num_variable_ckey_cols=count(name),
@max_var_ckey_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select * from #col_list)

if @num_variable_ckey_cols= 0 --@max_var_ckey_size is null. change to 0
select @max_var_ckey_size=0

-- If there are fixed-length columns in the clustered index,
-- a portion of the index row is reserved for the null bitmap. Calculate its size:
if @num_fixed_ckey_cols <> 0
select @cindex_null_bitmap=2+((@num_fixed_ckey_cols + 7)/8)
else
select @cindex_null_bitmap=0

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

if @num_variable_ckey_cols <> 0
select @variable_ckey_size=2+(@num_variable_ckey_cols *2)+@max_var_ckey_size
else
select @variable_ckey_size=0

-- Calculate the index row size

select @cindex_row_size=@fixed_ckey_size +@variable_ckey_size+@cindex_null_bitmap+1+8

--Next, calculate the number of index rows per page (8096 free bytes per page):

select @cindex_rows_per_page=(8096)/(@cindex_row_size+2)

-- consider fillfactor
if @fillfactor=0
select @free_cindex_rows_per_page = 2
else
select @free_cindex_rows_per_page= 8096 * ((100-@fillfactor)/100)/@cindex_row_size

-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.

select @num_pages_clevel_0=ceiling(convert(decimal,(@data_space_used/8192))/(@cindex_rows_per_page-@free_cindex_rows_per_page))
select @Num_CIndex_Pages=@num_pages_clevel_0
select @num_pages_clevel_x=@num_pages_clevel_0

while @num_pages_clevel_x <> 1
begin
select @num_pages_clevel_y=ceiling(convert(decimal,@num_pages_clevel_x)/(@cindex_rows_per_page-@free_cindex_rows_per_page))
select @Num_CIndex_Pages=@Num_CIndex_Pages+@num_pages_clevel_y
select @num_pages_clevel_x=@num_pages_clevel_y
end
end

--*********************************************
-- END CLUSTERED INDEX SECTION
--*********************************************

--*********************************************
-- BEGIN NON-CLUSTERED INDEX SECTION
--*********************************************

-- create temp table with non-clustered index info

select indid, colid into #col_list2
from sysindexkeys where id= @table_id and indid<>1 -- indid=1 means clustered

if (select count(*) from #col_list2) >0 -- do the following only if non-clustered indexes exsist
begin
declare @i int -- a counter variable
select @i=1 -- initilize to 2, because 1 is id of clustered index

while @i< 249 -- max number of non-clustered indexes
begin
select @i=@i+1 -- look for the next non-clustered index
-- reinitialize all numbers
select @num_fixed_key_cols = 0,
@fixed_key_size = 0,
@num_variable_key_cols = 0,
@max_var_key_size = 0,
@index_null_bitmap = 0,
@variable_key_size = 0,
@nl_index_row_size = 0,
@nl_index_rows_per_page = 0,
@index_row_size = 0,
@index_rows_per_page = 0,
@free_index_rows_per_page = 0,
@num_pages_level_0 = 0,
@num_pages_level_x = 0,
@num_pages_level_y = 0,
@Num_Index_Pages = 0

-- get total number and total maximum size
-- of fixed-length columns in nonclustered index
select @num_fixed_key_cols=count(name),
@fixed_key_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select colid from #col_list2 where indid=@i)
if @num_fixed_key_cols= 0 --@fixed_key_size is null. change to 0
select @fixed_key_size=0

-- get total number and total maximum size of variable-length columns in index

select @num_variable_key_cols=count(name),
@max_var_key_size= sum(length)
from syscolumns
where id= @table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select colid from #col_list2 where indid=@i)
if @num_variable_key_cols= 0 --@max_var_key_size is null. change to 0
select @max_var_key_size=0

if @num_fixed_key_cols = 0 and @num_variable_key_cols = 0 --there is no index
continue
-- If there are fixed-length columns in the non-clustered index,
-- a portion of the index row is reserved for the null bitmap. Calculate its size:
if @num_fixed_key_cols <> 0
select @index_null_bitmap=2+((@num_fixed_key_cols + 7)/8)
else
select @index_null_bitmap=0

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

if @num_variable_key_cols <> 0
select @variable_key_size=2+(@num_variable_key_cols *2)+@max_var_key_size
else
select @variable_key_size=0

-- Calculate the non-leaf index row size
select @nl_index_row_size=@fixed_key_size +@variable_key_size+@index_null_bitmap+1+8

--Next, calculate the number of non-leaf index rows per page (8096 free bytes per page):

select @nl_index_rows_per_page=(8096)/(@nl_index_row_size+2)

-- Next, calculate the leaf index row size

select @index_row_size=@cindex_row_size + @fixed_key_size + @variable_key_size+@index_null_bitmap+1

-- Next, calculate the number of leaf level index rows per page

select @index_rows_per_page = 8096/(@index_row_size + 2)

-- Next, calcuate the number of reserved free index rows/page based on fill factor

if @fillfactor=0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the non-clustered index pages in this section
select @free_index_rows_per_page=0
else
select @free_index_rows_per_page= 8096 * ((100-@fillfactor)/100)/@index_row_size

-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.

select @num_pages_level_0=ceiling(convert(decimal,@num_rows)/@index_rows_per_page-@free_index_rows_per_page)

select @Num_Index_Pages=@num_pages_level_0
select @num_pages_level_x=@num_pages_level_0

while @num_pages_level_x <> 1
begin
select @num_pages_level_y=ceiling(convert(decimal,@num_pages_level_x)/@nl_index_rows_per_page)
select @Num_Index_Pages=@Num_Index_Pages+@num_pages_level_y
select @num_pages_level_x=@num_pages_level_y
end

select @total_num_nonclustered_index_pages=@total_num_nonclustered_index_pages+@Num_Index_Pages
end
end
--*********************************************
-- END NON-CLUSTERED INDEX SECTION
--*********************************************
-- display numbers

select @tot_pages=@num_pages + @Num_CIndex_Pages + @total_num_nonclustered_index_pages
select @table_size_in_bytes= 8192*@tot_pages
select @table_size_in_kbytes= @table_size_in_bytes/1024.0
select @table_size_in_meg= str(@table_size_in_kbytes/1000.0,17,2)

select substring(@table_name,1,20) as 'Table Name',
convert(varchar(10),@table_size_in_meg) as 'MB Estimate',
@tot_pages as 'Total Pages',
@num_pages as '#Data Pgs',
@Num_CIndex_Pages as '#Clustered Idx Pgs',
@total_num_nonclustered_index_pages as '#NonClustered Idx Pgs'
GO
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
use like

exec CalcSpace 'internalpart',1        --- size of the table for one row
exec CalcSpace 'internalpart',100        --- size of the table for 100 rows
exec CalcSpace 'internalpart',1000        --- size of the table for 1000 rows
exec CalcSpace 'internalpart',10000        --- size of the table for 10000 rows
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
0
 

Author Comment

by:motioneye
Comment Utility
Hi,
I've installed the sp and  run it

With 1 row and have the values as below:
Table          MB estimate   Total Pages        #data pgs       Clustereted indx pgs        nonclustered indx pgs
internalpart  0.42            51                     1           1                                50

With 10 row and have the values as below:
Table          MB estimate   Total Pages        #data pgs       Clustereted indx pgs        nonclustered indx pgs
internalpart  0.42            53                     2           1                                50

what's is the difference in MB estimate between 1 row and 10 rows, both carry  the same values 0.42
0
 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
There is an undocumented procedure just for that and you can use it's logic to calculate anything you want...

sp_MSTableSpace 'Table name'

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
Comment Utility
>what's is the difference in MB estimate between 1 row and 10 rows, both carry  the same values 0.42

Some space will be allocated by default for both data and log. In this case this alloted disk space is sufficient to allocate both 1 row and 10 rows ..
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
ASP SQL Syntax Duplicate Key 7 64
MS SQL Backup 24 69
Test a query 23 10
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now