Estimating the size of a DB from a physical design

[Amelia Mariotti]

Can someone recommend an application that produces an estimate of the size of a database from column definitions and
estimated number of rows in each table? -- Thanks

On 1/5/99 2:11:06 PM, Amelia Mariotti wrote:
> Can someone recommend an application that produces an estimate of the size
> of a database from column definitions and
estimated number of rows in each
> table? -- Thanks

Try this stored procedure. I don't know where it originated

CREATE PROCEDURE sp_estspace
/* A procedure to estimate the disk space requirements of a table
** and its associated indexes.
** Written by Malcolm Colton
** Modifications by Hal Spitz, Jim Panttaja
**
**
*/
(@table_name varchar(30)=null, /* name of table to estimate */
@no_of_rows float = 1, /* number of rows in the table */
@fill_factor float = 0, /* the fill factor */
@cols_to_max varchar(255) =null /* variable length columns for which
to use the maximum rather than 50% of
the maximum length */
)
as
declare @msg varchar(120)
/* Give usage statement if @table_name is null */
If @table_name = null or @no_of_rows = 1
begin
print 'Usage is:'
print 'sp_estspace table_name, no_of_rows, fill_factor, cols_to_max'
print 'where table_name is the name of the table,'
print ' no_of_rows is the number of rows in the table,'
print ' fill_factor is the index fill factor (default = 0) '
print ' cols_to_max is a list of the variable length columns for which'
print ' to use the maximum length instead of the average'
print ' (default = null)'
print 'Examples: sp_estspace titles, 10000, 50, "title, notes"'
print ' sp_estspace titles, 50000'
print ' sp_estspace titles, 50000, 0, null, 40'
return
end
declare @sum_fixed int,
@sum_var int,
@sum_avgvar int,
@table_id int,
@num_var int,
@data_pages float,
@sysstat tinyint,
@temp float,
@index_id int,
@last_id int,
@i int,
@indlevel_pages float,
@key varchar(30),
@usertype tinyint,
@type tinyint,
@indlevel tinyint,
@vartype smallint,
@more bit,
@next_indlevel float,
@rows_per_page smallint,
@row_len smallint,
@length tinyint,
@index_name varchar(30),
@page_size smallint,
@page_K tinyint,
@index_type varchar(20),
@factor float
select @sum_fixed=0,
@sum_var=0,
@sum_avgvar=0,
@table_id=0,
@num_var=0,
@data_pages=0,
@row_len=0,
@sysstat=0
set nocount on
/* Make sure table exists */
select @sysstat = sysstat,
@table_id = id
from sysobjects where name = @table_name
and uid = user_id()
if @sysstat & 7 not in (1,3)
begin
select @msg = "The "+@table_name +" table does not exist."
print @msg
return
end
/* Get machine page size */
select @page_size = low - 32
from master.dbo.spt_values
where type = 'E'
and number = 1
select @page_K = (@page_size +32) /1024
select @fill_factor = @fill_factor / 100.0
/* Create tables for results */
create table #results
(name varchar(30),
type varchar(12),
indlevel tinyint,
pages float,
Kbytes float)
/* Create table of column info for the table to be estimated */
select length, type, name, offset
into #col_table
from syscolumns
where id = @table_id
/* Look up the important values from this table */
select @sum_fixed = isnull(sum(length),0)
from #col_table
where offset !< 0
select @num_var = isnull(count(*),0), @sum_var = isnull(sum(length),0)
from #col_table
where offset < 0
and charindex(name, @cols_to_max) > 0
select @num_var = @num_var + isnull(count(*),0),
@sum_avgvar = isnull(sum(length / 2),0)
from #col_table
where offset < 0
and charindex(name, @cols_to_max) = 0
/* Calculate the data page requirements */
if @num_var = 0
select @row_len = 4.0 + @sum_fixed
else
select @row_len = 8.0 + @sum_fixed + @sum_var +@sum_avgvar + @num_var
+ (@sum_var +@sum_avgvar) / 256.0
/* Allow for fill-factor if set to other than zero */
if @fill_factor = 0
select @temp = convert(float, @no_of_rows) *
( convert(float, @row_len) / convert(float, @page_size) )
else
begin
select @temp = convert(float, @no_of_rows) /
(convert(float, @page_size) * convert(float, @fill_factor) )
select @temp = convert(float, @row_len) * @temp
end
/* Now add in allocation pages */
select @temp = @temp +(@temp / 256.0)
select @data_pages = @temp + 1.0
if @data_pages < 8.0
select @data_pages = 8.0
insert #results values
(@table_name, &#39;data&#39;, 0, @data_pages, @data_pages * @page_K)
/* See if the table has any indexes */
select @index_id = min(indid)
from sysindexes
where id = @table_id
and indid > 0
if @index_id = null /* We&#39;ve finished if there are no indexes */
begin
select @msg = @table_name + &#39; has no indexes&#39;
print @msg
select name, type, indlevel,
Pages = str(pages,12,0), Kbytes = str(Kbytes,12,0)
from #results
select Total_Mbytes = str(sum(Kbytes)/1000.0,15,0)
from #results
drop table #results
return
end
select @sum_fixed = 0,
@sum_var = 0,
@num_var = 0,
@temp = 0
/* For each index, calculate the important variables
** use them to calculate the index size, and print it */
while @index_id != null
begin
select @index_name = name
from sysindexes
where id = @table_id
and indid = @index_id
and indid < 255
if @index_id = 1
select @index_type = &#39;clustered&#39;
else
select @index_type = &#39;nonclustered&#39;
select @num_var = 0,
@sum_var = 0,
@sum_fixed = 0
select @i = 1
/* Look up each of the key fields for the index */
while @i <= 16
begin
select @key = index_col(@table_name, @index_id, @i)
if @key = null
break
else /* Process one key field */
begin
select @type = type, @length = length, @vartype = offset
from syscolumns
where id = @table_id
and name = @key
if @vartype < 0
select @num_var = @num_var + 1
else
select @sum_fixed = @sum_fixed + @length
if @vartype < 0 /* variable:check if in @cols_to_max */
begin
if charindex(@key, @cols_to_max) = 0
select @sum_var = @sum_var + (@length / 2)
else
select @sum_var = @sum_var + @length
end
end
select @i = @i + 1 /* Get next key field in this index */
end
/* Calculate the space used by this index */
if @num_var = 0
select @row_len = 5 + @sum_fixed
else
select @row_len = @sum_fixed + @sum_var + @num_var + 8
if @index_id != 1 /* add row id for nc indexes */
select @row_len = @row_len + 4
select @indlevel = 0
/* Allow for fill-factor if set to other than zero */
if @fill_factor = 0
select @rows_per_page = @page_size / @row_len - 2
else
select @rows_per_page = @page_size / @row_len * @fill_factor
if @rows_per_page > 256
select @rows_per_page = 256
/* For clustered indexes, the first indlevel of index is based on the
** number of data pages.
** For nonclustered, it is the number of data rows */
if @index_id = 1
select @next_indlevel = @data_pages
else
select @next_indlevel = @no_of_rows
select @more = 1 /* Flag for end of index indlevels */
while @more = 1
begin
/* calculate the number of pages at a single index indlevel */
select @temp = @next_indlevel / convert(float, @rows_per_page)
/* Add in a factor for allocation pages */
if @temp > 200.0
select @temp = @temp + (@temp /256.0) + 1.0
select @indlevel_pages = @temp
insert #results values
(@index_name, @index_type, @indlevel, @indlevel_pages,
@indlevel_pages * @page_K)
if @index_id != 1 and @indlevel = 0 /* adjust NC non-leaf rows */
begin
select @row_len = @row_len + 4
/* Allow for fill-factor if set to other than zero */
if @fill_factor = 0
select @rows_per_page = @page_size/@row_len - 2
else
select @rows_per_page = @page_size/@row_len*@fill_factor
end
if @rows_per_page > 256
select @rows_per_page = 256
select @next_indlevel = @indlevel_pages
select @indlevel = @indlevel + 1
/* see if we can fit the next indlevel in 1 page */
if @rows_per_page >= @next_indlevel
select @more = 0
end
/* Account for single root page */
if @indlevel_pages > 1
insert #results values
(@index_name, @index_type, @indlevel, 1, @page_K)
/* Now look for next index id for this table */
select @last_id = @index_id
select @index_id = null
select @index_id = min(indid)
from sysindexes
where id = @table_id
and indid > @last_id
and indid < 255
end
select name, type, indlevel, Pages = str(pages,12,0), Kbytes = str(Kbytes,12,0)
from #results
select Total_Mbytes = str(sum(Kbytes)/1000.0,15,0)
from #results
drop table #results
drop table #col_table
return