Expert advice on database administration

Digg This!     StumbleUpon     Del.icio.us

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. Stora To continue reading for free, register below or login Requires Membership to View To gain access to this and all member only content, please provide the following information (all fields required): Email Address: Create Password: Confirm Password: By joining SearchSQLServer.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email. TechTarget cares about your privacy. Read our Privacy Policy To read more you must become a member of SearchSQLServer.com As an existing member of the TechTarget network please provide your password to activate your account (Forgot your password?): Password: By joining SearchSQLServer.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email. TechTarget cares about your privacy. Read our Privacy Policy Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT SQL Server Database Modeling and Design Managing the development lifecycle with Visual Studio Team System 2008 A first look at Visual Studio Team System 2008 Database Edition Testing transaction log autogrowth behavior in SQL Server Top 10 SQL Server Tips of 2008 Tutorial: SQL Server indexing tips to improve performance Tutorial: Learn SQL Server basics from A-Z SQL Server database design disasters: How it all starts Can you shrink your SQL Server database to death? Physical data storage in SQL Server 2005 and 2008 SQL Server 2008 data types: Datetime, string, user-defined and more SQL Server Migration Strategies and Planning Using Microsoft Hyper-V for SQL Server consolidation Migrating to SQL Server 2008 and leveraging new features The challenges of SQL Server consolidation Testing a SQL Server environment before an upgrade SQL Server Consolidation Fast Guide SQL Server consolidation strategies and best practices Does upgrading to SQL Server 2008 fit your business? A guide to advanced new features in SQL Server Management Studio 2008, part 2 A guide to basic new features in SQL Server Management Studio 2008, part 1 SQL Server virtualization pros and cons: Weigh the performance impact Database Development Managing the development lifecycle with Visual Studio Team System 2008 Processing XML files with SQL Server functions A first look at Visual Studio Team System 2008 Database Edition How to create a SQL inner join and outer join: Basics to get started New datetime data types in SQL Server 2008 offer flexibility Using DATEADD and DATEDIFF to calculate SQL Server datetime values SQL Server database design disasters: How it all starts SQL Server database design disasters: What not to do Secure SQL Server from SQL injection attacks Check SQL Server database and log file size with this stored procedure RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary binary tree  (SearchSQLServer.com) block  (SearchSQLServer.com) data structure  (SearchSQLServer.com) DDBMS  (SearchSQLServer.com) entity-relationship model  (SearchSQLServer.com) initial extent  (SearchSQLServer.com) primary key  (SearchSQLServer.com) segment  (SearchSQLServer.com) tablespace  (SearchSQLServer.com) view  (SearchSQLServer.com) RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary ge requirements for numeric data types [TABLE]
Money	8 bytes
Smallmoney (SQL 2008 and newer)	4 bytes
Float	[TABLE]
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.

[TABLE]
((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.

[TABLE]

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.

[TABLE]

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.