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 paging but plenty of memory available

I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.
DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com

High paging but plenty of memory available

I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" => 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com

High paging but plenty of memory available

I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on th
e
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com