SQL Server Performance

How can we do an estimate of the size of a new Database to be created ??

Discussion in 'SQL Server 2005 General DBA Questions' started by kasaranenikiran, Jan 28, 2011.

  1. kasaranenikiran New Member

    Hi,
    I have to create a new database .
    I need around 15 Tables to be created .
    2 of the Tables have around 80 columns .
    Rest of them have 5-10 columns .
    The total rows of all the Tables would be around 5,00,000 .

    My Approach :
    I checked the size of bytes on each column of the table and summed it up of the total row.
    And multiplied the number to total no of rows(approximate rows) .
    That would be the total size of the data on the Table . SO, the total of all the tables will give the number .
    I will calculate the index size and add it to the Data size. So, this will be the total estimate .
    Please correct me if I am wrong ...
    ANd
    Can anyone give me an easier approach to analyze (or) how to do an estimate of the Database ?? And how do we calculate the index size ??
    Regards
    Kay


    kasaranenikiran, Jan 28, 2011
    #2

  2. Luis Martin Moderator

    How that DB will be filled?.
    (I'm trying to find future growing).
    Luis Martin, Jan 28, 2011
    #3

  3. kasaranenikiran New Member

    The DB woun't grow beyond 500,000 rows including all the Table's ...
    Kay
    kasaranenikiran, Jan 31, 2011
    #4

  4. satya Moderator

    You can also get information from Books online that how to calculate size of clustered index: http://msdn.microsoft.com/en-us/library/ms178085.aspx and also MSDN blog refers this handy script:
    CREATE PROCEDURE [dbo].[IndexSize]
    @TableName NVARCHAR(256),
    @IndexName VARCHAR(256)
    AS
    BEGIN
    DECLARE @index_id INT
    DECLARE @index_size BIGINT SET @index_size = 0
    SELECT @index_id = index_id FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) AND name = @IndexName
    SELECT
    @index_size = @index_size + (avg_record_size_in_bytes * record_count)
    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')
    SELECT @index_size as IndexSizeBytes
    END
    satya, Feb 7, 2011
    #5

Share This Page