Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Basics of Sizing a SQL Server Database

By Brian Knight, (first published: 2001/07/25)

Sizing a database can be one of the most arduous tasks a DBA, analyst or developer must attend to. It’s time consuming and more hours go into analyzing the database than actually sizing it. This article focuses on how to monitor the database’s growth after its deployed and contains some tips on how to size it before deployment. We will also dive a little into how to benchmark your database against a robust data load.

So how much equipment do you need to buy for your database? What you answer can mean the difference between a SAN deployment and a few RAID 1 drives. Luckily for you, there are some tools out there to efficiently size the database before deployment. For example, you can use ERwin (a product of CA) to determine how large your database will be in x number of months. You can also load a database with random data using products like Datatect 1.6. Those who are feeling brave can try to do the math yourself, but beware of the dreaded indexes. When most people size a database without the aid of a 3rd party product, they miss the space needed by indexes, foreign keys and constraints.

The easiest way of sizing databases is through the data model program itself, like ERwin. ERwin (as most data modeling programs) has a volumetric tab that allows you to specify a number of volume items when you’re creating the table. In ERwin, you can specify:

  • The number of initial records
  • The number of new records monthly
  • The change a column will be NULL

The combination of these three factors can give the product the ability to generate accurate volumetric reports any number of months out. These reports can be outputted to HTML and can even be viewed by object. Most modeling tools account for indexes, foreign keys and constraints. The only thing ERwin doesn’t account for is indexed views, which can take up quite a bit of space if they’re against a large table.

Once you have a general ideal of how large your database is going to be, you can prove your theory with a product like Datatect 1.6. Datatect allows you to create meaningful random data to load into your database. It also takes into account foreign keys as it’s loading data. After trying all the other data generators, Datatect 1.6 was the easiest way to load valuable sample data into my database I found. I found the other data loading applications to be unstable and would easily peg my CPU. I am finishing my review of Datatect 1.6 next week and will have my full lab results complete then.

So why would you want to load a sample database? A few months ago I used ERwin to project a new product’s database size would grow to about 50GB after 6 months of operation. The application developers were used to developing against test databases with only a few hundred sample records in them and couldn’t fathom how to develop against a database this size. After loading the database using Datatect, they were able to find deficiencies in the application that would’ve crashed the server within six months once the application went to production. 

With Datatect, I was able to load a 10 GB table in about 12 hours with meaningful test data. The same task in a similar product by a larger vendor took over 8 days. Make sure that whenever you load the data, that you remove all clustered indexes. Otherwise you can increase your load time significantly. Once you have a sample database, you can use programs like WinRunner, which can benchmark an applications performance, to see how it will perform with so many records. 

The final approach is to watch and learn. You can use this when you want to size a server aggressively and then deploy it with your best guess on the size. Once I deploy a server, I have a table on each server where I log statistics about my databases. Each week, I capture a snapshot of how large each database is and then I can pull it into a spreadsheet for later use. Once in the spreadsheet, you can draw trend charts to project where your database will be physically in one year.

A sample stored procedure to get you started is available in the Resources section below. In a later article, we will dive into the more advanced topic of how to size your database yourself without the assistance of a third-party tool

Resources:

usp_databases.txt
Total article views: 39959 | Views in the last 30 days: 47
 
Related Articles
ARTICLE

Review: Loading Sample Data with DataTect 1.6

So, you've created a database and application and want to see how it operates with a substantial loa...

FORUM

ERwin

Tutoriials for ERWin

FORUM

production database sql server 2000

production database sql server 2000

FORUM

Anyone knows supporting version of ERWin

Anyone knows supporting version of ERWin

FORUM

Development and Production Database

Insert Into Development and Production Database at the same time

Tags
administration    
advanced    
configuring    
database design    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones