Re: SQL Server 2000 Hardware Recommendations?

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/08/04 

Date: Thu, 8 Jul 2004 13:30:21 -0400

That's hard to say what is the cause but it soulds like you have some very
poorly optimized sql code. Are you using a lot of cursors? Run a profiler
trace and see what is taking up all the resources especially cpu. What is
your cache hit ratio? Are you using stored procedures or adhoc sql calls?
Check your compilations per second counters as well. 

-- 
Andrew J. Kelly  SQL MVP
"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.
>
> In a nutshell, our database server appears to be struggling during
> performance testing of the new Web site. We are trying to determine
> whether we simply need new hardware, or if there are things we can do
> to help MS SQL make better use of the resources we have. 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. The Web
> server is a 2.8ghz Pentium IV with 2.5gb RAM, RAID 5 with 3 HD (15,000
> rpm??) running Windows 2000 standard and IIS 5.0. 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. Memory and disk access
> appear to remain fairly stable -- there isn't a lot of paging going
> on, and the disk queue doesn't escalate much if any. The Web server
> shows spikes in processor use, but appears to be coping well. However,
> under a heavy load, a sql-heavy page can take as long as 90 seconds to
> load! We've been assuming that the network is not the issue, as the
> servers are communicating over a gigibit backbone and while we've
> identified aspects of the ASP code that we can optimize, the database
> server seems to be a large part of the problem.
>
> We've reviewed our SQL configuration settings, and they appear to
> align with the best practices, which in our case are the default
> settings for SQL 2000. We have rebuilt our indexes, and have
> defragmented the hard disks on both the database and Web servers.
> This, along with changes to the structure of the Web pages themselves,
> has led to improvements, but the processor on the database server
> seems to be groaning under the strain, and pages are still taking an
> unacceptable amount of time to load.
>
> What else should we be looking at? Are there steps we could take to
> minimize the load generated by client/server and Web-related traffic,
> or specific performance counters that would help us to identify the
> problem? Do we just need to look at getting some new hardware? If new
> hardware is unavoidable, is there anyone running a similar environment
> who could suggest what minimum requirements we should be looking for?
> Any suggestions would be much appreciated!

Relevant Pages

  • 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)
  • Re: new guy needs some help
    ... I was looking at that actually, but I don't see were I can use my SQL ... database with the 2.0 logon control to validate the users. ... I'm new to asp.net 2.0 and I'm working on creating a web site that allows ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: new guy needs some help
    ... against my user and role tables in my current database. ... database with the 2.0 logon control to validate the users. ... using SQL db, ... user to log into the web site and view information pertaining to them ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: 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)