January 13, 2016 at 9:59 am
I've came up with the idea about how is database sizing done.
I've read the information provided by Microsoft on its website (https://msdn.microsoft.com/es-es/library/ms189124(v=sql.120).aspx) about how to make estimations about sizing, but the information on this article supposes that the number of rows that will be present in the tables is already known.
What about if I don't know what will be the exact number of rows that will hold the table?
Event though I coulde use a simple estimate ,this could take to some errors about a minor or bigger estimation.
So I ask you guys, how could I handle this approach for an estimation?
What about running databases, how can estimate future growth?
Thank you.
January 13, 2016 at 10:07 am
ilikawa (1/13/2016)
I've came up with the idea about how is database sizing done.I've read the information provided by Microsoft on its website (https://msdn.microsoft.com/es-es/library/ms189124(v=sql.120).aspx) about how to make estimations about sizing, but the information on this article supposes that the number of rows that will be present in the tables is already known.
What about if I don't know what will be the exact number of rows that will hold the table?
Event though I coulde use a simple estimate ,this could take to some errors about a minor or bigger estimation.
So I ask you guys, how could I handle this approach for an estimation?
What about running databases, how can estimate future growth?
Thank you.
There isn't a catch all formula here. It take analysis and projections of the applications the data requires. Also, the link you provided is talking about a heap (that is a table with no clustered index). Don't create a table like that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 13, 2016 at 10:46 am
It take analysis and projections of the applications the data requires.
So what type of analysis and projections are requiered?
About the link, it's something that I found on the internet and by being provided by Microsoft, I think it coulde work for I'm trying to achive.
Any other advice on what to look on google will be grateful.
January 13, 2016 at 11:08 am
I release my app to many different customers and each uses it differently; some attach huge files to each transaction while others don't. Doing a calculation on each table's rows per page is insane. It is much better to use a real world approach.
Get the actual space used now. Ignore database sizes which is irrelevant. Then divide by number of months in use. For example, a one GB full database used for 10 months is probably growing at 100 MB per month.
Virtually all database space is used by transactional data so if you get an increasing number of "sales" each month you can adjust appropriately...if you calculate SpaceUsedPerSale by dividing total space used by total number of sales.
January 13, 2016 at 12:22 pm
But that would work only expecting that usage will continue as usual but as for a suddenly change in the database usage, isn't?.
I'm aware that daily monitoring will notice this so proper changes will be made.
So there is no final formula or standard procedure to follow, but what else could I use?
January 13, 2016 at 12:35 pm
Prediction is really hard, especially about the future!
I tried to explain customers will adapt and change how they use the app on a daily basis. Watching monthly growth trends should be sufficient...unless you're buying new disks each day to add to your vast array.
January 15, 2016 at 7:38 am
ilikawa (1/14/2016)
@Sean Lange, what else do you recommend?
On what topic? Seems that Bill provided a solid way to go about analyzing potential storage needs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2016 at 11:21 am
b
Sean Lange (1/15/2016)
ilikawa (1/14/2016)
@Sean Lange, what else do you recommend?On what topic? Seems that Bill provided a solid way to go about analyzing potential storage needs.
It's just that to me,this approach sounds to easy and simple to make.
In a brief summary, all what I would need to do is obtain each size of the tables on my database and divide by number of months in use. It's that all?
For new databases where no prior information has been stored in tables, how could I make the calculation to assign the storage?
January 15, 2016 at 12:01 pm
ilikawa (1/15/2016)
bSean Lange (1/15/2016)
ilikawa (1/14/2016)
@Sean Lange, what else do you recommend?On what topic? Seems that Bill provided a solid way to go about analyzing potential storage needs.
It's just that to me,this approach sounds to easy and simple to make.
In a brief summary, all what I would need to do is obtain each size of the tables on my database and divide by number of months in use. It's that all?
Well that would be accurate for historical storage purposes right? The total amount of actual used storage divided by the length of time in use. That would be very accurate description of how much storage was needed per month over the life of the system.
For new databases where no prior information has been stored in tables, how could I make the calculation to assign the storage?
There is no formula here. What does the system do? How much data do you anticipate being in the system. How much wiggle room is in the estimate? What are the storage values for each row? There just isn't a way to state you can calculate this by xxx. It comes down to the amount of storage needed per row and the number of rows. That varies immensely based on the datatypes, architecture of the tables, type of system, amount of users, amount of usage etc....
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply