Determining SQL Server database storage requirements

Determining SQL Server database storage requirements

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in January 2008

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,

More on SQL Server database storage and design:

 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 over 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 currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

 Check out his blog: SQL Server with Mr. Denny.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    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.

    Back to top