This week I attended a day of training on some of the new features for SQL 2008.  I mention this because one of the presenters made a 'joke' about problems with databases being related to 'needing more ram'.  The reason this is of interested is it touches on the first approach most people are familiar with in regards to addressing performance issues on a database/site: scaling up.

Lets for a moment, put on the hat of a person who is building or supporting a site that has poor performance.  The database/site didn't start out that way.  In fact, it was working nicely.... for a while.  At first it was humming along.  You hit a web page and it is super quick.  As more users began to use the system, it was a little bit slower (but thats OK, its still working nicely).  More users hit the site and performance degrades even more.  You start to notice that your database is struggling.  CPU is a little higher than you expect, disk I/O begin to increase, cache hit rations start to decline and queued disk requests start to pile up.

You know that you cannot afford to rewrite the application or redesign the schema, and your being told more users are coming.  If just reading this causes you a little bit of anxiety, well then you are the right person or people to be reading this.  (Have I gotten your attention?)

What is the first thing you think of to address this problem... well if your running a 64 bit OS your answer is almost always 'More ram please'.  Congratulations, you have made it to the first stage of recovery, er.... scaling up and providing your server can handle it, it is a very cost effective approach to improving performance for most systems.

Imagine the sounds of a calculator and bell going off... chink chink DING.. 'minus the cost per hour of a developer' chink chink chink DING ... times the number of hours for a developer to write the changes .... chink chink chink DING... plus the cost to test....plus...plus...plus.... EQUALS!!!!    'Buy the ram!!!'

(in my best 'old tome of wisdom' voice) 'Heed ye these warnings, you'll be back, with hat in hand, asking for more soon.' 

Like a drug addict looking for a quick fix, you will find that the answer to all your problems is just more More MORE.  More CPUs, more memory, more disk, more storage, more RPMs, more more more.  At some point you may recognize that you have a problem.  There is only so much room for more.

 Today, there are practical limits to how many CPUs, ram and how many disks you can use.  The advent of multi-core CPUs, cheap ram, and terabyte disks has made it very 'affordable' and easy to hit those limits.  For most people, the following are a sort of glass ceiling that becomes too expensive to pass through:

• 16 cores (effective CPUs)
• 64 G ram
• ~64 disk drives

Practically speaking, this becomes an expensive box and a critical resource.  Why is that the case?  For four reasons:

1. This box could run in excess of $60,000.  That alone could be a deal breaker for some people.
2. Since this is a critical resource, you need to have a backup either cold or warm. (add another $60,000 to your bill)
3. Since you went down this route, you clearly know about your performance challenges.  Before any changes are permitted on the production system, you will want a full performance acceptance stress test done on equivalent hardware (add another $60,000 to your bill)
4. You may want your development organization to be able to reproduce production level issues.  Depending on the size of your company, you may need to leverage the 'test' machine, or dedicate another box for development. (yup, add another $60,000)

That $60,000+ investment could easily cost about a quarter of a million dollars ($240,000).  Of course you may be able to avoid some of these costs through creative resource sharing, but it will be hard to avoid spending at least twice the price that it will cost for your one 'scaled up' box. 

Note to self: How many servers could I buy for $240,000?  A lot!!!  If only there was a way to use smaller, cheaper servers to do what I need. (if only it were that simple)

Not to let you think that scaling up is a negative approach.  It is an excellent approach for most small sites.  You do not have to make changes to your software.  An entire series of tasks is easier: deployment of upgrades or fixes, testing, disaster recovery, systems management, etc.

If you didn't already think of it, you may start to focus on performance related changes to stave off the costs of more, more, more.  However, if your 'lucky' you will reach a point where scaling up no longer works. 

It will sneak up on you, but you will scale out.  You may already have scaled out and not have known it.