July 9, 2011 at 3:28 am
What should be the strategy to anticipate the DB growth for the next entire year or say 5 years.
July 9, 2011 at 4:35 am
The simplest, easiest, and, perhaps, the most accurate method is to determine the database size today and a month from now. Multiply the difference between those two numbers times the number of months you want to forecast out and add any company growth factors.
--Jeff Moden
July 9, 2011 at 8:12 pm
Try to do what you can to minimize the data volume as well. Ask yourself these kinds of questions:
- How long you need to keep the data for? Purge Old Data
- If you need to keep old data, can it be archived off into another database/backup file?
- Make sure there is not unnecessary history table growth. History Table Growth
Jason
Webmaster at SQL Optimizations School
July 12, 2011 at 2:43 pm
Another important thing to consider is just how normalized your tables are in your database.
I can't tell you how many times I've worked with databases in which there is a single large table containing 50 columns and millions of rows, in which quite a few of these columns are either INT fields storing single-digit numbers, or worse, VARCHAR(X) fields, all repeated hundreds of times.
Normalizing the database design might take some time to do, but in the end what you'll have is a table structure which is much more scalable, and also far easier to index.
September 2, 2011 at 11:33 am
sqlnaive (7/9/2011)
What should be the strategy to anticipate the DB growth for the next entire year or say 5 years.
We keep monthly stats showing space allocation therefore (in a perfect world and taking out of the picture any new functionality or changes on retention policies) we can predict the size of the database in the future.
In general, OLTP databases tend to reach a cruise size and stay there - this implies having a well designed archive and purge strategy in place.
On the other hand, DSS systems like a Data Warehouse are in general meant to grow forever. To avoid this situation some shops adopt strategies like moving out granular operational data older than - let's say 10 years - keeping just a summarized, consolidated version of it online. On my experience, once you painfully deploy such a solution in two or three month time it turns out somebody actually needs the granular, old data that is now comfortable sitting somewhere in a dark dungeon 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply