SQL Server Performance

Calculate table size

Discussion in 'Contribute Your SQL Server Scripts' started by Argyle, Jul 19, 2004.

  1. Argyle New Member

    Found this nice script lying around. Estimate the size of your tables based on all the details described in BOL on how to do it. Written for SQL 7 but I've had no problems using it in SQL 2000.<br /><br /><pre><br />if exists (select * from sysobjects where id = object_id(N'[dbo].[calcspace]') <br />and OBJECTPROPERTY(id, N'IsProcedure') = 1)<br />drop procedure [dbo].[calcspace]<br />GO<br /><br />create procedure CalcSpace<br />/************************************************************************/<br />/* Stored Procedure: CalcSpace*/<br />/* Creation Date: 1999-04-11*/<br />/* Copyright: -*/<br />/* Written by: Sharon Dooley*/<br />/**/<br />/* Purpose: &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />urpose of the script&gt;*/<br />/*A procedure to estimate the disk space requirements of a table.*/<br />/*Refer to Books OnLine topic "Estimating the size of a table..." */<br />/*for a detailed description*/<br />/**/<br />/* Input Parameters: &lt;list any input parameters&gt;*/<br />/*@table_nameVARCHAR(30)Name of table to estimate*/<br />/*@num_rowsINTNumber of rows in the table*/<br />/**/<br />/* Output Parameters: &lt;list any output parameters&gt;*/<br />/*-*/<br />/**/<br />/* Return Status: &lt;list any return codes&gt;*/<br />/*-*/<br />/**/<br />/* Usage: &lt;a sample usage statement&gt;*/<br />/*EXEC CalcSpace 'MyTable', 10000*/<br />/**/<br />/* Other info: &lt;other info for this SP&gt;*/<br />/*The is a direct copy from the CalcSpace stored procedure made by*/<br />/*Sharon Dooley, 1999-04-11. The only change is the added */<br />/*documentation header and a small bug fix mentioned below.*/<br />/**/<br />/* Updates: &lt;this section is used to track changes to the script&gt;*/<br />/* Date Author Purpose*/<br />/* 2000-07-04Magnus AnderssonChanged @sysstat from tinyint */<br />/* to int to prevent overflow */<br />/* scenario. Added documentation.*/<br />/* */<br />/************************************************************************/<br /><br />(@table_namevarchar(30)=null,-- name of table to estimate <br /> @num_rowsint = 0) -- number of rows in the table <br />as<br /><br />declare @msgvarchar(120)<br /><br />--Give usage statement if @table_name is null<br /><br />if @table_name = null or @num_rows = 0<br />begin<br />print 'Usage is:'<br />print ' calcspace table_name, no_of_rows'<br />print 'where table_name is the name of the table,'<br />print ' no_of_rows is the number of rows in the table,' <br />print ' '<br />return<br />end<br /><br />declare@num_fixed_colint,<br />@fixed_data_sizeint,<br />@num_variable_colint, <br />@max_var_sizeint,<br />@null_bitmapint,<br />@variable_data_sizeint,<br />@table_idint,<br />@num_pagesint,<br />@table_size_in_bytesint,<br />@table_size_in_megreal,<br />@table_size_in_kbytesreal,<br />@sysstatint,<br />@row_sizeint,<br />@rows_per_pageint,<br />@free_rows_per_pageint,<br />@fillfactorint,<br /> @num_fixed_ckey_cols int,<br />@fixed_ckey_size int,<br />@num_variable_ckey_cols int,<br />@max_var_ckey_size int,<br />@cindex_null_bitmapint,<br />@variable_ckey_sizeint,<br />@cindex_row_sizeint,<br />@cindex_rows_per_pageint,<br />@data_space_usedint,<br />@num_pages_clevel_0int,<br />@num_pages_clevel_1int,<br />@num_pages_clevel_xint,<br />@num_pages_clevel_yint,<br />@Num_CIndex_Pagesint,<br />@clustered_index_size_in_bytesint,<br />@num_fixed_key_cols int,<br />@fixed_key_size int,<br />@num_variable_key_cols int,<br />@max_var_key_size int,<br />@index_null_bitmapint,<br />@variable_key_sizeint,<br />@nl_index_row_sizeint,<br />@nl_index_rows_per_pageint,<br />@index_row_sizeint,<br />@index_rows_per_pageint,<br />@free_index_rows_per_pageint,<br />@num_pages_level_0int,<br />@num_pages_level_1int,<br />@num_pages_level_xint,<br />@num_pages_level_yint,<br />@num_index_pagesint,<br />@nonclustered_index_sizeint,<br />@total_num_nonclustered_index_pagesint,<br />@free_cindex_rows_per_pageint,<br />@tot_pagesint<br /><br />-- initialize variables<br />select@num_fixed_col=0,<br />@fixed_data_size=0,<br />@num_variable_col=0, <br />@max_var_size=0,<br />@null_bitmap=0,<br />@variable_data_size=0,<br />@table_id=0,<br />@num_pages=0,<br />@table_size_in_bytes=0,<br />@table_size_in_meg=0,<br />@table_size_in_kbytes=0,<br />@sysstat=0,<br />@row_size=0,<br />@rows_per_page=0,<br /> @num_fixed_ckey_cols =0,<br />@fixed_ckey_size =0,<br />@num_variable_ckey_cols =0,<br />@max_var_ckey_size =0,<br />@cindex_null_bitmap=0,<br />@variable_ckey_size=0,<br />@cindex_row_size=0,<br />@cindex_rows_per_page=0,<br />@data_space_used=0,<br />@num_pages_clevel_0=0,<br />@num_pages_clevel_1=0,<br />@Num_CIndex_Pages=0,<br />@clustered_index_size_in_bytes=0,<br />@num_fixed_key_cols =0,<br />@fixed_key_size =0,<br />@num_variable_key_cols =0,<br />@max_var_key_size =0,<br />@index_null_bitmap=0,<br />@variable_key_size=0,<br />@nl_index_row_size=0,<br />@nl_index_rows_per_page=0,<br />@index_row_size=0,<br />@index_rows_per_page=0,<br />@free_index_rows_per_page=0,<br />@num_pages_level_0=0,<br />@num_pages_level_1=0,<br />@num_pages_level_x=0,<br />@num_pages_level_y=0,<br />@num_index_pages=0,<br />@nonclustered_index_size=0,<br />@total_num_nonclustered_index_pages=0,<br />@free_cindex_rows_per_page=0,<br />@tot_pages=0<br /><br />set nocount on<br /><br />--*********************************************<br />-- MAKE SURE TABLE EXISTS<br />--*********************************************<br /><br />select @sysstat = sysstat,<br />@table_id = id<br />from sysobjects where name = @table_name<br /><br />if @sysstat & 7 not in (1,3)<br />begin<br />select @msg = 'I can''t find the table '+@table_name<br />print @msg<br />return<br />end<br /><br />--*********************************************<br />-- ESTIMATE SIZE OF TABLE<br />--*********************************************<br /><br />-- get total number and total size of fixed-length columns<br /><br />select @num_fixed_col = count(name), <br />@fixed_data_size = sum(length) <br />from syscolumns <br />where id= @table_id and xtype in <br />(<br />select xtype from systypes where variable=0<br />)<br /><br />if @num_fixed_col= 0 --@fixed_data_size is null. change to 0<br />select @fixed_data_size=0<br /><br />-- get total number and total maximum size of variable-length columns<br /><br />select @num_variable_col=count(name), <br />@max_var_size= sum(length) <br />from syscolumns <br />where id= @table_id and xtype in <br />(<br />select xtype from systypes where variable=1<br />)<br />if @num_variable_col= 0 --@max_var_size is null. change to 0<br />select @max_var_size=0<br /><br />-- get portion of the row used to manage column nullability<br /><br />select @null_bitmap=2+((@num_fixed_col+7)/<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />-- determine space needed to store variable-length columns <br />-- this assumes all variable length columns will be 100% full<br />if @num_variable_col = 0<br />select @variable_data_size=0<br />else<br />select @variable_data_size = 2 + (@num_variable_col *2 )+ @max_var_size<br /><br />-- get row size<br /><br />select @row_size= @fixed_data_size + <br />@variable_data_size + <br />@null_bitmap + 4 -- 4 represents the data row header<br /><br /><br />-- get number of rows per page<br /><br />select @rows_per_page = (8096) / (@row_size+2)<br /><br />-- If a clustered index is to be created on the table, <br />-- calculate the number of reserved free rows per page, <br />-- based on the fill factor specified. <br />-- If no clustered index is to be created, specify Fill_Factor as 100. <br /><br />select @fillfactor = 100 -- initialize it to the maximum<br />select @free_rows_per_page = 0 --initialize to no free rows/page<br />select @fillfactor=OrigFillFactor <br />from sysindexes <br />where id = @table_id and indid=1 -- indid of 1 means the index is clustered<br /><br />if @fillfactor&lt;&gt;0<br />-- a 0 fill factor ALMOST fills up the entire page, but not quite.<br />--The doc says that fill factor zero leaves 2 empty rows (keys) <br />--in each index page and no free rows in data pages of clustered <br />--indexes and leaf pages of non-clustered. <br />--We are working on the data pages in this section<br />select @free_rows_per_page=8096 * ((100-@fillfactor)/100)/@row_size<br /><br />-- get number of pages needed to store all rows<br /><br />select @num_pages = ceiling(convert(dec,@num_rows) / (@rows_per_page-@free_rows_per_page))<br /><br />-- get storage needed for table data<br /><br />select @data_space_used=8192*@num_pages<br /><br /><br />--*********************************************<br />-- COMPUTE SIZE OF CLUSTERED INDEX IF ONE EXISTS<br />--*********************************************<br /><br />-- create a temporary table to contain columns in clustered index. System table<br />-- sysindexkeys has a list of the column numbers contained in the index<br /><br />select colid into #col_list <br />from sysindexkeys where id= @table_id and indid=1 -- indid=1 means clustered<br /><br />if (select count(*) from #col_list) &gt;0 -- do the following only if clustered index exsists<br />begin<br />-- get total number and total maximum size of fixed-length columns in clustered index<br /><br />select @num_fixed_ckey_cols=count(name), <br />@fixed_ckey_size= sum(length) <br />from syscolumns <br />where id= @table_id and xtype in <br />(<br />select xtype from systypes where variable=0<br />) <br />and colid in (select * from #col_list)<br /><br />if @num_fixed_ckey_cols= 0 --@fixed_ckey_size is null. change to 0<br />select @fixed_ckey_size=0<br /><br />-- get total number and total maximum size of variable-length columns in clustered index<br /><br />select @num_variable_ckey_cols=count(name), <br />@max_var_ckey_size= sum(length) <br />from syscolumns <br />where id= @table_id and xtype in <br />(<br />select xtype from systypes where variable=1<br />) <br />and colid in (select * from #col_list)<br /><br />if @num_variable_ckey_cols= 0 --@max_var_ckey_size is null. change to 0<br />select @max_var_ckey_size=0<br /><br />-- If there are fixed-length columns in the clustered index, <br />-- a portion of the index row is reserved for the null bitmap. Calculate its size: <br />if @num_fixed_ckey_cols &lt;&gt; 0<br />select @cindex_null_bitmap=2+((@num_fixed_ckey_cols + 7)/<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />else<br />select @cindex_null_bitmap=0<br /><br />-- If there are variable-length columns in the index, determine how much <br />-- space is used to store the columns within the index row: <br /><br />if @num_variable_ckey_cols &lt;&gt; 0<br />select @variable_ckey_size=2+(@num_variable_ckey_cols *2)+@max_var_ckey_size<br />else<br />select @variable_ckey_size=0<br /><br />-- Calculate the index row size<br /><br />select @cindex_row_size=@fixed_ckey_size +@variable_ckey_size+@cindex_null_bitmap+1+8<br /><br />--Next, calculate the number of index rows per page (8096 free bytes per page): <br /><br />select @cindex_rows_per_page=(8096)/(@cindex_row_size+2)<br /><br />-- consider fillfactor<br />if @fillfactor=0<br />select @free_cindex_rows_per_page = 2<br />else<br /> select @free_cindex_rows_per_page= 8096 * ((100-@fillfactor)/100)/@cindex_row_size<br /><br />-- Next, calculate the number of pages required to store <br />-- all the index rows at each level of the index. <br /><br />select @num_pages_clevel_0=ceiling(convert(decimal,(@data_space_used/8192))/(@cindex_rows_per_page-@free_cindex_rows_per_page))<br />select @Num_CIndex_Pages=@num_pages_clevel_0<br />select @num_pages_clevel_x=@num_pages_clevel_0<br /><br />while @num_pages_clevel_x &lt;&gt; 1<br />begin<br />select @num_pages_clevel_y=ceiling(convert(decimal,@num_pages_clevel_x)/(@cindex_rows_per_page-@free_cindex_rows_per_page))<br />select @Num_CIndex_Pages=@Num_CIndex_Pages+@num_pages_clevel_y<br />select @num_pages_clevel_x=@num_pages_clevel_y<br />end<br />end<br /><br />--*********************************************<br />-- END CLUSTERED INDEX SECTION<br />--*********************************************<br /><br />--*********************************************<br />-- BEGIN NON-CLUSTERED INDEX SECTION<br />--*********************************************<br /><br />-- create temp table with non-clustered index info<br /><br />select indid, colid into #col_list2 <br />from sysindexkeys where id= @table_id and indid&lt;&gt;1 -- indid=1 means clustered<br /><br />if (select count(*) from #col_list2) &gt;0 -- do the following only if non-clustered indexes exsist<br />begin<br />declare @i int -- a counter variable<br />select @i=1 -- initilize to 2, because 1 is id of clustered index<br /><br />while @i&lt; 249 -- max number of non-clustered indexes<br />begin<br />select @i=@i+1 -- look for the next non-clustered index<br />-- reinitialize all numbers<br />select @num_fixed_key_cols = 0,<br />@fixed_key_size = 0,<br />@num_variable_key_cols = 0,<br />@max_var_key_size = 0,<br />@index_null_bitmap = 0,<br />@variable_key_size = 0,<br />@nl_index_row_size = 0,<br />@nl_index_rows_per_page = 0,<br />@index_row_size = 0,<br />@index_rows_per_page = 0,<br />@free_index_rows_per_page = 0,<br />@num_pages_level_0 = 0,<br />@num_pages_level_x = 0,<br />@num_pages_level_y = 0,<br />@Num_Index_Pages = 0<br /><br />-- get total number and total maximum size <br />-- of fixed-length columns in nonclustered index<br />select @num_fixed_key_cols=count(name), <br />@fixed_key_size= sum(length) <br />from syscolumns <br />where id= @table_id and xtype in <br />(<br />select xtype from systypes where variable=0<br />) <br />and colid in (select colid from #col_list2 where indid=@i)<br />if @num_fixed_key_cols= 0 --@fixed_key_size is null. change to 0<br />select @fixed_key_size=0<br /><br />-- get total number and total maximum size of variable-length columns in index<br /><br />select @num_variable_key_cols=count(name), <br />@max_var_key_size= sum(length) <br />from syscolumns <br />where id= @table_id and xtype in <br />(<br />select xtype from systypes where variable=1<br />) <br />and colid in (select colid from #col_list2 where indid=@i)<br />if @num_variable_key_cols= 0 --@max_var_key_size is null. change to 0<br />select @max_var_key_size=0<br /><br />if @num_fixed_key_cols = 0 and @num_variable_key_cols = 0 --there is no index<br />continue<br />-- If there are fixed-length columns in the non-clustered index, <br />-- a portion of the index row is reserved for the null bitmap. Calculate its size: <br />if @num_fixed_key_cols &lt;&gt; 0<br />select @index_null_bitmap=2+((@num_fixed_key_cols + 7)/<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />else<br />select @index_null_bitmap=0<br /><br />-- If there are variable-length columns in the index, determine how much <br />-- space is used to store the columns within the index row: <br /><br />if @num_variable_key_cols &lt;&gt; 0<br />select @variable_key_size=2+(@num_variable_key_cols *2)+@max_var_key_size<br />else<br />select @variable_key_size=0<br /><br />-- Calculate the non-leaf index row size<br />select @nl_index_row_size=@fixed_key_size +@variable_key_size+@index_null_bitmap+1+8<br /><br />--Next, calculate the number of non-leaf index rows per page (8096 free bytes per page): <br /><br />select @nl_index_rows_per_page=(8096)/(@nl_index_row_size+2)<br /><br />-- Next, calculate the leaf index row size<br /><br />select @index_row_size=@cindex_row_size + @fixed_key_size + @variable_key_size+@index_null_bitmap+1<br /><br />-- Next, calculate the number of leaf level index rows per page<br /><br />select @index_rows_per_page = 8096/(@index_row_size + 2)<br /><br />-- Next, calcuate the number of reserved free index rows/page based on fill factor<br /><br />if @fillfactor=0<br />-- a 0 fill factor ALMOST fills up the entire page, but not quite.<br />--The doc says that fill factor zero leaves 2 empty rows (keys) <br />--in each index page and no free rows in data pages of clustered <br />--indexes and leaf pages of non-clustered. <br />--We are working on the non-clustered index pages in this section<br />select @free_index_rows_per_page=0<br />else<br />select @free_index_rows_per_page= 8096 * ((100-@fillfactor)/100)/@index_row_size<br /><br />-- Next, calculate the number of pages required to store <br />-- all the index rows at each level of the index. <br /><br />select @num_pages_level_0=ceiling(convert(decimal,@num_rows)/@index_rows_per_page-@free_index_rows_per_page)<br /><br />select @Num_Index_Pages=@num_pages_level_0<br />select @num_pages_level_x=@num_pages_level_0<br /><br />while @num_pages_level_x &lt;&gt; 1<br />begin<br />select @num_pages_level_y=ceiling(convert(decimal,@num_pages_level_x)/@nl_index_rows_per_page)<br />select @Num_Index_Pages=@Num_Index_Pages+@num_pages_level_y<br />select @num_pages_level_x=@num_pages_level_y<br />end<br /><br />select @total_num_nonclustered_index_pages=@total_num_nonclustered_index_pages+@Num_Index_Pages<br />end<br />end<br />--*********************************************<br />-- END NON-CLUSTERED INDEX SECTION<br />--*********************************************<br />-- display numbers<br /><br />select @tot_pages=@num_pages + @Num_CIndex_Pages + @total_num_nonclustered_index_pages<br />select @table_size_in_bytes= 8192*@tot_pages<br />select @table_size_in_kbytes= @table_size_in_bytes/1024.0<br />select @table_size_in_meg= str(@table_size_in_kbytes/1000.0,17,2)<br /><br />select substring(@table_name,1,20) as 'Table Name',<br />convert(varchar(10),@table_size_in_meg) as 'MB Estimate',<br />@tot_pages as 'Total Pages',<br />@num_pages as '#Data Pgs', <br />@Num_CIndex_Pages as '#Clustered Idx Pgs',<br />@total_num_nonclustered_index_pages as '#NonClustered Idx Pgs' <br /></pre>
  2. dandrade New Member

    If I execute

    exec MyTable, 1

    I will get the equivalent value of rowsize?

    Then, I could use compare this value with Pages Scanned from DBCC SHOWCONTIG to know if there is internal fragmentation?
  3. dandrade New Member

    quote:Originally posted by dandrade
    exec MyTable, 1

    I meant EXEC CalcSpace MyTable, 1
  4. Argyle New Member

    I would think so, give it a try.
  5. DerekR New Member

    Can I advise using bigInt for the total vars @total_num_nonclustered_index_pages and @tot_pages.
    I tried this out on some of my larger DBs and it overflowed.
    There's a sale on bigInts, same price as ints =, so no probs there.
  6. darre1 New Member

    I'd like to make another recommended change.

    It does not take account for the correct physical storage of BIT columns. i.e. 1-8 BITs require 1 byte, 9-16 BITs require 2 bytes etc.

    To fix this, replace this:


    -- 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
    )

    ...with this:


    -- get total number and total size of fixed-length columns
    -- (BIT columns only)

    select @num_fixed_col = count(name),
    @fixed_data_size = (sum(length) + 7) / 8
    from syscolumns
    where id = @table_id and xtype in
    (
    select xtype from systypes where name = 'bit'
    )

    -- get total number and total size of fixed-length columns
    -- (non BIT columns only)

    select @num_fixed_col = @num_fixed_col + count(name),
    @fixed_data_size = @fixed_data_size + sum(length)
    from syscolumns
    where id = @table_id and xtype in
    (
    select xtype from systypes where variable = 0 AND name <> 'bit'
    )
  7. GoranJ New Member

    I got overflow error messages so I simply changed every int to bigint and it worked fine.
  8. vinayak New Member

    Hi,
    the above procedure not giving the accurate table size for SQL 2005 and SQL 2008.
    Can you pelase modify the procedure so that it can work fine on "SQL 2005 and 2008"?
    Thanks in advance.
  9. vinayak New Member

    Hi,
    I executed the above stored procedure on SQL 2005 and SQL 2008 but the procedure is giving incorrect size values.
    Can somebody modify this so that it can work on SQL server 2005 and 2008 also.
    Can somebody pelase help me on this?
    Thanks in advance.
  10. FrankKalis Moderator

    The author of the original script is not a regular poster here on SSP as far as I'm aware. So chances are that Sharon will miss this request and you will have to do that changes on your own.
  11. vinayak New Member

    This procedure is not working for SQL 2005 and 2008.
    Can you please modify it
  12. ghemant Moderator

  13. vinayak New Member

    Hi,
    i tried this script with sql 2005 and sql 2008. its not giving the coorect size values.
    Can you please modify it for 2005 and 2008.
  14. RGKN New Member

    As Frank Kalis suggested you could always modify it yourself. It seems to be the way with fora, that one gives and takes rather than just demanding help and taking.
    Here is a bit of code that will give you similar information to right clicking on a table and displaying the properties. Can it be trusted? Probably not. Is it the most efficient way? Absolutely not.
    You can enhance it so that it does all tables, or even so that it is a bit more elegant and then post it back.
    Regards,
    Robert.declare @PageSize float
    select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'SELECT
    tbl.name, ISNULL
    ((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM
    sys.indexes as iJOIN
    sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_idJOIN
    sys.allocation_units as a ON a.container_id = p.partition_idwhere
    i.object_id = tbl.object_id),0.0) AS [DataSpaceUsed_KB],ISNULL
    ((select @PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM
    sys.indexes as iJOIN
    sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_idJOIN
    sys.allocation_units as a ON a.container_id = p.partition_idwhere
    i.object_id = tbl.object_id),0.0) AS [IndexSpaceUsed_KB]FROMsys.tables
    AS tblWHERE
    tbl.name = 'TBL_A'
  15. satya Moderator

    Well said Rob, over the time one should look in a way that the scripts needs change as per the versions (2000 to 2005 and 2008).... just a little pro-activeness can solve major issues [:)]

Share This Page