Re: SQL Server 2000 Hardware Recommendations?

From: Lorax (folkfest_at_comcast.net)
Date: 07/09/04 

Date: 9 Jul 2004 08:39:40 -0700

Thanks to the both of you for your input. We're using adhoc sql calls,
but the compilations/sec is very low, and the cache hit ratio is
nearly 100% (99.7%, I believe off the top of my head). So what you are
both saying has the ring of truth--that we really need to focus in on
how our SQL queries are structured. Unfortunately we don't have a lot
of experience in the area of optimizing SQL. Aside from obviously
searching the web for information, can you recommend any resources in
this area?

Thanks again for your valuable input.

Gary

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:<e72qCFRZEHA.3228@TK2MSFTNGP12.phx.gbl>...
> 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

  • Re: SQL Server 2000 Hardware Recommendations?
    ... Hardware Performance CheckList ... Andrew J. Kelly SQL MVP ... >> trace and see what is taking up all the resources especially cpu. ... Currently the database server houses all data pertinent to ...
    (microsoft.public.sqlserver.server)
  • Re: commit work not working?
    ... Because he is using straight sql, ... The transaction is made up of the statements that occur ... locks the stock table, updates rows in the stock table ... The database server must perform this sequence of operations either ...
    (comp.databases.informix)
  • RE: Sync DB SQL Server CE and remote MySQL on internet
    ... Safe to say there is no chance of native replication from SQL CE ... from the Pocket PC client to the database server is your best bet for ... across multiple devices, but in most cases this is not too hard to figure out ... i have a local db in the PPC device and MySQL DB on the ...
    (microsoft.public.pocketpc.developer)
  • Re: How do I query a dataset?
    ... There is no real SQL support there. ... > I get how to populate a dataset by querying a database server... ... > In some cases I'd say this is preferable to hitting the database server ... > As an example if I have a populated table of employee data in a dataset ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MSCS Questions
    ... hardware won't necessarily speed things up. ... > server solution than we had, including more memory and faster processors. ... > I understand that clustering isn't a performance benefit, ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.server)