August 12, 2009 at 7:03 pm
Hi
I have one transactional table called message in my prod db. It is around 15 GB for 5 days data in the table. The data in message table move into archive table data older than 5 days as per current setup.
We are planning partition for this table. So, I need to estimate the size of table for next one year.
How to estimate it and what is the best way implement the partition for this table.
One of the column is datetime and another one column is configured by identity.
Thanks in advance.....
August 12, 2009 at 8:53 pm
Really? You have 15GB in 5 days. Can't you do the estimate for a year?
15GB * (365/5) = xxGB
August 12, 2009 at 9:09 pm
Steve Jones - Editor (8/12/2009)
Really? You have 15GB in 5 days. Can't you do the estimate for a year?15GB * (365/5) = xxGB
Or, an average of 3GB/day * 365 days = 1095GB for one year (approximately).
Really simple math here. :w00t:
August 12, 2009 at 9:22 pm
August 12, 2009 at 9:59 pm
karthikeyan (8/12/2009)
Hi...How do I setup the partition for this table....
Pls share with me any best way to implement the partition.
Before we share our ideas with you, why don't you share your ideas with us first. You know your situation better than we do. What options have you considered and what conclusions have you come up with?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 13, 2009 at 1:48 am
http://www.google.com/search?SQL+partitioning+recommendations
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 9:51 am
Lynn Pettis (8/12/2009)
Steve Jones - Editor (8/12/2009)
Really? You have 15GB in 5 days. Can't you do the estimate for a year?15GB * (365/5) = xxGB
Or, an average of 3GB/day * 365 days = 1095GB for one year (approximately).
Really simple math here. :w00t:
It may be slightly less simple than that. How did you get the five days figure? Were they five business days? If not, then what volume would you expect over the weekends? Will there be seasonal fluctuations in the volume?
If weekends are excluded, the total may only be closer to 52+15=780GB.
If seasonal flucuations are expected, the total may be quite different.
Bottom line is that just five days seems a rather small sample size for something that could grow that large. If it's off by say 2GB, you could end up planning for a TB-sized table but actually have one that needs yet another 250GB.
August 13, 2009 at 11:22 am
john.arnott (8/13/2009)
Lynn Pettis (8/12/2009)
Steve Jones - Editor (8/12/2009)
Really? You have 15GB in 5 days. Can't you do the estimate for a year?15GB * (365/5) = xxGB
Or, an average of 3GB/day * 365 days = 1095GB for one year (approximately).
Really simple math here. :w00t:
It may be slightly less simple than that. How did you get the five days figure? Were they five business days? If not, then what volume would you expect over the weekends? Will there be seasonal fluctuations in the volume?
If weekends are excluded, the total may only be closer to 52+15=780GB.
If seasonal flucuations are expected, the total may be quite different.
Bottom line is that just five days seems a rather small sample size for something that could grow that large. If it's off by say 2GB, you could end up planning for a TB-sized table but actually have one that needs yet another 250GB.
True John, however, I think I'd rather err on the side of over estimating rather than the other way around. If I were actually making an estimate based on the amount of data collected over 5 days, I'd actually add another 20% to the estimate. Who knows what growth in data may occur over time.
August 14, 2009 at 8:17 am
karthikeyan (8/12/2009)
HiI have one transactional table called message in my prod db. It is around 15 GB for 5 days data in the table. The data in message table move into archive table data older than 5 days as per current setup.
We are planning partition for this table. So, I need to estimate the size of table for next one year.
How to estimate it and what is the best way implement the partition for this table.
One of the column is datetime and another one column is configured by identity.
Thanks in advance.....
My standard recommendation for scenarios like this: You are asking questions that someone tasked with managing 15GB of data growth per week should not be asking. Get yourself a professional to help with analysis and implementation of your needs to avoid a lot of pain and problems in the future. You simply cannot be successful in your endeavour from a few back-and-forth forum posts.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply