469,623 Members | 1,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

I/O Congestion Troubleshooting: Memory and Swap File Usage Question

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?

Thanks in advance for any assistance.
Jul 20 '05 #1
1 4154
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/de...izing_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.co...ing_review.asp

John

"Jeff Roughgarden" <jr**********@stanfordalumni.org> wrote in message
news:b8**************************@posting.google.c om...
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?

Thanks in advance for any assistance.

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by rbt | last post: by
7 posts views Thread by Felix E. Klee | last post: by
50 posts views Thread by Joerg Schwerdtfeger | last post: by
4 posts views Thread by Douglas | last post: by
8 posts views Thread by Gawain Lavers | last post: by
5 posts views Thread by hamishd | last post: by
12 posts views Thread by Tony | last post: by
17 posts views Thread by frederic.pica | last post: by
10 posts views Thread by Daniel Peterson | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.