Tech Off Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL Server Record Size.

Back to Forum: Tech Off
  • User profile image
    Rossj

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

  • User profile image
    Mike Dimmick

    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.

  • User profile image
    Rossj

    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 Smiley

  • User profile image
    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.

  • User profile image
    stevef100

    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'

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.