We have a fairly simple record structure, but one of our customers is asking for the amount of space required by our application.
Whilst we can tell them how big the data is (are), is there a document anywhere that can give us an idea of the overhead of certain field types? i.e. Binary fields need X bytes + data, integer fields need Y bytes + data...
-
Follow
Oops, something didn't work.
Sign In to be begin to follow this thread
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in. You need to be signed in to Channel 9 to use this feature.Getting "follow" information
Start following this thread
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in and view them all on your notifications page.Removing your "follow"
Setting up your "follow"
Did you know you can
sign up for email notifications?
-
-
The only source I'm aware of is in Inside SQL Server 2000. Unfortunately I left my copy at work.
IIRC, there's a certain amount of per-row overhead anyway, then there's a bitmap for NULLable columns (one bit per nullable column, rounded up to a whole number of bytes). Each bit column occupies one bit of storage; all the bits for a single row are stored together, rounded up to a whole number of bytes. Variable-length data (varchar, nvarchar, varbinary, plus text, ntext and image if text in row is on and there's space) is stored at the end of the page; the field itself contains a (16-bit?) pointer to the data itself, which is length prefixed.
Fixed-size types have no overhead, again IIRC.
You can ask the database by querying the sysindexes table. If the table has a clustered index, look for the row where indid = 1. Otherwise look for indid = 0. To find the table you're after, join sysobjects on sysobjects.id = sysindexes.id. The minimum length for a row is the minlen column. The maximum possible length is xmaxlen, but I can't recall if this includes all the overhead. -
Mike Dimmick wrote:The only source I'm aware of is in Inside SQL Server 2000. Unfortunately I left my copy at work.
IIRC, there's a certain amount of per-row overhead anyway, then there's a bitmap for NULLable columns (one bit per nullable column, rounded up to a whole number of bytes). Each bit column occupies one bit of storage; all the bits for a single row are stored together, rounded up to a whole number of bytes. Variable-length data (varchar, nvarchar, varbinary, plus text, ntext and image if text in row is on and there's space) is stored at the end of the page; the field itself contains a (16-bit?) pointer to the data itself, which is length prefixed.
Fixed-size types have no overhead, again IIRC.
You can ask the database by querying the sysindexes table. If the table has a clustered index, look for the row where indid = 1. Otherwise look for indid = 0. To find the table you're after, join sysobjects on sysobjects.id = sysindexes.id. The minimum length for a row is the minlen column. The maximum possible length is xmaxlen, but I can't recall if this includes all the overhead.
Mike you are a star. Please come and work for me
-
figuerres
???
DO not forget to show the size of the indexes on each table.... sometimes that's as big a a table for some indexes.
thouhg generaly less than that....
but if a table has a bunch of different indexes it will take a bunch of room....
also SQL Enterprise Manager can show you graphs of table and index size.
open SQL EM, click on a database name, right click and select View |--> Taskpad
use the GUI and you get info on all the db or on the tables and indexes....
also look up this "Estimating the Size of a Table with a Clustered Index" in sql server books on-line
it will show some formulas for computing sizes and links to related stuff, look and they have the info to do the math of any table. -
stevef100
DaddyO
try this sizing SP
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
/* 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'
Thread Closed
This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.