|
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
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.
SELECT AVG(LEN(FirstName))
FROM Employees
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: |
|
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
|
|
 |
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| | |
All Rights Reserved, , TechTarget |
|
|
|
|
| | |
