SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


capacity planning for SQL Server Dbs storage


capacity planning for SQL Server Dbs storage

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62703 Visits: 19111
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
MSQLDBA
MSQLDBA
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 577
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.

Learning is a path with no destination...


Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62703 Visits: 19111
How do you get rows per day?

The formulas for calculating space are trivial. It's the information gathering that is hard.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4863 Visits: 9108
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.



sqlguy-549681
sqlguy-549681
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 519
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?
MSQLDBA
MSQLDBA
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 577
Steve Jones - Editor (2/22/2010)
How do you get rows per day?

The formulas for calculating space are trivial. It's the information gathering that is hard.



you right steve, from our school we using the word suppose in math, which means virtual value not exact value, like that as per the database we can justify and in client meeting u can take the idea of their daily transaction, as per their final table structure. For that i also given a example of emp table.

M.I.

________________________________________
M.I.

Learning is a path with no destination...


Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62703 Visits: 19111
sqlguy-549681 (2/23/2010)
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?


This deserves an article, but for a new database, how do you know the rows per day? How do you know the distribution? Often you take a guess, but just as often that guess has no basis. You can guess wildly high and pad things, and what often happens is people start to then implement shrinks to keep the database closer to their guesses. Or you can guess low, in which case you need more space.

For an existing database, do you know the count of rows per day as an average? Almost no tables/schemas I see track this. They don't know. If I asked you right now, for your largest database, what is the rows/day could you easily get the info? Maybe on some tables, but not most of them.

My point is that SQL Server doesn't have a good method for tracking the rows/day. So your formula, while correct, isn't useful. Most people have no idea of distribution as well. How does the Employees table grow v the Tasks table, or the Time table?

I've done what Homebrew does for existing databases, track backup size. It doesnt' give you rows per day, but it gives you a rough average of the aggregate growth of data per day. If you track that, then you can guess in the future what you'll need xx months down the road.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
MSQLDBA
MSQLDBA
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 577
if i use this query so i can find out how many rows does our largest SQL Server table contains?

USE AdventureWorks
SELECT o.name as [Name], i.rowcnt as [Row Count]
FROM sysindexes i
INNER JOIN sysobjects o ON i.id = o.id
WHERE indid < 2 AND o.xtype = 'U'
ORDER BY Name

if we see it today at the day end time so we can find the next day transaction and we can get the approperiate result of daily transaction. we required math daily.
Whats ur openion?

________________________________________
M.I.

Learning is a path with no destination...


homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4863 Visits: 9108
okiftequar (2/23/2010)
if i use this query so i can find out how many rows does our largest SQL Server table contains?

USE AdventureWorks
SELECT o.name as [Name], i.rowcnt as [Row Count]
FROM sysindexes i
INNER JOIN sysobjects o ON i.id = o.id
WHERE indid < 2 AND o.xtype = 'U'
ORDER BY Name

if we see it today at the day end time so we can find the next day transaction and we can get the approperiate result of daily transaction. we required math daily.
Whats ur openion?


My opinion is that I usually don't care about individual table growth. I'm concerned about database growth for capacity (storage space) planning, so that's what I look at.

Table growth, index size, etc is a separate topic for me, usually specific to a particular database and it's performance.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62703 Visits: 19111
Sure you can run that query, but will you run it for every table adn every day? Or are you going to assume the growth that occurs today is representative? Any particular day isn't. It could be wildly up or down.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search