Hi,

I see in BOL that 1.5 times the amount of physical RAM is suggested and 3
times if you are running Full Text Search. Is this true for 64-bit as well
as 32-bit SQL2005 thru SQL2008?

Thanks

Chris

If we are talking about modern servers with 16GB, 32GB, or 64GB RAM, that
guideline is most certainly obsolete. Think about it. On a server with 32GB,
if you follow the suggestion, you'd set your swap file to 48GB (32*1.5). And
if you ever come close to actually require that much swap space, there is
certainly something bigger that is wrong and should be addressed first.

Linchi

"Chris Wood" wrote:

Thanks Linchi.

What do you think the swap file should be for say 16Gb, 32Gb and 48Gb of RAM
on a 64 bit server?
I see your point.

Chris

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote

Different places seem to have different standards for this. But I have seen
some standardize to 4GB ~ 8GB. I have seen some set it to evne higherif they
have ample storage space.

I'll check around a few large institutions to see what their enterprise
standard is w.r.t. this, and post back.

Linchi

"Chris Wood" wrote:

The amount of RAM is now totally irrelevant. The concept of a swap file
is to support minimal RAM circumstances (When you have less than 1GB) so
if you have 4GB RAM or more and your CPU needs to use swap files, you
are doing something wrong and need to investigate. Simple as that.

4GB of RAM is a good measure as anything else will almost certainly
never be used. In fact, in a properly tuned environment, I challenge you
to prove your swap file is even used. Some folks would have you disable
it as a waste, preferring instead to know if a process needs to go that
far...

Geoff Schaller
Software Objectives



"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote

For Windows 2003 and earlier, if the paging file is enabled,
then it will be used. My guess is that Server 2008 is similar,
however, I have not studied this aspect of 2008 yet.

The goal should be to have plenty of physical RAM so that
use of the paging file is minimal. I would say on a well-tuned
server the only thing in the paging file would be code/data
that is rarely used, and as such does not slow the server
down.

I prefer to use pstat.exe to see the amount of page file in use.

"Geoff Schaller" <geoffxx (AT) softwareobjectivesxx (DOT) com.au> wrote

Hi Chris,

Please see my other replies. If you are not concerned
about the requirements for a complete memory dump,
my recommendation is to:

1. Leave the paging file setting at default (do nothing)

2. Set the paging file to a size that is what you normally
need + a cushion for unusal circumstance. For example,
say your server has 16GB RAM. You monitor over
time and notice that total committed memory peaks at
about 18GB. So you may decide to set the paging
file at 8GB, which is the 2GB you need plus 6GB of
cushion.

"Chris Wood" <anonymous (AT) microsoft (DOT) com> wrote

Hi Chris,

In a well tuned system it is expected not to use page file at all cause that
gives you best performance...now a days systems if you have a system more
than 32GB of ram than you should be able to tune system in such a way where
no paging happen....(its possible your systems demands way much then
definately you have to go with 1.5 times paging file, then make sure its on a
separate drive altogether)

I suggest enabling Loack pages in memory to avoid database pages getting
swapped to page file...see below...


http://www.tipandtrick.net/2008/enab...aging-to-disk/

if you feel that your performance is bad then run perfmon to see whats going
on and tune the queries by looking into stats DMV's...try not to use page
file if at all possible to get the best out of your system....

and normally 4-8GB of size is good to have memory dump...

Prakash Heda
Sr Infrastructure DBA


"Chris Wood" wrote: