I/O Congestion Troubleshooting: Memory and Swap File Usage Question -- sql server (alternate)

jroughgarden NO[at]SPAM stanfordalumni.org
2/20/2004 12:51:50 PM

We are hosting a 140 GB database on SQL Server Version 7 and Windows
2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5
SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to the
OS) and a 4 GB swap file. (The PeopleSoft CIS application will not
permit us to upgrade to SQL 2K.) We recently upgraded the server from
4 to 8 cpus and the SAN disks from 10K to 15K drives. But we still
have heavy SAN disk usage, sometimes at 100%, and read queues often
averaging 4 and peaking at 12.

The CPUs are loaded at only 20-50%. (The politics are such that it is
easier to throw hardware at the problems.)

We are looking into archiving, converting from RAID 5 to RAID 10, and
at splitting the mdf file into several file groups in an attempt to
get more disk heads into play. (We are also looking at rewriting the
application to reduce the read volume and frequency.) Does anyone have
any other ideas?

Incidentally, does swapfile get used when the physical memory equals
the OS maximum? If the OS can only see 2 GB and we have 2 GB (actually
4 GB) of memory, is the 4GB local swap file on the C drive unused?

John Bell
2/20/2004 9:20:59 PM

Hi

You don't list upgrading to SQL 2000 as an option!

Your swap file should be rarely used, and you should look at the performance
monitor (which knowing the queue lengths I assume your are) and profiler to
try and locate the bottlenecks. More disks/filegroups should help, even more
disks in the raid array would help if you don't have the throughput, but you
will need to do some analysis (see book below!).

The following may be useful but are aimed at SQL 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agoptimizing_5lt1.asp

You may also want to look at:
http://www.sql-server-performance.com/

This is an excellent read:
http://www.sql-server-performance.com/sql_server_2000_performance_tuning_review.asp

John

[quoted text, click to view]

sql server (alternate) : I/O Congestion Troubleshooting: Memory and Swap File Usage Question