Determining accurate storage requirements in SQL Server is an ongoing process that you should repeat regularly throughout the life of the database application. The first time to decide how much storage you'll need is during the development phase of the database application's life span, before the QA and production hardware is ordered. The earlier and more accurately you can set storage needs, the more precise the system budget will be.
Trying to accurately establish the size of your SQL Server database in the future is not an easy task. There are two methods that can be used. The easier method gives you less accurate sizing, but requires less information, time and math. The harder method will give you a more accurate size but requires more research, information, time and math. While the formulas for the harder method are more complex, once you understand how they work you'll get accurate requirements fairly quickly.
How to calculate SQL Server database storage needs<...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
/b>
When calculating the size of your database, you are actually calculating the size of each table and adding them together to get a total database size. The first thing to do is determine the average size of a record. If you already have data in your database, this is a fairly easy thing to get. For each character column within your table, simply find the average length of the column. This can be done with either the LEN or DATALENGTH functions. If you choose the LEN function and use Unicode data types (NVARCHAR, NCHAR, NTEXT, etc.), then multiply the value by 2 to get the actual storage required.
Take these averages and add them to the SQL Server storage requirements as noted below for the numeric data types. The data types you should be querying from the database are char, nchar, varchar, nvarchar, text, ntext, xml, sysname, sql_variant, binary, varbinary and image.
Storage requirements for numeric data types
bigint
8 bytes
Int
4 bytes
Smallint
2 bytes
Tinyint
1 byte
Bit
1 byte
Decimal and numeric
Precision
Bytes Required
1-9
5 bytes
10-19
9 bytes
20-28
13 bytes
29-38
17 bytes
Money
8 bytes
Smallmoney (SQL 2008 and newer)
4 bytes
Float
Precision
Bytes Required
1-24 (7 digits)
4 bytes
25-53 (15 digits)
8 bytes
Real
4 bytes
Datetime
8 bytes
Smalldatetime
4 bytes
Timestamp
8 bytes
Uniqueidentifier
16 bytes
Once you add up all these values, you'll have the average row size for a single record in your database.
Now, the two methods begin to differ. We will start with the easier, less accurate method.
The easy way to determine database storage requirements
To calculate your storage needs the easy way, take the average row length and multiply this by the number of records per month that will be entered into the database. Take this number and multiply it by the number of months' worth of data you'll be keeping in the database. This gives you a rough idea of the amount of storage each table will require. If you have any variable length string fields in your table, add 4 bytes to the average row length to account for overhead, which SQL Server requires when maintaining the table.
Parameter
Value
Average Row Length
91+4 = 95
Records Per Month
187643
Data Retention Period
36 Months
((95*187643)*36)/1024/1024/1024 = 0.597 gigs of storage
The hard way to determine database storage requirements
To calculate your storage needs the hard way, first gather some information in addition to the numbers gathered above. You need to know the estimated month-to-month growth rate of the table as a percentage of the prior months' data insertion rate. This growth rate will probably be about the same growth rate as the business unit it supports. You also need to know that SQL Server uses 8k pages when storing the data to disk. When using this method, all calculations will be done in number of pages then converted back to bytes/gigabytes.
Parameter
Value
Average Row Length
91+4 = 95
Records Per Month
187643
Data Retention Period
36 Months
Monthly Data Growth Rate
8%
Data Page Size
8k (8192 bytes)
Records Per Page – 8,192/95 = 86.23 (rounded down to 86. Always round this number down, as SQL Server does not support page splits).
Pages required for Month 1 – 187,643 / 86 = 2,181.89 (rounded up to 2,182. Always round this number up).
Pages required for Month 2 – 2,182*1.08= 2,356.56 (rounded up to 2,357. Always round this number up).
Continue until you account for all months in your data retention period.
Add up the values for each month to get the total pages required for this table over the data retention period. In our example, 408,272 pages are required. To convert this to gigabytes, multiply that number by 8 then divide by 1,048,576.
(408,272*8)/1,024/1,024 = 3.11 gigs of storage
As you can see, there is quite a large difference in size between the two methods. The easy method does not account for growth of the business and database. (We all hope the database grows, because if it doesn't there is a business problem that needs to be addressed.) Neither does the easy method account for the fact that your rows may not exactly fit into the 8k data pages. Because SQL Server requires that the data for a record (excluding text, ntext, image, varchar(max), nvarchar(max), varbinary(max) and xml data type data) be stored within a single data page, white space is left within the data page. This white space cannot be reclaimed and you need to account for it when calculating data storage requirements, especially for large tables (either wide or long tables).
Click here to view the calculations for the database storage needs used in this tip.
ABOUT THE AUTHOR:
[IMAGE]Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's more than 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He uses these skills in his role as a senior database administrator and architect at Awareness Technologies. Denny is a longtime member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Copyright 2008 TechTarget
