<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
capacity planning for SQL Server Dbs storage
25 posts, Page 2 of 3
««
1
2
3
»»
capacity planning for SQL Server Dbs storage
Rate Topic
Display Mode
Topic Options
Author
Message
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Monday, February 22, 2010 8:36 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
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
Post #870402
MSQLDBA
MSQLDBA
Posted Monday, February 22, 2010 8:41 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 21, 2012 8:49 AM
Points: 129,
Visits: 551
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...
Post #870412
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Monday, February 22, 2010 8:43 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
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
Post #870413
homebrew01
homebrew01
Posted Monday, February 22, 2010 9:20 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 2,551,
Visits: 7,201
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.
Post #870473
sqlguy-549681
sqlguy-549681
Posted Tuesday, February 23, 2010 2:53 AM
SSC Journeyman
Group: General Forum Members
Last Login: 2 days ago @ 7:14 AM
Points: 93,
Visits: 456
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?
Post #870999
MSQLDBA
MSQLDBA
Posted Tuesday, February 23, 2010 7:39 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 21, 2012 8:49 AM
Points: 129,
Visits: 551
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...
Post #871135
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, February 23, 2010 8:36 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
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
Post #871184
MSQLDBA
MSQLDBA
Posted Tuesday, February 23, 2010 9:26 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 21, 2012 8:49 AM
Points: 129,
Visits: 551
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...
Post #871243
homebrew01
homebrew01
Posted Tuesday, February 23, 2010 9:52 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 2,551,
Visits: 7,201
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.
Post #871277
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, February 23, 2010 10:23 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
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
Post #871305
« Prev Topic
|
Next Topic »
25 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
