frequent periods of slow response

We are running SQL 7.0 on a quad-pentium (? MHz)server.
We have 10-12 production databases on the server, a couple
of them, financial/personnel & Prosecutor Dialog
(atty/courts)) are used quite heavily, but still should
not be enough to bring processing to a stop, quite often.
The system runs OK most of the time (50-60%), slow (20-30)
and dog-slow (10-20%). I have ran performance monitor &
cannot see any coorelation between any values there &
response. I'm not sure of our current patch levels (OS-
W2000 or SQL7.0). Tech mgr. says it appears that one or
more programs is starting & then not releasing the memory
back. Main memory fills and then swap space fills. If
this is the case, something clears the space, because the
system does slow down & sometimes stops for 30-60-180
seconds, but then speeds up, slow & quick, slow, stop &
quick, over & over. No pattern during operations hours,
and no specific processing going on, large searches, etc.
How can I identify which program(s) may be hogging memory,
or what other potential problems could there be, e.g.
behind on patch levels, server and/or SQL7.0 optimization
variables not set correctly, whatever. Any ideas on where
to keep looking to resolve significant response problem.If you've got a paging problem, you've likely got a fundamental resource
allocation / configuration problem on that server.
In an attempt to make an easy kill, I'd first use the task manager to look
for processes with excessive memory usage during a period of slow
responsiveness. You could set up an alert to inform you when this occurs
immediately so you can get to the console in time, or perhaps even start a
log.
You should trace the PagingFile object under a SystemMonitor (perfmon if
you're under NT4) log & check it after periods of slow response. This will
confirm whether you have a paging problem. It's not uncommon for SQL Server
to consume the available memory on the server (as it should) but then some
other application require memory unexpectedly. This is sometimes as simple
as a virus checker or other scheduled app..
I'd also track the Process\Working Set counter for the SQL Server process
instance to see if it's SQL Server causing the problem in a log.
Whatever the case, it sounds like you need to establish which app is causing
the memory usage spike & plan memory allocation on that server more
carefully. It might be a case of setting a max memory config for the SQL
Server so other apps can get at memory when they need it without causing
paging on the server.
HTH
Regards,
Greg Linwood
SQL Server MVP
"errol" <mikeu@.co.davis.ut.us> wrote in message
news:8d8601c37efa$eb26acd0$a601280a@.phx.gbl...
> We are running SQL 7.0 on a quad-pentium (? MHz)server.
> We have 10-12 production databases on the server, a couple
> of them, financial/personnel & Prosecutor Dialog
> (atty/courts)) are used quite heavily, but still should
> not be enough to bring processing to a stop, quite often.
> The system runs OK most of the time (50-60%), slow (20-30)
> and dog-slow (10-20%). I have ran performance monitor &
> cannot see any coorelation between any values there &
> response. I'm not sure of our current patch levels (OS-
> W2000 or SQL7.0). Tech mgr. says it appears that one or
> more programs is starting & then not releasing the memory
> back. Main memory fills and then swap space fills. If
> this is the case, something clears the space, because the
> system does slow down & sometimes stops for 30-60-180
> seconds, but then speeds up, slow & quick, slow, stop &
> quick, over & over. No pattern during operations hours,
> and no specific processing going on, large searches, etc.
> How can I identify which program(s) may be hogging memory,
> or what other potential problems could there be, e.g.
> behind on patch levels, server and/or SQL7.0 optimization
> variables not set correctly, whatever. Any ideas on where
> to keep looking to resolve significant response problem.