Performance Questions on SQL 2000 from perfmon

I am a new admin at our office and I am trying to get a handle on the SQL
systems running.
I followed a few online guides to performance monitoring and I have a few
concerns that I wanted to run past some experts.
This SQL server is a dual PIII 1.4 GHz with 2 GB of RAM.
So far I have monitored a few SQL specific counters along with the proc and
memory. Under normal load, everything has seemed good so far, but when we
run reports, the SQL server goes under a heavy load that appears to be
stressing the system. Under Task Manager, the procs spike a lot (not a
curtain though) and the server becomes sluggish. This is what I have
discovered so far from perfmon:
1) Under the processor queue length, the graph is spiking over 200, but it
lists a maximum of 49 on the chart. Either way, this seems to be pretty
high.
2) Total proc time is averaging at around 50%
3) The sqlserver Working Set is at 1.6 GB
4) Pages/Sec frequently exceeds 50 with an average at 20 and a maximum of
755
5) Available MBytes is at 10 - 16
6) Page File % usage is at 38%
7) Non-Paged Pool Memory was around 26 MB
During heavy load, I also lost some collection of data via perfmon at 5
second intervals. My interpretation from this is that SQL has sucked up all
available RAM and wants more. I have not rebooted to see how this effects
the server. The proc's seem to be overloaded under heavy load with the
queue length filing up, but an avg of 50% shouldn't need more processing.
Also I attribute the loss of data collection to the high queue length.
What are possible resolutions? Do I need to collect more data? I suspect
that SQL is not using the 1.6 GB assigned to it very effectively. Is it
possible that some of the applications we have running are somehow either
causing a memory leak, or locking the memory in SQL? If that is an issue,
how can I detect what memory inside SQL is locked/wasted?
Thank You for any assistance,
Kevin Hammonddatabase engines are essentially a disk cache, so it so
should be using all the memory it needs if it is
available, otherwise, it would have to go to disk for the
data,
if you are concerned as to whether SQL server really needs
all that memory, monitor disk read/sec, writes/sec, avg
disk queue len, avg sec/read and avg sec/write for the
physical disk containing the main data files (not the log)
then try reducing the amount of memory SQL Server can use,
if the disk counters rise, then SQL Server was making good
use of that memory
>--Original Message--
>I am a new admin at our office and I am trying to get a
handle on the SQL
>systems running.
>I followed a few online guides to performance monitoring
and I have a few
>concerns that I wanted to run past some experts.
>This SQL server is a dual PIII 1.4 GHz with 2 GB of RAM.
>So far I have monitored a few SQL specific counters along
with the proc and
>memory. Under normal load, everything has seemed good so
far, but when we
>run reports, the SQL server goes under a heavy load that
appears to be
>stressing the system. Under Task Manager, the procs
spike a lot (not a
>curtain though) and the server becomes sluggish. This is
what I have
>discovered so far from perfmon:
>1) Under the processor queue length, the graph is
spiking over 200, but it
>lists a maximum of 49 on the chart. Either way, this
seems to be pretty
>high.
>2) Total proc time is averaging at around 50%
>3) The sqlserver Working Set is at 1.6 GB
>4) Pages/Sec frequently exceeds 50 with an average at 20
and a maximum of
>755
>5) Available MBytes is at 10 - 16
>6) Page File % usage is at 38%
>7) Non-Paged Pool Memory was around 26 MB
>During heavy load, I also lost some collection of data
via perfmon at 5
>second intervals. My interpretation from this is that
SQL has sucked up all
>available RAM and wants more. I have not rebooted to see
how this effects
>the server. The proc's seem to be overloaded under heavy
load with the
>queue length filing up, but an avg of 50% shouldn't need
more processing.
>Also I attribute the loss of data collection to the high
queue length.
>What are possible resolutions? Do I need to collect more
data? I suspect
>that SQL is not using the 1.6 GB assigned to it very
effectively. Is it
>possible that some of the applications we have running
are somehow either
>causing a memory leak, or locking the memory in SQL? If
that is an issue,
>how can I detect what memory inside SQL is locked/wasted?
>Thank You for any assistance,
>Kevin Hammond
>
>.
>