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