I'm working on a performance problem on a SQL server 2000 W2K SP4
Active / Active cluster. The primary database (bigdb) is 140 Gb and
has 1200 users. Looking at perfmon I am suspecting an I/O bottleneck.
The 8 CPUs in the system run all under 50%, and the 4 Gb RAM shows
only 3.2 Gb in use. I'm an old Windows guy, but new to SQL
performance tuning. The system is all up to par with patches etc.

The disk is a screaming fast SAN with great cache. The databases are
all on a single drive mapped to H: for this instance. Here's some
results from a ~20 hr perfmon analysis with ~4500 values of drive H:

Counter Average Min Max
Disk Bytes / sec: 4,406,000 52,000 39,912,000
Disk Queue Length: 3.50 0 255
%Disk Time: 344 0.12 15,500

So, I'm wondering if separating the databases onto different drive
letters would help disk time and queue length. For example: tempdb on
H:, bigdb-data on I:, bigdb-index on J:, bigdb-logs on K: and master
etc. on J:. All of these would still on the same san fiber, but maybe
W2K would do a better job managing the data using multiple disks to
the same SAN? Thoughts?

Are there any tuning tweeks I can make to W2K to give more priority to
the SQL SAN drive? The internal drives are not have any issues...

Hi

The disk queues do not look too good.
What type of SAN is it? EMC? Depending on the SAN technology, the disk
layout can play a big role. RAID-10 preferably.
It does not help splitting up the volume into different volumes / drive
letters, unless the volumes are going to be on different spindles.
The Transaction log writes are generally sequential, whilst the database is
usually random. Having the same disks try to service both types of requests
leads to terrible disk queue lengths.

The OS can't do much more to help the SAN drives, they are the ones not
coping.

With 4GB or RAM and 32 Bit OS, SQL Server can only use up to 3GB and the OS
gets the other 1GB (assuming /3GB is in boot.ini) Considering the load on
the server, 4GB is a bit little. 8GB would start to sound right, 16 might
become very sweet. With more RAM, SQL Server can cache more, resulting in
less reads being required. In effect, you have too many processors for the
RAM.

There are other factors that can bury the best hardware. Bad database and
index design, and terrible queries. e.g bad indexing results in SQL Server
having to read though all the data in a table, this causes more read
requests. this slows down writes, which in turn slow down reads....on and on
and on.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike (AT) epprecht (DOT) net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

<paulaner (AT) iname (DOT) com> wrote