Binaryworld - Estimate the table size (Data + Index) for a given # of rows ... [ SQL Server -> Stored Procedure ]

Ever tried to estimate the table total size which include data and index in calculation ? Believe me its not easy job to estimate the size of a table for a give # of rows.

Here is the kool SP which does it for you. Full credit goes to Sharon Dooley for his excellent Job.

Click here to copy the following block

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

create procedure usp_CalcSpace
/************************************************************************/
/* Stored Procedure: usp_CalcSpace */
/* Creation Date: 1999-04-11 */
/* Copyright: - */
/* Written by: Sharon Dooley */
/* Modified by npatel on 5/23/05 */
/* */
/* 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 . If this is zero the space will be calculated for currunt # of rows in the specified table*/
/* */
/* Output Parameters: <list any output parameters> */
/* - */
/* */
/* Return Status: <list any return codes> */
/* - */
/* */
/* Usage: <a sample usage statement> */
/* EXEC usp_CalcSpace 'MyTable', 10000 */
/* */
/* Other info: <other info for this SP> */
/* The is a direct copy from the usp_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
begin
print 'Usage is:'
print ' usp_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
if @num_rows = 0
SELECT @num_rows=rows FROM sysindexes WHERE id = OBJECT_ID(@table_name) AND indid < 2

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',
@num_rows as 'Rows to estimate',
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

EXEC usp_CalcSpace 'DBTRACT'

go

Output

Table Name           Rows to estimate MB Estimate Total Pages #Data Pgs   #Clustered Idx Pgs #NonClustered Idx Pgs 
-------------------- ---------------- ----------- ----------- ----------- ------------------ --------------------- 
DBTRACT              660542           101.91      12739       12703       36                 0