Essential Guide

Hardware for SQL Server: Optimizing performance

A comprehensive collection of articles, videos and more, hand-picked by our editors

Determining SQL Server database storage requirements

The first time you calculate storage requirements for a SQL Server database application is significantly important and should be done during the development phase. Here are two methods for pinpointing future storage needs.

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.

More on SQL Server database design

Learn to use SQL Server Management Studio Table Designer

Read these guidelines for database index design and optimization

Check out the top SQL Server stories of 2013

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.

This was first published in January 2008

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Hardware for SQL Server: Optimizing performance

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close