I've often been asked the question
'If we add a new table to
our database, will we have enough storage?' and to be honest the
answers I have given to date have been random at best.
I've recently had the need to do this a bit for accurately and this
article aims to try and explain the method I have used... I'm not
claiming that this is the correct method for all database storage
calculations but it has worked for me (SQL Server 2005 SP2).
Calculating the size of a table on its own will not tell us a great deal -
we actually need to understand the total initial size of all tables and
then make an informed estimate relating to the projected growth of
each table to ensure that future capacity requirements are taken into
consideration.
The 1st step is to calculate the maximum possible
record size for each table so that we can multiply this by the number of rows -
this will also allow us to forecast growth.
Ok, in order to do this we first need to understand the maximum length of
each of our character data type fields (VARCHAR, TEXT etc). For unicode datatypes
the maximum length will need to be multiplied by 2 to obtain the actual storage requirement
for each field.
Once we have these values for each table we can add them
to the storage requirements for the non-character fields in each table. The
table below shows the storage requirements in
bytes for
the remaining datatypes.
| Data Type |
Size |
| BIGINT |
8 Bytes |
| INT | 4 Bytes |
| SMALLINT | 2 Bytes |
| TINYINT | 1 Byte |
| BIT | 1 Byte |
| DECIMAL / NUMERIC | Precision / Bytes
1-9 / 5 Bytes
10-19 / 9 Bytes
20-28 / 13 Bytes
29-38 / 17 Bytes
|
| MONEY | 8 Bytes |
| FLOAT | Precision / Bytes
1-24 / 4 Bytes
25-53 / 8 Bytes
|
| REAL | 4 Bytes |
| DATETIME | 8 Bytes |
| SMALLDATETIME | 4 Bytes |
| TIMESTAMP | 8 Bytes |
| UNIQUEIDENTIFIER | 16 Bytes |
We now have the detail we need to calculate the initial (static) size of each
table within our database buy simply multiplying the record size (calculated above) by
the total rows. What we don't have is a view of the capacity requirements including
growth. To work this out we need the following data in addition to the
record size and number of inital rows:
» Forecast new records per month
» How long we need to keep data (in months)
» %age growth rate per month
» Data Page Size
Then we calculate:
» The number of records that will fit per page (assuming 8K page size) 8192 / record size = x (round down).
» The month on month storage required (in pages) for the data retention period:
Month 1 - Records / records per page = Month 1 Pages (rounded up)
Month 2 - Month 1 Pages * (100+%age monthly growth) = Months 2 Pages... and so on...
Next add up the month by month page requirements to give us the total number
of pages we will need to store the data in each table for the data retention period.
All we need to do now is convert this into Gigabites so it is meaningful in terms
of the disk storage required. We will assume 300,000 pages and a page size of 8K
for the purposes of the example below:
(300,000 * 8)/1024/1024 = 2.3Gb
Confused? Me too, if in doubt when asked, simply take a deep breath, shake
your head slowly and say... 'That's a big job, it will cost you... are you
sure you need that table?'