February 21, 2010 at 7:16 am
Hello All,,
I am tasked to provide the storage required for SQL Server Databases for the coming 3 years
I followed some instructions I have found and collected the data required to complete my task
Like
•How many database will host on SQL server
•How many users connected to SQL SERVER
•Row length per table for each database
•Data loading rate per day\week\month
---------------------------------------
On the time being I am searching on an easy way to calculate the storage required for the coming 3 years based on the input I have.
Does anyone have an idea or clear and solid steps to do this??
Thanks in advance & appreciate ur support
February 21, 2010 at 4:11 pm
FYI: this is actually called "Storage Planning", as "capacity planning" has a very specific and completely different meaning for computer systems.
I do remember seeing some good storage planning articles a couple of weeks ago, I'll see if I can find them...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 4:13 pm
also, what kind of organization is this for? Business, academic, non-profit, Govt, NGO, ... ?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 5:03 pm
This isn't the article I was thinking of, but it does have some perspective and approach for this: http://sqlblog.com/blogs/merrill_aldrich/archive/2009/10/29/using-historical-perf-counters-for-storage-planning.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 6:18 pm
I guess you could find out the storage required for data using the data load rate, then double it to account for indexes and then double it again to account for any underestimation you could have made.
February 21, 2010 at 6:58 pm
Are you "loading" data or is this users making changes? If it's the latter, which is what most systems have, it's impossible to do anything other than guess without historical data.
I've typically used backup sizes (since those are data) and tracked that over time to allow me to extrapolate out what space I will need in the next xxx months.
February 22, 2010 at 3:13 am
thx guys
here is how to Determining SQL Server database storage requirements in 2 ways Hard and easy
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1289528_mem1,00.html
thx again
February 22, 2010 at 7:43 am
That's a highly incomplete link, and it's what was said before.
The hard part is determining
- records/month
If you know that, then it's easy. But getting that number can be very hard.
February 22, 2010 at 8:03 am
I guess this info supposed to be available with applications developesr and business users, the only way to get this number is "Expectaion " , coz no way provide exact number for record per (day,week or Month) for a blank farm
Kindly correct me if i am wrong
February 22, 2010 at 8:26 am
I don't delete my full backup history so that I can use previous backup size to predict future growth.
-- Average DB size by month for ALL Databases
-- Backups DO NOT include free space in database
selectsubstring(a.database_name,1,35) as 'Database',
datepart(year,a.backup_start_date) as 'year',
datepart(month,a.backup_start_date) as 'month' ,
avg(cast((a.backup_size /1073741824) as decimal (9,2)))as 'Avg Gig'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name
and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D'
--and a.database_name = 'xyz'
and a.backup_size > 1073741824 -- > 1 Gig
GROUP BY a.database_name,datepart(year,a.backup_start_date),datepart(month,a.backup_start_date)
order by a.database_name,datepart(year,a.backup_start_date) desc,datepart(month,a.backup_start_date) desc
February 22, 2010 at 8:36 am
The only way to get this number is to track it. I used to track it by table, but it didn't seem to be a useful metric for me. I have done as homebrew01 mentions and tracked backup sizes. I keep track of them over time and use that as a space metric.
I also flag more than 10% growth in a day as something that needs to be investigated.
February 22, 2010 at 8:41 am
Hi freinds,
Me not that much strong like u guys, u my seniors but i want to share something my small research with u. If u feel any mistake in my research for capacity planning, so pls give ur valuable suggestions,
Supppose we have a table emp:
EMP Table
-----------
Feild Type
Empno - Int(4) ---> 64bytes (db capacity calculation)
Empname - varcahr(50) db. avg siz = 400 bytes
Sal - money 1 day = 12000 rows (daily transaction)
*(multiply by)
1 month = 30
*(multiply by)
3 month = 90 (v.versa)
Avg space req. = ?
Example: (To stimate db capcity plan)
Avg. size of row = 400 byts
No. of rows per day = 12000
Working days in a month = 30
Frequency (how many months) = 3 months
Formula:
Space to be allocated for the db = 3*30*12000*400
Note: Based on designed tables we can stimate the db size/capacity.
Thanks & regards,
M.I.
________________________________________
M.I.
[font="Times New Roman"]
February 22, 2010 at 8:43 am
How do you get rows per day?
The formulas for calculating space are trivial. It's the information gathering that is hard.
February 22, 2010 at 9:20 am
Then, after I qet the query results of monthly growth averages, I paste them into a spreadsheet to calulate average growth percent per database per month. I then use that average to estimate future growth per database for 1 and 2 year periods in the future.
February 23, 2010 at 2:53 am
Hello Steve,,
as you said "The formulas for calculating space are trivial. It's the information gathering that is hard."
would you please tell what are the informations I must have?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply