Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

capacity planning for SQL Server Dbs storage Expand / Collapse
Author
Message
Posted Monday, February 22, 2010 8:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:59 PM
Points: 31,082, Visits: 15,529
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
Posted Monday, February 22, 2010 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:43 AM
Points: 131, Visits: 558
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
Posted Monday, February 22, 2010 8:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:59 PM
Points: 31,082, Visits: 15,529
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
Posted Monday, February 22, 2010 9:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:08 AM
Points: 2,827, Visits: 8,480
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
Posted Tuesday, February 23, 2010 2:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 3:02 AM
Points: 93, Visits: 500
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
Posted Tuesday, February 23, 2010 7:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:43 AM
Points: 131, Visits: 558
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
Posted Tuesday, February 23, 2010 8:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:59 PM
Points: 31,082, Visits: 15,529
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
Posted Tuesday, February 23, 2010 9:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:43 AM
Points: 131, Visits: 558
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
Posted Tuesday, February 23, 2010 9:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:08 AM
Points: 2,827, Visits: 8,480
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
Posted Tuesday, February 23, 2010 10:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:59 PM
Points: 31,082, Visits: 15,529
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse