# Estimating SQL Server Table Sizes

October 16, 2013 8 Comments

Microsoft gives guidance for calculating the estimated sizes of your tables and indexes at http://msdn.microsoft.com/en-us/library/ms175991.aspx. 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.

Reblogged this on Says My Mind.

Really helpful, Thank you!

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

I considered that initially, but decided to leave it as an exercise for the reader. 🙂

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

Thanks for letting me know. I have updated the original link. Let me know if you still have problems accessing the spreadsheet.

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

GO

Insert into Test(Num)values(null)

GO 215

Exec sp_spaceused Test

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!