How to Spec a SQL server

[Matt]

Hello

We are upgrading our DB server to a new machine and there is a heated
debate about what spec machine to use.

We have on this server a 15 GB DB that handled around 2-3 million
transactions / day, 50% updates and 50& reads roughly, the main goal
since it is used by an interactive end user application is speed for
updates and reads, it does not need to do any heavy calculations at
all during daytime, at nighttime it does a lot of batch jobs, going
through the data and inserting results into different tables.

the main questions I have is:

Single or Dual CPU
AMD 64 or Intel CPU
2 or 4 GB of RAM
DIsk Setup for max I/O, we are thinking, mirror for the OS, mirror for
the DB logs and RAID 5 for the DB data, using 2 different RAID
controllers to try and maximize disk I/O, keeping the logs and OS on
one controller and DB data on the second controller.

is there anything I need to watch out for? or something to pay more
attention to than other things?

ANy advice would be greatly appreciated

rgds

Matt

[Simon Hayes]

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@posting.google.c om...

Hello

We are upgrading our DB server to a new machine and there is a heated
debate about what spec machine to use.

We have on this server a 15 GB DB that handled around 2-3 million
transactions / day, 50% updates and 50& reads roughly, the main goal
since it is used by an interactive end user application is speed for
updates and reads, it does not need to do any heavy calculations at
all during daytime, at nighttime it does a lot of batch jobs, going
through the data and inserting results into different tables.

the main questions I have is:

Single or Dual CPU
AMD 64 or Intel CPU
2 or 4 GB of RAM
DIsk Setup for max I/O, we are thinking, mirror for the OS, mirror for
the DB logs and RAID 5 for the DB data, using 2 different RAID
controllers to try and maximize disk I/O, keeping the logs and OS on
one controller and DB data on the second controller.

is there anything I need to watch out for? or something to pay more
attention to than other things?

ANy advice would be greatly appreciated

rgds

Matt

You might want to start by looking at where your current server
configuration is encountering limitations. For example, if you have issues
due to insufficient memory (such as a low cache hit ratio), but the CPU is
not heavily used, then you should probably consider more memory rather than
an additional CPU. The disk setup sounds reasonable, RAID10 would be better
than RAID5 (but more expensive, of course); again, you should probably look
at I/O in your current configuration first.

Some server vendors have sizing guides for MSSQL which help you to assess
what hardware you need - you may want to check that out. In addition the
Microsoft SQL 2000 Performance Tuning Guide has chapters on capacity
planning and I/O subsystems which could be useful in making a decision.

Simon

[Greg D. Moore \(Strider\)]

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@posting.google.c om...

Hello

We are upgrading our DB server to a new machine and there is a heated
debate about what spec machine to use.

We have on this server a 15 GB DB that handled around 2-3 million
transactions / day, 50% updates and 50& reads roughly, the main goal
since it is used by an interactive end user application is speed for
updates and reads, it does not need to do any heavy calculations at
all during daytime, at nighttime it does a lot of batch jobs, going
through the data and inserting results into different tables.

the main questions I have is:
There's a good book from MS Press on sizing SQL Servers. I don't have the
name off the top of my head.

As you note, the biggest thing is going to be the disk I/O.

As a point of reference I have a server that handles about 14 million
inserts a day. (and a roll-up job at night.)

It's a quad XEON 700Mhz 2MB CPU (so rather old).
3 physical arrays, 10K drives. All are RAID 10

It used to handle only about 7 million inserts a day, but we found a flaw in
the code that we fixed that allowed us to get more inserts.

(in fact used to max out at about 50-60% cpu and beyond that, we just
couldn't get any more inserts done. Code change allows it to run at nearly
100% CPU. So now the CPU is the bottleneck.)

Single or Dual CPU
AMD 64 or Intel CPU
2 or 4 GB of RAM
DIsk Setup for max I/O, we are thinking, mirror for the OS, mirror for
the DB logs and RAID 5 for the DB data, using 2 different RAID
controllers to try and maximize disk I/O, keeping the logs and OS on
one controller and DB data on the second controller.

is there anything I need to watch out for? or something to pay more
attention to than other things?

ANy advice would be greatly appreciated

rgds

Matt