Estimating SQL Server Table Sizes

Microsoft gives guidance for calculating the estimated sizes of your tables and indexes at  Performing such estimates is an important step in determining required server capacity.

Unfortunately, Microsoft’s guidance consists of long textual descriptions of the steps that need to be followed to perform the estimations.  It takes some time to interpret the instructions given by Microsoft and perform the calculations.

With that in mind, I put together a spreadsheet to help me perform the calculations.  To use the spreadsheet, I simply fill out the spreadsheet with the total number of rows in the table and details of the columns (data types and sizes) in the table and/or index.  In my experience, the estimates provided by the spreadsheet are within 5-10% of the actual sizes of the tables/indexes, which has been good enough for my needs.

You can download the spreadsheet here.

To complete the spreadsheet, simply enter the requested data into the cells with black borders.  For example, enter the expected number of rows in the table in the cell labeled “Rows in Table’”.  Enter the number of columns in the table in the cell labeled “# of Columns”.  Continue until you have specified all of the information about the table or index.  The spreadsheet uses the information to calculate the total space needed for the table or index.

To help you get started, the spreadsheet includes sample data for a table that contains 1699488 rows and has six columns.  This table has two integer columns (one of which is the primary key), two datetime columns, one tinyint column, and one nvarchar column.  The average size of the data in the nvarchar column is 12 characters.  Compare these table specifications to the sample data in the spreadsheet for an example of how to map the specifications for your table/index into the spreadsheet.

Note that while Microsoft provides guidance for estimating sizes of tables, clustered indexes, and non-clustered indexes, this spreadsheet only handles tables and clustered indexes.  I originally intended to add the calculations for non-clustered indexes, but simply did not find the time.  So, I am providing the spreadsheet as-is.  It should, at the very least, provide a good starting point for estimating table and index sizes.


8 Responses to Estimating SQL Server Table Sizes

  1. Mayrun Digmi says:

    Really helpful, Thank you!

  2. Neal Amin says:

    any chance you can turn the spreadsheet into an online form?

  3. Rajesh says:

    spreadsheet link does not work anymore. can you provide new link?

  4. Ganesh says:

    I created a table with one column of type numeric(18,0). It is allocating 2 data pages for 215 rows. As per the calculation given in Microsoft page, single page should accept at least 449 rows. I am not understanding why 2 pages getting allocated. I have pasted my query below. Please help me to understand.

    Create table Test(Num numeric(18,0))
    Insert into Test(Num)values(null)
    GO 215
    Exec sp_spaceused Test

    • mlichtenberg says:

      Without researching this thoroughly, I can only take a guess. It appears that when data is first added to a table, a page for the data and second page for the clustered index are immediately allocated. The spreadsheet I put together does not account for that initial index page, so at very low numbers of rows it will give inaccurate estimates.

      Run your same test again, but add 20000 rows to the table instead of just 215. The spreadsheet will estimate 45 pages allocated, and the actual allocation will be 47 pages for the data, 1 for the index, and 1 unused. That is within the 5-10% expected margin of error.

      By the way, here is another way to get the space usage statistics:

      SELECT t.NAME AS TableName,
      p.rows AS RowCount,
      SUM(a.total_pages) AS TotalPages,
      SUM(a.used_pages) AS UsedPages,
      (SUM(a.total_pages) – SUM(a.used_pages)) AS UnusedPages
      FROM sys.tables t
      INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
      INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
      INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
      WHERE t.NAME = ‘Test’
      GROUP BY t.Name, p.Rows

      Hope that helps!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: