Estimating the table size...

  • Comments posted to this topic are about the item Estimating the table size...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • After cleaning up the text of the functions so it would create them, the function fn_GetTableSize only returns NULL.

    I changed the function so the calculations handle a NULL return.

    ALTER FUNCTION [dbo].[fn_GetTableSize]

    (

    @TableName VARCHAR(100)

    )

    RETURNS VARCHAR(25)

    AS BEGIN

    DECLARE @TableSize VARCHAR(25)

    IF EXISTS ( SELECT

    1

    FROM

    sys.indexes

    WHERE

    object_id = OBJECT_ID(@TableName)

    AND type = 1 )

    BEGIN

    SELECT

    @TableSize = ISNULL(dbo.fn_ClusteredIndexSize(@TableName), 0)

    + ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName), 0)

    END

    ELSE

    BEGIN

    SELECT

    @TableSize = (ISNULL(dbo.fn_CalculateHeapSize(@TableName), 0)

    + ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName),

    0))

    END

    SET @TableSize = LTRIM(STR(@TableSize)) + ' KB'

    RETURN @TableSize

    END

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • do you mean that it is not working....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • The uploaded text is full of gargage characters that will prevent a quick copy and paste from compiling.

    Any way to clean up the script.

    -- Mark D Powell --

  • well... I just scripted the function thru ssms copied them to the editor... I will try to remove the characters.....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • For some reason I'm getting loads of "Incorrect syntax" errors while trying to compile this

    edit:

    what version of SQL were you using to write this?

    --
    Thiago Dantas
    @DantHimself

  • dant12 (7/2/2009)


    For some reason I'm getting loads of "Incorrect syntax" errors while trying to compile this

    edit:

    what version of SQL were you using to write this?

    u r getting the syntax errors because the characters added by the editor.....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • I have attached the updated script...once published it will be available for viewing....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • ah, yes.

    pasted it in notepad and got to see it

    quick replace fixed it

    --
    Thiago Dantas
    @DantHimself

  • Here is a cleaned up set of code.

    CREATE FUNCTION [dbo].[fn_CalculateHeapSize]

    (

    @Tablename VARCHAR(100)

    )

    RETURNS VARCHAR(100)

    AS BEGIN

    -- Calculate the space used taken at leaf level

    DECLARE

    @Num_Rows FLOAT

    ,@Num_Cols INT

    ,@Fixed_data_size INT

    ,@Num_var_Cols INT

    ,@Max_var_size INT

    ,@Null_Bitmap INT

    ,@Variable_Data_Size INT

    ,@Heap_size BIGINT

    ,@Row_Size INT

    ,@Rows_per_page FLOAT

    ,@Num_Pages FLOAT

    SET @Num_Rows = (

    SELECT

    [rows]

    FROM

    sys.sysindexes

    WHERE

    indid = 1

    AND id = OBJECT_ID(@Tablename)

    )

    SET @Num_Cols = (

    SELECT

    COUNT(*)

    FROM

    sys.columns

    WHERE

    object_id = OBJECT_ID(@Tablename)

    )

    SET @Fixed_data_size = (

    SELECT

    SUM(max_lenGth)

    FROM

    sys.columns

    WHERE

    object_id = OBJECT_ID(@Tablename)

    AND system_type_id NOT IN (165, 167, 231, 34,

    35, 99)

    )

    SET @Num_var_Cols = (

    SELECT

    COUNT(*)

    FROM

    sys.columns

    WHERE

    object_id = OBJECT_ID(@Tablename)

    AND system_type_id IN (165, 167, 231, 34, 35, 99)

    )

    SET @Max_var_size = (

    SELECT

    SUM(max_lenGth)

    FROM

    sys.columns

    WHERE

    object_id = OBJECT_ID(@Tablename)

    AND system_type_id IN (165, 167, 231, 34, 35, 99)

    )

    SET @Null_Bitmap = 2 + (@Num_Cols + 7) / 8

    IF (@Num_var_Cols = 0)

    BEGIN

    SET @Variable_Data_Size = 0

    END

    ELSE

    BEGIN

    SET @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size

    END

    SET @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info

    SET @Rows_per_page = 8096 / (@Row_Size + 2)

    -- No. of pages needed to store rows

    SET @Num_Pages = CEILING(@Num_Rows / @Rows_per_page)

    SET @Heap_size = (8192 * @Num_Pages) / 1024

    -- Space used to store index info

    RETURN LTRIM(STR(@Heap_size))-- + ' KB'

    END

    GO

    CREATE FUNCTION [dbo].[fn_ClusteredIndexSize]

    (

    @Tablename VARCHAR(100)

    )

    RETURNS BIGINT

    AS BEGIN

    -- Calculate the space used taken at leaf level

    DECLARE

    @Num_Rows FLOAT

    ,@Num_Cols INT

    ,@Fixed_data_size INT

    ,@Num_var_Cols INT

    ,@Max_var_size INT

    ,@fill_factor INT

    ,@uniquifier SMALLINT

    ,@uniquefiersize SMALLINT

    ,@Null_Bitmap INT

    ,@Variable_Data_Size INT

    ,@Total_Space VARCHAR(100)

    ,@Row_Size INT

    ,@Rows_per_page FLOAT

    ,@Free_rows_per_page FLOAT

    ,@level FLOAT

    ,@Num_Pages FLOAT

    ,@Leaf_level_space INT

    SET @uniquifier = 1

    SET @uniquefiersize = 4

    SET @Num_Rows = (

    SELECT

    [rows]

    FROM

    sys.sysindexes

    WHERE

    indid = 1

    AND id = OBJECT_ID(@Tablename)

    )

    SET @Num_Cols = (

    SELECT

    COUNT(*)

    FROM

    sys.columns

    WHERE

    object_id = OBJECT_ID(@Tablename)

    )

    SET @Fixed_data_size = (

    SELECT

    SUM(max_lenGth)

    FROM

    sys.columns

    WHERE

    object_id = OBJECT_ID(@Tablename)

    AND system_type_id NOT IN (165, 167, 231, 34,

    35, 99)

    )

    SET @Num_var_Cols = (

    SELECT

    COUNT(*)

    FROM

    sys.columns

    WHERE

    object_id = OBJECT_ID(@Tablename)

    AND system_type_id IN (165, 167, 231, 34, 35, 99)

    )

    SET @Max_var_size = (

    SELECT

    SUM(max_lenGth)

    FROM

    sys.columns

    WHERE

    object_id = OBJECT_ID(@Tablename)

    AND system_type_id IN (165, 167, 231, 34, 35, 99)

    )

    IF ((

    SELECT

    is_unique

    FROM

    sys.indexes

    WHERE

    type = 1

    AND object_id = OBJECT_ID(@Tablename)

    ) = 0)

    BEGIN

    SET @Num_Cols = @Num_Cols + @uniquifier

    SET @Num_var_Cols = @Num_var_Cols + @uniquifier

    SET @Max_var_size = @Max_var_size + @uniquefiersize

    END

    SET @Null_Bitmap = 2 + (@Num_Cols + 7) / 8

    SET @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size

    SET @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info

    SET @Rows_per_page = 8096 / (@Row_Size + 2)

    SET @fill_factor = (

    SELECT

    fill_factor

    FROM

    sys.indexes

    WHERE

    object_id = OBJECT_ID(@Tablename)

    AND type = 1

    )

    -- No. of reserved free rows per page

    SET @Free_rows_per_page = 8096 * (((100 - @Fill_Factor) / 100)

    / (@Row_Size + 2))

    -- No. of pages needed to store rows

    SET @Num_Pages = CEILING((@Num_Rows / (@Rows_per_page

    - @Free_rows_per_page)))

    SET @Leaf_level_space = 8192 * @Num_Pages

    -- Space used to store index info

    DECLARE

    @Num_Key_cols INT

    ,@Fixed_key_size INT

    ,@Num_var_key_cols INT

    ,@Max_var_key_size INT

    ,@Index_Null_Bitmap INT

    ,@Variable_Key_size INT

    ,@Index_row_size INT

    ,@Index_row_per_page FLOAT

    ,@levels INT

    ,@Num_Index_pages INT

    ,@Index_level_space INT

    ,@Null_Cols INT

    SET @Num_Key_cols = (

    SELECT

    Keycnt

    FROM

    sys.sysindexes

    WHERE

    id = OBJECT_ID(@Tablename)

    AND indid = 1

    )

    SET @Fixed_key_size = (

    SELECT

    SUM(max_length)

    FROM

    sys.index_columns a

    ,sys.indexes b

    ,sys.columns c

    WHERE

    a.index_id = b.index_id

    AND b.object_id = OBJECT_ID(@Tablename)

    AND type = 1

    AND a.object_id = b.object_id

    AND c.object_id = b.object_id

    AND a.column_id = c.column_id

    AND system_type_id NOT IN (165, 167, 231, 34, 35,

    99)

    )

    SET @Num_var_key_cols = (

    SELECT

    COUNT(c.name)

    FROM

    sys.index_columns a

    ,sys.indexes b

    ,sys.columns c

    WHERE

    a.index_id = b.index_id

    AND b.object_id = OBJECT_ID(@Tablename)

    AND type = 1

    AND a.object_id = b.object_id

    AND c.object_id = b.object_id

    AND a.column_id = c.column_id

    AND system_type_id IN (165, 167, 231, 34, 35,

    99)

    )

    SET @Max_var_key_size = (

    SELECT

    ISNULL(SUM(max_length), 0)

    FROM

    sys.index_columns a

    ,sys.indexes b

    ,sys.columns c

    WHERE

    a.index_id = b.index_id

    AND b.object_id = OBJECT_ID(@Tablename)

    AND type = 1

    AND a.object_id = b.object_id

    AND c.object_id = b.object_id

    AND a.column_id = c.column_id

    AND system_type_id IN (165, 167, 231, 34, 35,

    99)

    )

    IF ((

    SELECT

    is_unique

    FROM

    sys.indexes

    WHERE

    type = 1

    AND object_id = OBJECT_ID(@Tablename)

    ) = 0)

    BEGIN

    SET @Num_Key_cols = @Num_Key_cols + @uniquifier

    SET @Num_var_key_cols = @Num_var_key_cols + @uniquifier

    SET @Max_var_key_size = @Max_var_key_size + @uniquefiersize

    END

    SET @Null_Cols = (

    SELECT

    ISNULL(COUNT(c.name), 0)

    FROM

    sys.index_columns a

    ,sys.indexes b

    ,sys.columns c

    WHERE

    a.index_id = b.index_id

    AND b.object_id = OBJECT_ID(@Tablename)

    AND type = 1

    AND a.object_id = b.object_id

    AND c.object_id = b.object_id

    AND a.column_id = c.column_id

    AND c.is_nullable = 1

    )

    SELECT

    @Index_level_space = dbo.fn_getIndexSpace(@Null_Cols,

    @Num_var_key_cols,

    @Max_var_key_size,

    @Fixed_key_size, @Num_Rows)

    SET @Total_space = LTRIM(STR((@Index_level_space + @Leaf_level_space)

    / (1024)))

    RETURN @Total_space

    END

    GO

    CREATE FUNCTION [dbo].[fn_getIndexSpace]

    (

    @Num_Null_key_cols INT

    ,@Num_var_key_cols INT

    ,@Max_var_key_size INT

    ,@Fixed_key_size INT

    ,@Num_Rows FLOAT

    )

    RETURNS BIGINT

    AS BEGIN

    DECLARE

    @Index_Null_Bitmap INT

    ,@Var_Key_Size INT

    ,@Index_row_Size INT

    ,@Index_Row_per_Page FLOAT

    ,@level FLOAT

    ,@Num_Index_pages INT

    ,@Index_Space_Used BIGINT

    IF (@Num_Null_key_cols > 0)

    BEGIN

    SET @Index_Null_Bitmap = 2 + ((@Num_Null_key_cols + 7) / 8)

    END

    ELSE

    BEGIN

    SET @Index_Null_Bitmap = 0

    END

    IF (@Num_var_key_cols > 0)

    BEGIN

    SET @Var_Key_Size = 2 + (@Num_var_key_cols * 2)

    + @Max_var_key_size

    END

    ELSE

    BEGIN

    SET @Var_Key_Size = 0

    END

    SET @Index_row_Size = @Fixed_key_size + @Var_Key_Size + @Index_Null_Bitmap

    + 1 + 6

    SET @Index_Row_per_Page = 8096 / (@Index_row_Size + 2)

    SET @level = 1 + FLOOR(ABS((LOG10(@Num_Rows / @Index_row_per_page)

    / LOG10(@Index_row_per_page))))

    SET @Num_Index_pages = 0

    DECLARE @i INT

    IF (@level > 0)

    BEGIN

    SET @i = 1

    WHILE(@i 0)

    BEGIN

    SET @Variable_leaf_size = 2 + (@Num_Var_leaf_Cols * 2)

    + @Max_var_leaf_size

    END

    ELSE

    BEGIN

    SET @Variable_leaf_size = 0

    END

    SET @Leaf_Row_Size = @Fixed_Leaf_Size + @Variable_leaf_size

    + @Leaf_Null_Bitmap + 1 + 6

    SET @Leaf_Rows_per_page = 8096 / (@Leaf_Row_Size + 2)

    SET @Free_Rows_Per_Page = 8096 * (((100 - @Fill_Factor) / 100)

    / (@Leaf_Row_Size + 2))

    SET @Num_Leaf_Pages = CEILING((@Num_Rows / (@Leaf_Rows_per_page

    - @Free_Rows_Per_Page)))

    SET @Leaf_Space_Used = 8192 * @Num_Leaf_Pages

    RETURN @Leaf_Space_Used

    END

    GO

    CREATE FUNCTION [dbo].[fn_GetNonClusteredIndexSize]

    (

    @TableName VARCHAR(100)

    )

    RETURNS BIGINT

    AS BEGIN

    DECLARE

    @Num_Rows FLOAT

    ,@Num_Key_cols INT

    ,@Fixed_key_size INT

    ,@Num_var_key_cols INT

    ,@Max_var_key_size INT

    ,@is_clustered INT

    ,@index_id INT

    ,@is_unique BIT

    ,@Num_Diff_cols INT

    ,@Num_Null_key_cols INT

    ,@Num_Index_pages INT

    ,@Index_Space_Used INT

    ,@Total_Index_space BIGINT

    ,@Num_Leaf_Cols INT

    ,@Num_Included_Cols INT

    ,@Leaf_Level_Space INT

    ,@Fill_Factor INT

    -- CALCULATE THE SPACE USED TO SAVE INDEX INFORMATION AT NON-LEAF LEVEL

    -- No of Rows in a table

    SET @Total_Index_space = 0

    SET @Leaf_Level_Space = 0

    -- insert info intom temp table

    SET @Num_Rows = (

    SELECT

    [rows]

    FROM

    sys.sysindexes

    WHERE

    indid = 1

    AND id = OBJECT_ID(@TableName)

    )

    DECLARE @Tmp_Info TABLE

    (

    Index_id INT

    ,Num_key_cols INT

    ,type INT

    ,is_unique BIT

    ,is_included SMALLINT

    ,fill_factor INT

    ,Num_Var_Key_cols INT

    ,Fixed_Key_Size INT

    ,Max_Var_Key_Size INT

    )

    DECLARE @tmp_Index_info TABLE

    (

    sno INT IDENTITY(1, 1)

    ,index_id INT

    ,Num_key_cols INT

    ,type INT

    ,is_unique BIT

    ,Num_Var_Key_cols INT

    ,Fixed_Key_Size INT

    ,Max_Var_Key_Size INT

    ,Num_Included_Col INT

    ,fill_factor INT

    )

    INSERT INTO

    @Tmp_Info

    SELECT

    b.Index_id

    ,COUNT(c.name) Num_key_cols

    ,b.type

    ,b.is_unique

    ,is_included_column

    ,fill_factor

    ,ISNULL((

    SELECT

    COUNT(c.name)

    FROM

    sys.columns e

    WHERE

    e.object_id = c.object_id

    AND a.column_id = e.column_id

    AND c.system_type_id IN (165, 167, 231, 34, 35, 99)

    ), 0) AS Num_Var_Key_cols

    ,ISNULL((

    SELECT

    SUM(max_length)

    FROM

    sys.indexes d

    WHERE

    d.index_id = b.index_id

    AND d.object_id = c.object_id

    AND c.system_type_id NOT IN (165, 167, 231, 34, 35, 99)

    ), 0) AS Fixed_Key_Size

    ,ISNULL((

    SELECT

    SUM(max_length)

    FROM

    sys.indexes d

    WHERE

    d.index_id = b.index_id

    AND d.object_id = c.object_id

    AND c.system_type_id IN (165, 167, 231, 34, 35, 99)

    ), 0) AS Max_Var_Key_Size

    --into @Tmp_Info

    FROM

    sys.index_columns a

    ,sys.columns c

    ,sys.indexes b

    WHERE

    a.column_id = c.column_id

    AND a.index_id = b.index_id

    AND b.object_id = c.object_id

    AND a.object_id = b.OBJECT_ID

    AND b.object_id = OBJECT_ID(@TableName) --and b.type>1

    GROUP BY

    c.name

    ,b.index_id

    ,c.object_id

    ,c.system_type_id

    ,a.column_id

    ,b.type

    ,b.is_unique

    ,is_included_column

    ,fill_factor

    ORDER BY

    b.index_id

    INSERT INTO

    @tmp_Index_info

    SELECT

    index_id AS Index_id

    ,SUM(num_key_cols) AS num_key_cols

    ,type

    ,is_unique

    ,SUM(Num_var_key_cols) AS Num_var_key_cols

    ,SUM(Fixed_key_size) AS Fixed_key_size

    ,SUM(max_var_key_size) AS max_var_key_size

    ,SUM(is_included)

    ,fill_factor

    --into @tmp_Index_info

    FROM

    @Tmp_Info

    WHERE

    type > 1

    GROUP BY

    index_id

    ,type

    ,is_unique

    ,fill_factor

    IF EXISTS ( SELECT

    1

    FROM

    @Tmp_Info

    WHERE

    type = 1 )

    BEGIN

    SET @is_clustered = 1

    END

    ELSE

    BEGIN

    SET @is_clustered = 0

    END

    DECLARE @Row_Count INT

    SET @Row_Count = (

    SELECT

    COUNT(*)

    FROM

    @tmp_Index_info

    WHERE

    type > 1

    )

    WHILE (@row_Count > 0)

    BEGIN

    SELECT

    @index_id = index_id

    ,@Num_Key_cols = num_key_cols

    ,@Fixed_key_size = fixed_key_size

    ,@Num_var_key_cols = Num_var_key_cols

    ,@Max_var_key_size = Max_var_key_size

    ,@is_unique = is_unique

    ,@Num_Included_Cols = Num_Included_Col

    ,@Fill_Factor = fill_factor

    FROM

    @tmp_Index_info

    WHERE

    sno = @Row_Count

    IF (@is_clustered = 0)

    BEGIN

    SET @Num_Key_cols = @Num_Key_cols + 1

    SET @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols

    + 1

    END

    ELSE

    BEGIN

    SELECT

    @Num_Diff_cols = COUNT(column_id)

    FROM

    sys.index_columns x

    ,sys.indexes y

    WHERE

    column_id NOT IN (

    SELECT

    column_id

    FROM

    sys.index_columns a

    ,sys.indexes b

    WHERE

    a.index_id = b.index_id

    AND type > 1

    AND a.object_id = b.object_id

    AND a.object_id = OBJECT_ID(@TableName)

    AND a.index_id = @index_id)

    AND x.object_id = y.object_id

    AND y.type = 1

    AND x.object_id = OBJECT_ID(@TableName)

    AND x.index_id = y.index_id

    SET @Num_Key_cols = @Num_Key_cols + @Num_Diff_cols

    + @is_unique

    SET @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols

    + @Num_Diff_cols + @is_unique

    END

    SELECT

    @Num_Null_key_cols = ISNULL(COUNT(x.column_id), 0)

    FROM

    sys.index_columns x

    ,sys.columns y

    WHERE

    x.column_id = y.column_id

    AND x.index_id = @index_id

    AND y.is_nullable = 1

    AND x.object_id = OBJECT_ID(@TableName)

    AND x.object_id = y.object_id

    DECLARE @index_name VARCHAR(100)

    SELECT

    @index_name = name

    FROM

    sys.indexes

    WHERE

    object_id = OBJECT_ID(@TableName)

    AND index_id = @index_id

    SELECT

    @Index_Space_Used = dbo.fn_getIndexSpace(@Num_Null_key_cols,

    @Num_var_key_cols,

    @Max_var_key_size,

    @Fixed_key_size,

    @Num_Rows)

    SELECT

    @Leaf_Level_Space = dbo.fn_GetLeafLevelIndexSpace(@Num_Leaf_Cols, @Num_var_key_cols, @Max_var_key_size, @Fixed_key_size, @Fill_Factor, @Num_Rows)

    SET @Total_Index_space = @Total_Index_space + @Index_Space_Used

    + @Leaf_Level_Space

    SET @Row_Count = @Row_Count - 1

    END

    RETURN LTRIM(STR((@Total_Index_space)) / (1024))

    END

    GO

    CREATE FUNCTION [dbo].[fn_GetTableSize]

    (

    @TableName VARCHAR(100)

    )

    RETURNS VARCHAR(25)

    AS BEGIN

    DECLARE @TableSize VARCHAR(25)

    IF EXISTS ( SELECT

    1

    FROM

    sys.indexes

    WHERE

    object_id = OBJECT_ID(@TableName)

    AND type = 1 )

    BEGIN

    SELECT

    @TableSize = dbo.fn_ClusteredIndexSize(@TableName)

    + dbo.fn_GetNonClusteredIndexSize(@TableName)

    END

    ELSE

    BEGIN

    SELECT

    @TableSize = (ISNULL(dbo.fn_CalculateHeapSize(@TableName), 0)

    + ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName),

    0))

    END

    SET @TableSize = LTRIM(STR(@TableSize)) + ' KB'

    RETURN @TableSize

    END

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • according to your script one of my tables has 440 MBs but SSMS says it has around 46 MBs

    u sure u did the math right?

    --
    Thiago Dantas
    @DantHimself

  • thanks william...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • dant12 (7/2/2009)


    according to your script one of my tables has 440 MBs but SSMS says it has around 46 MBs

    u sure u did the math right?

    I tested the script against the 7 tables of adventureworks database...5 tables gave me result with a margin +1/-1..

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Wouldn't it be incorrect to compare this script to an existing table, since the script is meant to estimate a table's size

    Sqlfrenzy (7/2/2009)


    dant12 (7/2/2009)


    according to your script one of my tables has 440 MBs but SSMS says it has around 46 MBs

    u sure u did the math right?

    I tested the script against the 7 tables of adventureworks database...5 tables gave me result with a margin +1/-1..

  • I like this, but Richard Ding's creation "sp_sos" is a little more thorough, and is a PROC instead of a group of Functions.

    http://media.techtarget.com/searchSQLServer/downloads/URL_for_SQL_Script_download_Tip_on_Sp_SOS.doc

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply