8

I am trying to estimate the space requirements for a central database server that will be collecting data from about 200 identical field databases. I have the average daily row count for each table and now need to estimate the row size, including indexes, for each table.

Is there such an animal in existence or do I need to roll my own? If I do need to roll my own, can you suggest a good approach?

TIA

  • Some info here: sqlskills.com/blogs/paul/… – Aaron Bertrand Jun 3 '14 at 15:15
  • If you take the size of a table in one of the largest field databases and divide it by the number of rows, you should get a decent estimate of the likely size per row of the final database. Storage is very close to linear with row count. Perhaps I'm missing something - do you need a more exact number? – Jon of All Trades Jun 3 '14 at 15:35
  • The daily average number of new rows is 85,000,000. I'd like to get as close to actual as possible. – Metaphor Jun 3 '14 at 15:44
9

Using Aaron's link for even finer adaption of my process, I would recommend a function and a view/query to report out size per row per table, including indexes.

create function dbo.getColumnSize (@typeName SYSNAME, @max_length INT, @precision INT)
RETURNS INT
AS
BEGIN
    RETURN (SELECT CASE @typeName
        WHEN 'tinyint'          THEN 1
        WHEN 'smallint'         THEN 2
        WHEN 'int'              THEN 4
        WHEN 'bigint'           THEN 8
        WHEN 'numeric'          THEN ((@precision - 1)/2) + 1
        WHEN 'decimal'          THEN ((@precision - 1)/2) + 1
        WHEN 'real'             THEN 4
        WHEN 'float'            THEN CASE WHEN @precision <=24 THEN 4 ELSE 8 END
        WHEN 'money'            THEN 8
        WHEN 'smallmoney'       THEN 4
        WHEN 'time'             THEN 5
        WHEN 'timestamp'        THEN 5
        WHEN 'date'             THEN 3
        WHEN 'smalldatetime'    THEN 4
        WHEN 'datetime'         THEN 8
        WHEN 'datetime2'        THEN 8
        WHEN 'datetimeoffset'   THEN 10
        WHEN 'char'             THEN @max_length
        WHEN 'varchar'          THEN @max_length + 2
        WHEN 'nchar'            THEN @max_length
        WHEN 'nvarchar'         THEN @max_length + 2
        WHEN 'binary'           THEN @max_length
        WHEN 'varbinary'        THEN @max_length + 2
        WHEN 'bit'              THEN 0.125
    END)
END

select SchemaName, ObjectName, SUM(CEILING(Bytes))+4+2+2+2+(ceiling(count(distinct columnname)*1.0/8.0)) As RowSize--, FLOOR((POWER(2,30)+(POWER(2,30)-1))*1.0/SUM(CEILING(Bytes))/30/2/60/24)
from (
    select s.name as SchemaName, o.name AS ObjectName, c.name as ColumnName, t.name as TypeName
        , dbo.getColumnSize(t.name,c.max_length, c.precision) AS Bytes
    from sys.objects o
        inner join sys.schemas s on s.schema_id=o.schema_id
        inner join sys.columns c on o.object_id=c.object_id
        inner join sys.types t on c.system_type_id=t.system_type_id
            and t.user_type_id=c.user_type_id
    where o.type='U'
        AND RIGHT(t.name,4) != 'text'
    UNION ALL
    select s.name as SchemaName, o.name AS ObjectName, c.name as ColumnName, t.name as TypeName
        , dbo.getColumnSize(t.name,c.max_length, c.precision) AS Bytes
    from sys.objects o
        inner join sys.schemas s on s.schema_id=o.schema_id
        inner join sys.indexes i on i.object_id = o.object_id
        inner join sys.index_columns ic on ic.object_id = o.object_id
            and i.index_id = ic.index_id
        inner join sys.columns c on o.object_id=c.object_id
            and ic.column_id = c.column_id
        inner join sys.types t on c.system_type_id=t.system_type_id
            and t.user_type_id=c.user_type_id
    where o.type='U'
        AND RIGHT(t.name,4) != 'text'
    ) Z
group by SchemaName, ObjectName
  • shouldn't the size of nchar and nvarchar be 2x the size of their length? – Stefan Sieber Sep 22 '17 at 15:20
  • 1
    StefanSieber, You would think so, and I thought so too, but in testing I found that SQL Server's sys.columns view reports max_length in bytes, not characters. so nvarchar(1) will report max_length of 2. – Jaaz Cole Sep 22 '17 at 15:43
  • like the elegance of the answer and I will likely use it... with one minor modification when 'bit' returns a non-integer. so either round down or round up depending on the application of the resulting data. – yeOldeDataSmythe May 22 '19 at 13:40
9

Script selected as an asnwer has some drawbacks:

  1. Does not take into account some SQL Server datatypes:

    image, text, uniqueidentifier, sql_variant, ntext, hierarchyid, geometry, geography, xml, sysname

and (max) data structures structures such as varchar(max) to name a few.

  1. Takes into account computed columns which should not affect record's size.
  2. Does not take into account the fact table has NULLable columns.
  3. Does not take into account the fact that some datatypes have different size depending on specified scale (for example time(0) and time(7) should have different column length)

The following script shows the size of the record as it is on a data page. It shows maximum size that may be taken by the record, minimum size (in case one all values in the record are NULL if permitted by table structure). It shows table type (CLUSTERED/NONCLUSTERED), total number of data columns and schema where table belongs.

Note this script does not take into account indexes.

Update: Script counts each bit column as 1/8th of a byte. Minimum and maximum sizes are adjusted to ensure that records cannot be smaller than the forwarding stub size.

  SELECT 
     -- record cannot be smaller than the forwarding stub size =9 Bytes
     CASE WHEN [Max Size]>=9
     THEN [Max Size]
     ELSE 9 
     END AS [Max Size]
     -- record cannot be smaller than the forwarding stub size =9 Bytes
    , CASE WHEN [Min Size]>=9
     THEN [Min Size]
     ELSE 9 
     END AS [Min Size]
    , [Table Name]
    , [Table Type]
    , [Total Number of Columns]
    , [Schema]
    FROM
    (
SELECT 
    DISTINCT 
    -- Overhead for row header of a data row
    4
    +
    -- Overhead for NULL bitmap
    2+cast(([Total Number of Columns]+7)/8 AS BIGINT)+
    -- overhead for variable length
    CASE WHEN [IsVariableLength]>0
    THEN
    2
    ELSE
    0
    END
    +  
    --- Sum is on record level
    SUM(
    a1.[max_length]
    +
    -- Overhead for variable-length columns
    CASE WHEN 
    -- varchar
    [System Type]='varchar'
    --(([system_type_id]=167) AND ([user_type_id]=167))
    OR
    -- nvarchar 
    [System Type]='nvarchar'
    --(([system_type_id]=231) AND ([user_type_id]=231))
    OR
    -- IMAGE
    (([system_type_id]=34) OR ([user_type_id]=34))
    OR
    -- TEXT
    (([system_type_id]=35) OR ([user_type_id]=35))
    OR 
    --  NTEXT
    (([system_type_id]=99) OR ([user_type_id]=99))
    OR 
    --  SQLVARIANT
    (([system_type_id]=98) OR ([user_type_id]=98))
    OR
    -- hierarchyid geometry geography
    (([system_type_id]=240))
    THEN 2
    ELSE 0
    END
    )
    OVER (PARTITION BY a1.[Schema], a1.[Table Name]) AS [Max Size]

    , -- Overhead for row header of a data row
    4
    +
    -- Overhead for NULL bitmap
    2+cast(([Total Number of Columns]+7)/8 AS BIGINT)+
    -- overhead for variable length
    CASE WHEN ([IsVariableLength]>0) AND ([AnyFixedColumn]=0)
    THEN
    2
    ELSE
    0
    END
    +
    --- Sum is on record level
    SUM(
    -- overhead for variable length depending on number of variable columns
    CASE WHEN 
    -- varchar
    --[System Type]='varchar'
    (([system_type_id]=167) OR ([user_type_id]=167))
    OR
    -- nvarchar 
    --[System Type]='nvarchar'
    (([system_type_id]=231) OR ([user_type_id]=231))
    OR
    -- IMAGE
    (([system_type_id]=34) OR ([user_type_id]=34))
    OR
    -- TEXT
    (([system_type_id]=35) OR ([user_type_id]=35))
    OR 
    --  NTEXT
    (([system_type_id]=99) OR ([user_type_id]=99))
    --  VARBINARY
    OR
    (([system_type_id]=165) OR ([user_type_id]=165))
    OR 
    --  SQLVARIANT
    (([system_type_id]=98) OR ([user_type_id]=98))
    OR
    -- hierarchyid geometry geography
    (([system_type_id]=240))
    OR
    -- xml
    (([system_type_id]=241))
    THEN
        CASE WHEN [Is Nullable]=1
        THEN 0 
        ELSE 
        1
        END
    ELSE
        CASE
        WHEN
        -- bit
        (([system_type_id]=104) OR ([user_type_id]=104))
        and [Is Nullable]=1
        THEN 0
        ELSE
        a1.[max_length]
        END
    END
    -- 

    )
    OVER (PARTITION BY a1.[Schema], a1.[Table Name]) AS [Min Size]
    , a1.[Table Name]
    , [Table Type]
    , [Total Number of Columns]
    , a1.[Schema]
    FROM
    -- Start a1
    (   SELECT
        (SELECT [name] FROM [sys].[schemas]
        WHERE [sys].[schemas].[schema_id]=[sys].[objects].[schema_id])
        AS [Schema]
        , [sys].[objects].[name] AS [Table Name]
        , [sys].[all_columns].[name] AS [Column Name]
        , [sys].[all_columns].[system_type_id]
        , (
            SELECT name FROM [sys].[types]
            WHERE [sys].[types].[system_type_id]=[sys].[all_columns].[system_type_id]
            AND
                    [sys].[types].[user_type_id]=[sys].[all_columns].[user_type_id]
            ) AS [System Type]
        , [sys].[all_columns].[user_type_id]
        , 
        CASE 
        WHEN 
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        THEN 2147483647
        -- TEXT
        WHEN (([system_type_id]=35) OR ([user_type_id]=35))
        THEN 2147483647
        --  NTEXT
        WHEN (([system_type_id]=99) OR ([user_type_id]=99))
        THEN 1073741823
        -- varchar(max)
        WHEN (([system_type_id]=167) OR ([user_type_id]=167)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 2147483647
        -- nvarchar(max) 
        WHEN (([system_type_id]=231) OR ([user_type_id]=231)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 1073741823
        -- varbinary(max)
        WHEN (([system_type_id]=165) OR ([user_type_id]=165)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 2147483647
        -- hierarchyid geometry geography
        WHEN (([system_type_id]=240))
        THEN 2147483647
        -- xml
        WHEN (([system_type_id]=241) AND ([sys].[all_columns].[max_length]=-1))
        THEN 2147483647
        -- bit
        WHEN (([system_type_id]=104) OR ([user_type_id]=104))
        THEN 1/8    
        ELSE 
        CAST([sys].[all_columns].[max_length] AS BIGINT)
        END [max_length]
        , [sys].[all_columns].[is_nullable] AS [Is Nullable]
        , 
        CASE 
        WHEN EXISTS 
            (   
                SELECT type_desc FROM sys.indexes
                WHERE type_desc='CLUSTERED'
                AND [sys].[objects].[object_id]=[sys].[indexes].[object_id]
            )
        THEN 'CLUSTERED'
        ELSE 'HEAP'
        END AS [Table Type]
        , COUNT([sys].[all_columns].[name]) OVER (PARTITION BY [sys].[objects].[object_id]) AS [Total Number of Columns]
        ,SUM (CASE WHEN 
        -- varchar
        (
        (([system_type_id]=167) AND ([user_type_id]=167))
        OR
        -- nvarchar 
        (([system_type_id]=231) AND ([user_type_id]=231))
        )
        AND [sys].[all_columns].[is_nullable]=0
        THEN 1
        ELSE 0
        END) OVER (PARTITION BY [sys].[objects].[name]) AS [IsNonNullableVariableLength]
        ,SUM (
        CASE WHEN 
        -- varchar
        (([system_type_id]=167) OR ([user_type_id]=167))
        OR
        -- nvarchar 
        (([system_type_id]=231) OR ([user_type_id]=231))
        OR
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        OR
        -- TEXT
        (([system_type_id]=35) OR ([user_type_id]=35))
        OR 
        --  NTEXT
        (([system_type_id]=99) OR ([user_type_id]=99))
        --  VARBINARY
        OR
        (([system_type_id]=165) OR ([user_type_id]=165))
        OR 
        --  SQLVARIANT
        (([system_type_id]=98) OR ([user_type_id]=98))
        OR
        -- hierarchyid geometry geography
        (([system_type_id]=240))        
        OR
        -- xml
        (([system_type_id]=241))    
        THEN 1
        ELSE 0
        END) OVER (PARTITION BY [sys].[objects].[name]) 
        AS [IsVariableLength]
        ,SUM (
        CASE WHEN 
        -- varchar
        (([system_type_id]=167) OR ([user_type_id]=167))
        OR
        -- nvarchar 
        (([system_type_id]=231) OR ([user_type_id]=231))
        OR
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        OR
        -- TEXT
        (([system_type_id]=35) OR ([user_type_id]=35))
        OR 
        --  NTEXT
        (([system_type_id]=99) OR ([user_type_id]=99))
        --  VARBINARY
        OR
        (([system_type_id]=165) OR ([user_type_id]=165))
        OR 
        --  SQLVARIANT
        (([system_type_id]=98) OR ([user_type_id]=98))
        OR
        -- hierarchyid geometry geography
        (([system_type_id]=240))    
        OR
        -- xml
        (([system_type_id]=241))    
        THEN 0
        ELSE 1
        END) OVER (PARTITION BY [sys].[objects].[name]) 
        AS [AnyFixedColumn]
    FROM [sys].[objects]
    INNER JOIN sys.all_columns
    ON [sys].[objects].[object_id]=[sys].[all_columns].[object_id]
    WHERE type_desc='USER_TABLE'
    ) a1
    ) a2
  • Nice. I think you should do a CEILING() around both SUM(...) AS [Max Size] and SUM(...) AS [Min Size] to account for fractional BIT values (when number of BIT fields is not evenly divisible by 8) as a single BIT field still takes up 1 byte. Also, any thoughts on accounting for: SPARSE, vardecimal, and compression? I realize those would be fairly complicated to work in, but am curious if approximations could be used at least in the case of SPARSE, maybe also vardecimal (is deprecated but so is TEXT, IMAGE, etc :) – Solomon Rutzky Dec 15 '14 at 18:28
  • @srutzky Good point, I will fix BIT part and refresh the script. This script is used as a part of a bigger script (stored procedure) that estimates the size of the tables in the database. vardecimal is not a type as far as I remember. But I am working on other enhancements. – Pavel Nefyodov Dec 15 '14 at 20:06
  • I wasn't saying that VARDECIMAL is a datatype. I was saying that it, along with the SPARSE option and COMPRESSION, affect the physical storage size of many of the datatypes that you are correctly accounting for. Meaning, a BIGINT field marked as SPARSE NULL will be either 0 bytes or 12 bytes, not "always 8 bytes". So it would require some estimation of number of rows and how many of those would be NULL vs NOT NULL. Maybe continuing to assume "always 8" is a good enough estimate. – Solomon Rutzky Dec 15 '14 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.