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

Estimating Your Space Needs

By Andre Vigneau,

How do you estimate what a database size will be?

What do you consider and how close do you have to be to reality?

I had to respond to that question many times. I had to come up with estimates many times also, and people were always surprised to get pages of explanation to accompany the magic number… It made the understanding more obvious, if you are not just estimating space but are estimating a storage strategy as well.

I mean let’s face it. An estimate is an estimate. The difficulty comes when you do not put enough in perspective the number you are giving. If you are too low, you might be blamed because it will represent a non-budgeted cost; hopefully you caught it before the database ran out of space. Storage solutions cost can be expensive and you have got to come with solid justification so it can be properly budgeted.

You need room to store the data to.

You might have redundancy needs.

You might have fault tolerance needs.

You might have special backup needs.

You might be looking at an expensive SAN solution to help cover all of the above.

Everyone in the community knows that data and log files, tempdb and system databases files as well as the OS and program files do not belong well together. And what about the page files? And what happens when you try to put many different databases files on the same disk system. Two OLAP or OLTP databases side by side will certainly affect each other …

You must make sure I/O performance will be constant and predictable. You must make sure you will have enough disk space to reach the goal you were given so you have to estimate the space the database will occupy in X months, in X years or whatever. You might want to have free space so you can use (almost) non-disruptive online re-indexing options.

Now, with all this in mind, let’s take a look at what is needed to purely store the data to disk.

You can use the data dictionary of an existing database to get the information you need. Then you can put the information into an Excel spreadsheet and fine tune your numbers. Following you will find an example of the command you can run to extract the basic information you need about tables and indexes structures. You will see that two views are created prior to the query. This is to hide a bit the complexity the query would have shown without them.

The query (code in the Resources Section) is a complex derived table type join based on the new views as well as the system tables they are built on. Before each step you will find within the query an explanation of what it is getting. Numbers and formulas used represent pretty much what SQL Server data structure is using right now. Some data types and details might be missing but it is a fairly good start.

You can put the result of this query in an Excel spread sheet and start to play with the numbers and have Excel calculate the new results for you.

You will also find a link at the bottom (Resources Section) to an example of an Excel worksheet you can use to manipulate the numbers and then use to document and justify your needs. I suggest you past data in the Excel spreadsheet carefully so you do not loose the formulas. You can also build an Excel form from scratch and ameliorate the prediction mechanism and put in better formulas of your own. This spreadsheet calculates space based on an existing database that will see its row increase normally distributed. It might not be exactly the real behavior a database would have in real life.

The important is that you have something to start with and an understandable way of explaining your numbers into a nice Excel format everyone will easily be able to read.

Andre Vigneau MCSE, MCDBA

Resources:

estimates.txt | EstimateSize.xls
Total article views: 11462 | Views in the last 30 days: 14
 
Related Articles
FORUM

Estimated number of rows - Temp tables

Does use of temp tables cause a difference in the estimated number of rows and the actual number of ...

BLOG

Execution Plan Cost Estimates

It’s been emphasized over and over that the costs of operations within an execution plan, and the es...

FORUM

Problem with EXCEL

EXCEL

FORUM

Excel Export Fail when Number of rows in the Excel sheet exceeded the limit of 65536 rows.

Excel Export Fail when Number of rows in the Excel sheet exceeded the limit of 65536 rows.

FORUM

How to export sqlserver 2005 database table to excel sheet

How to export sqlserver 2005 database table to excel sheet

Tags
miscellaneous    
strategies    
 
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