Capacity Planning

Question

Post reply

Capacity Planning

@SQLFRNDZ

SSCrazy Eights

Points: 9095
More actions
August 23, 2013 at 11:10 pm

#395362

I'm sure many of you can answer this question, Usually for an existing system , Based on current metrics we could decided if need additional resources is required or not based on the usage of the system.
My question, when we are developing new application, based on business requirements as below, how could we design a efficient capacity planning,
For Ex:
10000 Users
200 K transactions per day
Database size is about 1 TB
Throughput may be around 100MB/sec -Assumption
OLTP
VM or Physical
I'm looking for what specs(Please exclude RAID Levels ) I can give to sysadmin and justify -
Please post any tools or good documentation (Links) on this will helps
[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Hold the phone... you have a [font="Arial Black"]1TB[/font] database for a [font="Arial Black"]NEW [/font]application?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 2

No, this was an interview question, 1 TB is just an assumption that database will grow upto 1 TB in next one month and we should scale the server to support that.

The intention is how to choose CPU, Memory base on the example.

Thanks for your reply

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

In that case, my answer would be to examine a similar system and make a determination from that. That is, of course, if that similar system has some form of growth tracking in place. You can sometimes get that information from backup logs either in MSDB or from the tape system.

If the given is 1TB of growth in the first month, I'd plan on at least 2 or 3 years of growth with the understanding that the growth rate may increase if business improves. For the growth rate that you're talking about, it had better be a high throughput system and you might want to consider such appliances as the use of SSDs. The system also needs to be "extendable" so that you can grow into another disk rack if you need to.

For memory, it's nice to say that a system works better if the whole database can fit in memory. Realistically, you have to ask how much of the data becomes low usage static data such as that you might find in an Audit table. Memory and the underlying operating system to support it is one of those things you should never "go cheap" on. You just about can't have too much memory for what you're talking about.

As with memory, having more CPU's will never hurt except in one area. Licensing costs for things like SQL Server.

Personally, I don't care for VM on large systems but that's a personal judgment. Many others will give you good reasons for using VM on such things and they'd be correct also.

Hope that helps.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 4

Thank you Jeff, definitely helps.

In addition,I was thinking of a load test resource bottlenecks before going to live with some software which can create the virtual users, For example hammerdb tool as mentioned in Kendra, Little blog (not used it yet but read on blogs.)

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

@SQLFRNDZ (8/24/2013)
Thank you Jeff, definitely helps.
In addition,I was thinking of a load test resource bottlenecks before going to live with some software which can create the virtual users, For example hammerdb tool as mentioned in Kendra, Little blog (not used it yet but read on blogs.)

I found that load testing an application doesn't necessarily help with resource planning or scalability predictions because performance challenged code is frequently not helped at all by adding resources to the server. Examining and synchronizing a couple of days worth of SQL and Windows Profiler runs and then fixing the code that identifies might prove more useful. No matter how much hardware you have, things just might not scale if you have code that doesn't. And remember, the front end isn't the only user of code and data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)