Machine to run SQL 2000

Our current SQL server box is a Dell PowerEdge 4400 with one PIII Xeon
800Mhz CPU and 768MB RAM. Some of the tables we have are quite large (~50
million rows) and as of late we have noticed a degradation in performance.
As we will be needing a new server to host our new website, we have decided
to relegate the 4400 to web hosting and buy a new SQL server box. I know
there is time to be spent running profiler / perfmon etc to track down the
source of the performance problems, but we WILL be needing a new box first,
then I can start to diagnose the perf. issues.
What I would like to know is what factors are MOST important when
considering a machine to run SQL server i.e. CPU Mhz, Dual/Quad processor,
RAM, On-chip cache etc. I know the best advise is usually "get the best
machine you can afford" but when weighing up the options there are some
things that have to be decided on an either/or basis i.e. a one-CPU machine
with 1MB cache, or a dual-CPU machine with 512KB cache.
Many thanks for any input,
dfAll of this is my experience and personal opinion. YMMV.
I prefer more CPUs to bigger CPU cache.
More memory is preferable to faster CPUs, within reason. Unless you are
running Enterprise Edition, anything over 4GB in the box is wasted so plan
accordingly. If you are using Enterprise edition, put as much memory as you
can afford in the box.
RAID-10 is three to five times faster than RAID-5. Lots of drive spindles
is a cheap way to boost performance.
Definitely use Windows Server 2003 as the OS.
Since you are a Dell shop, you might want to check out the CX-200
entry-level SAN. It is a bit more than a SCSI Powervault, but not
prohibitively so. I would seriously look into the best PowerEdge 6650 I
could afford with a CX-200 back-end. If not, maybe a 6600 with 12 internal
SCSI drives. Obviously , we all have budget constraints but definitely
squeeze every bit you can for this.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"digitalfish" <digital.fish@.ntlworld.com> wrote in message
news:gxyeb.2143976$Bf5.300990@.news.easynews.com...
> Our current SQL server box is a Dell PowerEdge 4400 with one PIII Xeon
> 800Mhz CPU and 768MB RAM. Some of the tables we have are quite large (~50
> million rows) and as of late we have noticed a degradation in performance.
> As we will be needing a new server to host our new website, we have
decided
> to relegate the 4400 to web hosting and buy a new SQL server box. I know
> there is time to be spent running profiler / perfmon etc to track down the
> source of the performance problems, but we WILL be needing a new box
first,
> then I can start to diagnose the perf. issues.
> What I would like to know is what factors are MOST important when
> considering a machine to run SQL server i.e. CPU Mhz, Dual/Quad processor,
> RAM, On-chip cache etc. I know the best advise is usually "get the best
> machine you can afford" but when weighing up the options there are some
> things that have to be decided on an either/or basis i.e. a one-CPU
machine
> with 1MB cache, or a dual-CPU machine with 512KB cache.
> Many thanks for any input,
> df
>|||On Wed, 01 Oct 2003 11:04:44 GMT, "digitalfish"
<digital.fish@.ntlworld.com> wrote:
>Many thanks for any input,
Preference to the dual cpu -- the benefits outweigh the problems.
Lots o' RAM.
RAID disk. Used to be a big deal to mirror the log and stripe the
main store, I don't quite know now what's optimal. IMO, big RAM makes
a lot of that less important. Depends how big the database is and the
kind of traffic you do on it, of course.
J.|||Geoff;
I agree with you on the general choice between more CPUs and bigger CPU
cache, and between more memory and faster CPUs.
What would be your choice between faster CPUs and more CPU cache? In total
abstract and in general, my perference has been more CPU cache. Obviously,
I'm not talking about going from 400MHz to 2.6GHz.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uioFyHCiDHA.4024@.TK2MSFTNGP11.phx.gbl...
> All of this is my experience and personal opinion. YMMV.
> I prefer more CPUs to bigger CPU cache.
> More memory is preferable to faster CPUs, within reason. Unless you are
> running Enterprise Edition, anything over 4GB in the box is wasted so plan
> accordingly. If you are using Enterprise edition, put as much memory as
you
> can afford in the box.
> RAID-10 is three to five times faster than RAID-5. Lots of drive spindles
> is a cheap way to boost performance.
> Definitely use Windows Server 2003 as the OS.
> Since you are a Dell shop, you might want to check out the CX-200
> entry-level SAN. It is a bit more than a SCSI Powervault, but not
> prohibitively so. I would seriously look into the best PowerEdge 6650 I
> could afford with a CX-200 back-end. If not, maybe a 6600 with 12
internal
> SCSI drives. Obviously , we all have budget constraints but definitely
> squeeze every bit you can for this.
> --
> Geoff N. Hiten
> SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
>
> "digitalfish" <digital.fish@.ntlworld.com> wrote in message
> news:gxyeb.2143976$Bf5.300990@.news.easynews.com...
> > Our current SQL server box is a Dell PowerEdge 4400 with one PIII Xeon
> > 800Mhz CPU and 768MB RAM. Some of the tables we have are quite large
(~50
> > million rows) and as of late we have noticed a degradation in
performance.
> > As we will be needing a new server to host our new website, we have
> decided
> > to relegate the 4400 to web hosting and buy a new SQL server box. I know
> > there is time to be spent running profiler / perfmon etc to track down
the
> > source of the performance problems, but we WILL be needing a new box
> first,
> > then I can start to diagnose the perf. issues.
> >
> > What I would like to know is what factors are MOST important when
> > considering a machine to run SQL server i.e. CPU Mhz, Dual/Quad
processor,
> > RAM, On-chip cache etc. I know the best advise is usually "get the best
> > machine you can afford" but when weighing up the options there are some
> > things that have to be decided on an either/or basis i.e. a one-CPU
> machine
> > with 1MB cache, or a dual-CPU machine with 512KB cache.
> >
> > Many thanks for any input,
> > df
> >
> >
>|||Tough call. Most 4+ way systems now have 1MB cache except on the very
fastest processors which have 2MB cache, so you really don't get a tradeoff
there. It is either faster AND more cache or not. As an example, a Dell
PowerEdge 6600/6650 can currently have either 2.5GHz processors with 1MB
cache or 2.8GHz processors with 2MB cache. If you have to compromise, back
off the processor speed/cache slightly and get more RAM and a better disk
subsystem.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:%23wDjVyJiDHA.2120@.TK2MSFTNGP10.phx.gbl...
> Geoff;
> I agree with you on the general choice between more CPUs and bigger CPU
> cache, and between more memory and faster CPUs.
> What would be your choice between faster CPUs and more CPU cache? In total
> abstract and in general, my perference has been more CPU cache. Obviously,
> I'm not talking about going from 400MHz to 2.6GHz.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uioFyHCiDHA.4024@.TK2MSFTNGP11.phx.gbl...
> > All of this is my experience and personal opinion. YMMV.
> >
> > I prefer more CPUs to bigger CPU cache.
> > More memory is preferable to faster CPUs, within reason. Unless you are
> > running Enterprise Edition, anything over 4GB in the box is wasted so
plan
> > accordingly. If you are using Enterprise edition, put as much memory as
> you
> > can afford in the box.
> > RAID-10 is three to five times faster than RAID-5. Lots of drive
spindles
> > is a cheap way to boost performance.
> > Definitely use Windows Server 2003 as the OS.
> >
> > Since you are a Dell shop, you might want to check out the CX-200
> > entry-level SAN. It is a bit more than a SCSI Powervault, but not
> > prohibitively so. I would seriously look into the best PowerEdge 6650 I
> > could afford with a CX-200 back-end. If not, maybe a 6600 with 12
> internal
> > SCSI drives. Obviously , we all have budget constraints but definitely
> > squeeze every bit you can for this.
> >
> > --
> > Geoff N. Hiten
> > SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> >
> >
> > "digitalfish" <digital.fish@.ntlworld.com> wrote in message
> > news:gxyeb.2143976$Bf5.300990@.news.easynews.com...
> > > Our current SQL server box is a Dell PowerEdge 4400 with one PIII Xeon
> > > 800Mhz CPU and 768MB RAM. Some of the tables we have are quite large
> (~50
> > > million rows) and as of late we have noticed a degradation in
> performance.
> > > As we will be needing a new server to host our new website, we have
> > decided
> > > to relegate the 4400 to web hosting and buy a new SQL server box. I
know
> > > there is time to be spent running profiler / perfmon etc to track down
> the
> > > source of the performance problems, but we WILL be needing a new box
> > first,
> > > then I can start to diagnose the perf. issues.
> > >
> > > What I would like to know is what factors are MOST important when
> > > considering a machine to run SQL server i.e. CPU Mhz, Dual/Quad
> processor,
> > > RAM, On-chip cache etc. I know the best advise is usually "get the
best
> > > machine you can afford" but when weighing up the options there are
some
> > > things that have to be decided on an either/or basis i.e. a one-CPU
> > machine
> > > with 1MB cache, or a dual-CPU machine with 512KB cache.
> > >
> > > Many thanks for any input,
> > > df
> > >
> > >
> >
> >
>|||Minimum 2 CPU's, 2 GB RAM.
Preferred:
4 CPU's, 4-8 GB RAM. RAID array.
CPU speed or cache does not matter nearly as much at having 2 or more and
having lots of RAM, and a RAID array. RAID 5 or 10.
In order of preference - price vs performance
2 CPU
2 GB RAM
RAID 5
4 GB RAM
4 CPU's
RAID 10
J
www.urbanvoyeur.com
"digitalfish" <digital.fish@.ntlworld.com> wrote in message
news:gxyeb.2143976$Bf5.300990@.news.easynews.com...
> Our current SQL server box is a Dell PowerEdge 4400 with one PIII Xeon
> 800Mhz CPU and 768MB RAM. Some of the tables we have are quite large (~50
> million rows) and as of late we have noticed a degradation in performance.
> As we will be needing a new server to host our new website, we have
decided
> to relegate the 4400 to web hosting and buy a new SQL server box. I know
> there is time to be spent running profiler / perfmon etc to track down the
> source of the performance problems, but we WILL be needing a new box
first,
> then I can start to diagnose the perf. issues.
> What I would like to know is what factors are MOST important when
> considering a machine to run SQL server i.e. CPU Mhz, Dual/Quad processor,
> RAM, On-chip cache etc. I know the best advise is usually "get the best
> machine you can afford" but when weighing up the options there are some
> things that have to be decided on an either/or basis i.e. a one-CPU
machine
> with 1MB cache, or a dual-CPU machine with 512KB cache.
> Many thanks for any input,
> df
>