Re: SQL Server 2000 Hardware Recommendations?

From: David Browne (meat_at_hotmail.com)
Date: 07/08/04 

Date: Thu, 8 Jul 2004 12:10:35 -0500

"Lorax" <folkfest@comcast.net> wrote in message
news:8973f217.0407080849.434055df@posting.google.com...
> I'm on the IS team of a medium-sized non-profit with international
> reach. We're trying to make some decisions regarding our Web server
> and database server as we expand our web site to have more dynamic
> content. Currently the database server houses all data pertinent to
> the organization (membership data, events, products, etc) in one
> database (~2.2 GB) as well as the web site content in a separate
> database (~40 MB). The web site pulls from both databases but hits the
> content database more often

>The hardware is a COMPAQ ML370, 1266mhz Pentium III, 1gb RAM, RAID 5 with 3
HD
>(10,000rpm??) and a COMPAQ Smart Array 5i SCSI controller. The OS is
>Windows 2000 (standard) running Microsoft SQL 2000, SP 3a.
. . .

>While stress testing our web site under a moderate load (simulating
approximately 20
>simultaneous users), the database server processor tends to max out
>and stay that way for the duration of the test.
. . .
>under a heavy load, a sql-heavy page can take as long as 90 seconds to
load!

This is a gigantic red flag. And it suggests that a hardware upgrade may be
an expensive and frustrating experience for you. You could upgrade this box
and increase its CPU resources by about 300% by going to a new 2-way P4 box,
but your CPU would probably still be at 100% and your sql-heavy pages might
just take 20 seconds to load. The problem would not be fixed.

Your current hardware should be more than sufficient for 20 simultaneous
users. And it is completely unreasonable for such a tiny number of users
should cause such a load on the database. You can't fix an
application-induced performance problem with more hardware because there's
no limit to the amount of resources an application can waste.

You need to start profiling your application to see what queries are
generating all that CPU load. Once you've identified the culprets, then you
need to fix them. This may be as easy as adding an index, or it may require
substantial rewriting of application code. There's no easy roadmap
application performance analysis and correction since there's no way to tell
where the trail will lead. But the trail starts with Sql Profiler. Turn it
on and capture all the queries sent to the Sql Server during a typlical
client session. Use Query Analyzer to examine the execution plans and the
statistics to find out which ones are using a lot of resources. Then go
from there.

David

Relevant Pages

  • Re: SQL Server 2000 Hardware Recommendations?
    ... Are you using stored procedures or adhoc sql calls? ... > and database server as we expand our web site to have more dynamic ... Currently the database server houses all data pertinent to ...
    (microsoft.public.sqlserver.server)
  • Re: Is file is being used
    ... The file system doesn't know anything about the content of the .mdb file. ... That said, most database do support multiple users, and they do so by ... Note that even in the case of a database server supporting multiple ...
    (microsoft.public.dotnet.languages.csharp)
  • SQL Server 2000 Hardware Recommendations?
    ... and database server as we expand our web site to have more dynamic ... Currently the database server houses all data pertinent to ... under a heavy load, a sql-heavy page can take as long as 90 seconds to ...
    (microsoft.public.sqlserver.server)
  • IIS/SQL Server Hardware Recommendations?
    ... and database server as we expand our web site to have more dynamic ... Currently the database server houses all data pertinent to ... under a heavy load, a sql-heavy page can take as long as 90 seconds to ...
    (microsoft.public.inetserver.iis)
  • Good case for VMS
    ... Anyone else notice that the BOINC version of Seti@Home is down? ... We are still dealing with some database ... until we get at least one database server up and running. ... dump all the data from the replica back onto the master. ...
    (comp.os.vms)
Loading