Hight paging activities on the DB server

Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobing
Try tracing those counters on a per-process basis. It'll point you toward
where the problem lies.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"xy" <xyu@.redfig.com> wrote in message
news:693901c4758f$069f8ff0$a601280a@.phx.gbl...
Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobing