SQL Server 2000 Hardware Recommendations

[Lorax]

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!

[Leythos]

Re: SQL Server 2000 Hardware Recommendations

In article <8973f217.04070 80848.4e48b348@ posting.google. com>,
folkfest@comcas t.net says...
[snip]

Answered in the IIS group - you should really learn how to post to
multiple groups, you'll get better feedback.

--
--
spamfree999@rro hio.com
(Remove 999 to reply to me)

[Erland Sommarskog]

Re: SQL Server 2000 Hardware Recommendations

[posted and mailed, please reply in news]

Lorax (folkfest@comca st.net) writes:[color=blue]
> 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.[/color]

While not a terrifying the server, my gut feeling is that it should be
enough for the moderate load you are mentioning.

You say that you have rebuilt indexes etc, but how much have you looked
at the code itself? It is not unlikely that the performance could be
improved by tuning a couple of common, but ineffecient query. The best
way to find these is to run with Profiler and have a filter that
catches long durations.

Of course, in the end, throwing hardware of the problem may still be a
cheaper way to go, depending on the findings. It could be a case of
adding a single index et voilà! Or it could be that you need to make
a major architectual change in the application.

But you should certainly start aith analysing where the bottlenecks are.
You could save some bucks that way.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Greg D. Moore \(Strider\)]

Re: SQL Server 2000 Hardware Recommendations


"Lorax" <folkfest@comca st.net> wrote in message
news:8973f217.0 407080848.4e48b 348@posting.goo gle.com...[color=blue]
> 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.[/color]

Ok, you most likely have a code problem. Despite thinking you've found it,
I'd look there.

(though I'd probably run the Web server on the SQL box and vice versa).

However, the SQL box is still going to be fast enough. With 1 GB of RAM
most of the data will probably be in cache anyway. And as your disk queue
is low, that's more evidence.

COuple of things: What version of SQL Server are you running?

What's "SQL Heavy" mean? Lots of queries? Lots of inserts?

[color=blue]
> 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![/color]

Well, don't know enough to say if our environment is similar (I'd guess not)
but we run LOTS of queries through our servers (one handles millions a day)
on SQL Servers 4 years old.

[Lorax]

Re: SQL Server 2000 Hardware Recommendations

Thank you both very much for your responses.

Greg, to answer your questions: Our SQL version, we are using
Microsoft SQL 2000 , Standard Edition, SP3a. Our site is SQL-heavy in
the sense that it pulls a lot of information from the
database--meeting information, member information, library resources,
etc are all pulled out of the database. It's fairly infrequent that we
are doing inserts and updates via the web. It's mostly just pulling a
lot of information down using queries embedded in ASP pages, and
displaying that information for the user. Our new web site adds the
element of actually having web page code stored in the database.

Both you, and the previous poster (Erland), seem to agree that queries
are the place to look for optimization. I think our history has
typically involved upgrading hardware when performance becomes an
issue. Do you happen to know of good resources regarding optimization
of SQL queries?

I greatly appreciate your input. Thanks.

Gary

"Greg D. Moore \(Strider\)" <mooregr_delete th1s@greenms.co m> wrote in message news:<XNlHc.428 09$iJ4.17162@tw ister.nyroc.rr. com>...[color=blue]
> "Lorax" <folkfest@comca st.net> wrote in message
> news:8973f217.0 407080848.4e48b 348@posting.goo gle.com...[color=green]
> > 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.[/color][/color]

<<edited for brevity>>
[color=blue][color=green]
> > 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.[/color]
>
> Ok, you most likely have a code problem. Despite thinking you've found it,
> I'd look there.
>
> (though I'd probably run the Web server on the SQL box and vice versa).
>
> However, the SQL box is still going to be fast enough. With 1 GB of RAM
> most of the data will probably be in cache anyway. And as your disk queue
> is low, that's more evidence.
>
> COuple of things: What version of SQL Server are you running?
>
> What's "SQL Heavy" mean? Lots of queries? Lots of inserts?
>
>[color=green]
> > 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![/color]
>
> Well, don't know enough to say if our environment is similar (I'd guess not)
> but we run LOTS of queries through our servers (one handles millions a day)
> on SQL Servers 4 years old.[/color]

[Erland Sommarskog]

Re: SQL Server 2000 Hardware Recommendations

Lorax (folkfest@comca st.net) writes:[color=blue]
> Both you, and the previous poster (Erland), seem to agree that queries
> are the place to look for optimization. I think our history has
> typically involved upgrading hardware when performance becomes an
> issue. Do you happen to know of good resources regarding optimization
> of SQL queries?[/color]

Kalen Delaney's book "Inside SQL Server 2000" is certainly a good book.

http://www.sql-server-performance.com/ is a site that I have not explored
much myself, but the name sounds promising.

But the best way to learn to tune queries is to start working. What I
typically do when I hear the customer complain "the datbase is slow"
is to start the Profiler, and then set up a Profiler trace where filter
on the Duration column to find long-running queries. I also include
Recompile events, which unfortunately does not have a duration in
Profiler, but can be a serious performance killer. (They can boost
performance too!)

I might also use my own aba_lockinfo, which gives me locking and blocking
information, but also the current statement. If the same statement reappears
in several executions, I take that as in indication that this something
to look closer at. You can get aba_lockinfo at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

Once you have found the slow queries, you need to find out how to make
them faster. This can be done by adding/dropping/modifying indexes, adding
supplementary information to help the optimizer, or even adding query
hints. If recompiles are the problem, replacing a temp table with a
table variable is worth a try. Admittedly, at this stage it does help
if you have some basic understanding of how indexes work, and the
difference between clustered and non-clustered indexes. Also, under-
standing the different join methods that SQL Server can use helps. This
is material that is covered by Kalen's book.

Before you feel too overwhelmed by this and start looking for consultants
in the Yellow Pages, SQL Server does offer the Index Tuning Wizard that
help you to find suitable indexes. For ITW to be useful, you need a
good collection of workload for it to analyse. I have never used ITW
myself, mainly because the performance problems at our customer sites
are usually related to a certain function. (We're an ISV, so it's our
system that I analyse.)

Finally, I should say that there more than one way to overload an SQL
Server. Cursor and other iterative processing may not show up in
profiler, because each statement takes hardly no time at all, but
they sum up to a lot. And ineffecient communication from the upper
layers where data is retrived in small pieces for business logic
that would best be implemented in SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp