Showing posts with label paging. Show all posts
Showing posts with label paging. Show all posts

Monday, March 19, 2012

memory usage/allocation/paging question

Are there any *negative* consequences to SQL Server 2000 Standard
paging to disk more often if one reduces the amount of available RAM
from 2.0 GB to 1.5 GB to give the OS and other apps enough RAM, other
than things possibly being "slower" and whatever wear and tear could
happen to the disk drives??

Or is it better to add in more RAM per se to bring the server up to 4
GB (Win2k3 Svr SP1)?? (it has a 4GB pagefile)

Is any information available about judging how much RAM the OS (Windows
2003 Server) should have available?? Does IIS require a lot of RAM??

Thank you, Tomtlyczko (tlyczko@.gmail.com) writes:
> Are there any *negative* consequences to SQL Server 2000 Standard
> paging to disk more often if one reduces the amount of available RAM
> from 2.0 GB to 1.5 GB to give the OS and other apps enough RAM, other
> than things possibly being "slower" and whatever wear and tear could
> happen to the disk drives??
>
> Or is it better to add in more RAM per se to bring the server up to 4
> GB (Win2k3 Svr SP1)?? (it has a 4GB pagefile)
>
> Is any information available about judging how much RAM the OS (Windows
> 2003 Server) should have available?? Does IIS require a lot of RAM??

SQL Server is designed to get as much memory it can get and with a
reason: the more data and execution plans SQL Server can hold in cache,
the better it will perform.

If other applications on the same box calls for more memory, SQL Server
will yield, but it may not yield fast enough, so if that is a concern
it may be an idea to constraint the amount of memory that SQL Server
can get.

Then again, this is mainly something would do on a developer machine,
where you don't want broswers, Visual Studio, Word or whatever to be
swapped out, because you run a query from hell. Constrainting the amount
of memory box, is like driving your BMW on the third gear only.

If you are running IIS on the same box, you should either move IIS
to another box (I have no idea how much memory IIS needs, but it's
probably more than 100 MB than less) or get more memory. SQL Server
2000 Standard Edition can only use 2 GB of memory, so if you add 4GB
and turn on the /3GB switch, there will be some breathing room for
IIS.

I should add that the database size matters a bit. If your database
is small, say 1GB, it will fit entirely in cache, and you may need
all 2GB for memory.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, February 24, 2012

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAM
What is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAMWhat is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>

Memory paging out noted in errorlog

As my SQL Server starts, in the SQL error log, there is a message as
follows:
A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
37376, committed (KB): 105592, memory utilization: 35%.
Do I have to be concerned ? I have 64 bit and 16GB of RAMWhat is min/max server memory settings? Are there other processes running
on the server that are eating up memory? Paging in general is horrible for
performance.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OtgJd4CJIHA.3672@.TK2MSFTNGP02.phx.gbl...
> As my SQL Server starts, in the SQL error log, there is a message as
> follows:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 37376, committed (KB): 105592, memory utilization: 35%.
> Do I have to be concerned ? I have 64 bit and 16GB of RAM
>

Memory Paging Issue

We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I know
that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
shown below:
Averaged of 24 hour period:
Page Reads/sec: 39.287
Pages Input/sec: 438.466
I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
2000 Enterprise Edition. I have now been asked to give management some idea
of when this configuration will be to limited and prove to them that this
will eliminate the paging issues.
Anybody have any ideas on how I can intelligently give Management this
information? Does Microsoft have any Capacity Planning White Papers?First, what happens to the application when it starts
paging? What behaviors do you notice? Have you done any
other monitoring?
Since SQL 2000 is a different beast than SQL 7, start your
numbers based on what you see in SQL 7 - with N number of
users doing X, Y, and Z type of work, we max out at 4
proc/2 GB for SQL. But that really is a guesstimate.
What you need to honestly do is understand why your app is
paging - is the code written poorly? Sure, throwing new
OS, SQL, and HW at it may solve your problem, but it may
not. You should definitively prove that. As much as I
love SQL 2K, moving for the sake of moving is never a good
idea.
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||sql server by itself should not cause paging,
are there other applications that could be generating the
paging activity?
are there extended stored procs?
if there a large difference between the memory usage and
virtual memory in task manager?
have you checked the perfmon counters for Process - sql
servr Page File Bytes and Page Faults?
I like many of the features in SQL Server 2000, but agree
with Allan
in any case the SQL Serve version should not be related to
the paging,
your SQL instance may certainly benefit from more memory,
but should show in the physical disk counters, not the
memory paging counters
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||I do know that the code is very poorly written and there are over 200
databases on the server. I have recommended that they need to move some of
the databases to a different server. All 200+ are heavily used. I recently
counted over 2 million queries/Stored Procs that were run in a 1 hour
period. The only thing running on the server is SQL. I have monitored CPU
and it is actually in good shape, the only problem is paging and during the
periods of paging, extreme slowness. So far I have been unable to corelate
the paging with any particular query/stored proc.
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:010f01c3be76$2731a850$a301280a@.phx.gbl...
> First, what happens to the application when it starts
> paging? What behaviors do you notice? Have you done any
> other monitoring?
> Since SQL 2000 is a different beast than SQL 7, start your
> numbers based on what you see in SQL 7 - with N number of
> users doing X, Y, and Z type of work, we max out at 4
> proc/2 GB for SQL. But that really is a guesstimate.
> What you need to honestly do is understand why your app is
> paging - is the code written poorly? Sure, throwing new
> OS, SQL, and HW at it may solve your problem, but it may
> not. You should definitively prove that. As much as I
> love SQL 2K, moving for the sake of moving is never a good
> idea.
>
> >--Original Message--
> >We currently are using SQL 7.0 Standard Edition w/3 gigs
> of ram (yes, I know
> >that SQL is only using 2 gigs) and 4 cpu's. I am having
> paging issues as
> >shown below:
> >
> >Averaged of 24 hour period:
> > Page Reads/sec: 39.287
> > Pages Input/sec: 438.466
> >
> >I have recommended we go to a server with 8 gigs of ram,
> 8 cpu's and SQL
> >2000 Enterprise Edition. I have now been asked to give
> management some idea
> >of when this configuration will be to limited and prove
> to them that this
> >will eliminate the paging issues.
> >
> >Anybody have any ideas on how I can intelligently give
> Management this
> >information? Does Microsoft have any Capacity Planning
> White Papers?
> >
> >
> >.
> >|||SQL should not page in that configuration if it's the only app on there.
Are you seeing high disk queuing on the page file disk and have correleted
spikes in those counters with performance problems as well? If not, I
wouldn't be overly concerned about those numbers.
A couple things that causes fairly steady paging I have seen is file server
or disk-tape backup activities, but those generally won't cause severe
performance problems.
"Mike Johnson" <mj@.noemail.com> wrote in message
news:%23V8An$mvDHA.2464@.TK2MSFTNGP12.phx.gbl...
> We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I
know
> that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
> shown below:
> Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
> I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
> 2000 Enterprise Edition. I have now been asked to give management some
idea
> of when this configuration will be to limited and prove to them that this
> will eliminate the paging issues.
> Anybody have any ideas on how I can intelligently give Management this
> information? Does Microsoft have any Capacity Planning White Papers?
>

Memory Paging

I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
that is almost constantly paging OS processes (>500pg/sec 6-10 times an
hour).
Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major paging
culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have to
have the MOM stuff on there.
What are the other 2, and do they need to be running on a box dedicated to
SQL?
Any other ideas for helping me lower my paging rate?
The paging is really not that excessive. But in order to reduce paging you
need to ensure there is always enough memory for the apps other than SQL
Server as well as the OS. So 2GB might not be enough to satisfy these other
apps. I don't know how much MOM requires but I am suppressed the 2GB is not
enough for these.
Andrew J. Kelly SQL MVP
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?
|||wmiprvse is a process that is an instance of a wmi provider
(there are some trojans/worms that hide in this proccess also)
but it is more likely mom is using this provider.
RemoteRegistry does what its name implies, it lets users access the registry
remotely....it is a service which CAN be disabled...but i dont know about
moms dependencies on this either.
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?

Memory Paging

I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
that is almost constantly paging OS processes (>500pg/sec 6-10 times an
hour).
Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major paging
culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have t
o
have the MOM stuff on there.
What are the other 2, and do they need to be running on a box dedicated to
SQL?
Any other ideas for helping me lower my paging rate?The paging is really not that excessive. But in order to reduce paging you
need to ensure there is always enough memory for the apps other than SQL
Server as well as the OS. So 2GB might not be enough to satisfy these other
apps. I don't know how much MOM requires but I am suppressed the 2GB is not
enough for these.
Andrew J. Kelly SQL MVP
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?|||wmiprvse is a process that is an instance of a wmi provider
(there are some trojans/worms that hide in this proccess also)
but it is more likely mom is using this provider.
RemoteRegistry does what its name implies, it lets users access the registry
remotely....it is a service which CAN be disabled...but i dont know about
moms dependencies on this either.
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?

Memory Paging

I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
that is almost constantly paging OS processes (>500pg/sec 6-10 times an
hour).
Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major paging
culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have to
have the MOM stuff on there.
What are the other 2, and do they need to be running on a box dedicated to
SQL?
Any other ideas for helping me lower my paging rate?The paging is really not that excessive. But in order to reduce paging you
need to ensure there is always enough memory for the apps other than SQL
Server as well as the OS. So 2GB might not be enough to satisfy these other
apps. I don't know how much MOM requires but I am suppressed the 2GB is not
enough for these.
--
Andrew J. Kelly SQL MVP
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?|||wmiprvse is a process that is an instance of a wmi provider
(there are some trojans/worms that hide in this proccess also)
but it is more likely mom is using this provider.
RemoteRegistry does what its name implies, it lets users access the registry
remotely....it is a service which CAN be disabled...but i dont know about
moms dependencies on this either.
"Budman" <Budman@.discussions.microsoft.com> wrote in message
news:F55C30B9-B696-4303-8FF4-F9B51E0F49FF@.microsoft.com...
>I have a SQL Server 2000 running on Windows 2003 (both Enterprise Editions)
> that is almost constantly paging OS processes (>500pg/sec 6-10 times an
> hour).
> Its a 4 proc box, and I have 10GB RAM (8 to SQL; 2 to OS). The major
> paging
> culprits are MOM Hosts and Services, wmiprvse, and RemoteRegistry. I have
> to
> have the MOM stuff on there.
> What are the other 2, and do they need to be running on a box dedicated to
> SQL?
> Any other ideas for helping me lower my paging rate?