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

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
XiaobingTry 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

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
XiaobingTry 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
Xiaobingsql

High CPU utilization on distributor and sp_MSget_repl_commands

Hi,

In SQL 2005 Replication Monitor i was not seeing details for any of the publications on the "Distributor to Subscriber Histroy Tab" so i decided to stop and start synchronisation on this one publication. At this time there were approximayely 20000 undistributed commands. After the stop/start of the distribution agent, i started seeing messages like "x trasactions with x commands were delivered". Then i went and restarted all the other distribution agents using the Replication Monitor.

Has anyone experienced this kind of a behaviour?

The second issue is that our trasnactional replication looked to have caught up but i was supprised find that the distribution server was running at 100%. A profiler trace of the distribution database revealed that sp_MSget_repl_commands procedure was being executed and costing approximately in excess of 400 000 reads, 7000 in CPU cost and 15sec in duration. To me it looked as if sp_MSget_repl_commands has chosen an inefficient execution plan but then realised i couldn't recompile system procedures. I think a stop and start of the SQL instance is the only option i have.

PK

You can't recompile the proc itself, but you can force a recompile by updating the stats on the table. This should in theory trigger a recompile for the proc, and I'm sure it applies to procs in the resource db as well, but don't quote me on it.

Do you know if it was the replication monitor or the distribution agent that was making the proc call you were profiling? In pre-RTM days, there was a performance bug for the call made by the replication monitor, making it very expensive. I assume you're on RTM version of SQL 2005?

Without knowing what the plan looks like for the queries inside the proc, it's hard to say what the problem is.