Pagefile for SQL Server

How important it is to work or configure pagefile for SQL Server ? We have
never bothered to look into it, but was just wondering if there are any
performance enhancements one could make by working on pagefile settings?Hi Hassan
For a server dedicated to SQL Server you should not see a great deal of
paging to the page file, but that does not mean that you should not still
follow best practices such as putting the page file on it's own dedicated
fast disc or making it large enough to hold the memory dump.
John
"Hassan" wrote:
> How important it is to work or configure pagefile for SQL Server ? We have
> never bothered to look into it, but was just wondering if there are any
> performance enhancements one could make by working on pagefile settings?
>
>|||> best practices such as putting the page file on it's own dedicated
> fast disc
I'm not sure this is really best practice from a practical viewpoint. I
wonder how many people do this. That's not the practice I have encountered.
Pagefiles are typically put on the system drives (customarily C and D in most
cases), which are typical a two-disk mirrored set.
Linchi
"John Bell" wrote:
> Hi Hassan
> For a server dedicated to SQL Server you should not see a great deal of
> paging to the page file, but that does not mean that you should not still
> follow best practices such as putting the page file on it's own dedicated
> fast disc or making it large enough to hold the memory dump.
> John
> "Hassan" wrote:
> > How important it is to work or configure pagefile for SQL Server ? We have
> > never bothered to look into it, but was just wondering if there are any
> > performance enhancements one could make by working on pagefile settings?
> >
> >
> >|||Hi Linchi
In my experience it does tend to be the first compromise, but that may be
because it isn't even considered!
John
"Linchi Shea" wrote:
> > best practices such as putting the page file on it's own dedicated
> > fast disc
> I'm not sure this is really best practice from a practical viewpoint. I
> wonder how many people do this. That's not the practice I have encountered.
> Pagefiles are typically put on the system drives (customarily C and D in most
> cases), which are typical a two-disk mirrored set.
> Linchi
> "John Bell" wrote:
> > Hi Hassan
> >
> > For a server dedicated to SQL Server you should not see a great deal of
> > paging to the page file, but that does not mean that you should not still
> > follow best practices such as putting the page file on it's own dedicated
> > fast disc or making it large enough to hold the memory dump.
> >
> > John
> >
> > "Hassan" wrote:
> >
> > > How important it is to work or configure pagefile for SQL Server ? We have
> > > never bothered to look into it, but was just wondering if there are any
> > > performance enhancements one could make by working on pagefile settings?
> > >
> > >
> > >

Pagefile and Paging

My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
solution. He believes the pagefile settings should be 1.5 times the amount
of physical RAM which I agree but I seem to find it hard to correlate paging
with pagefile increase.
Also under what conditions would one need ot consider increasing the size of
the pagefile if its not set to 1.5 * Physical RAM ?
We are using SQL 2000/2005
Thank you.
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
>
He's 1/2 right. You need more memory. But increasing the pagefile won't
make a difference.
Basically SQL Server can page, or it can ask the OS to page to disk. Both
involve disk I/O.
Get more memory or rewrite your queries.

> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Increasing the page file size generally would not be the solution to hard
paging. I'd first try to determine whether hard paging is from the SQL Server
process (i.e. whether Windows is paging out the workign set of the SQL Server
process). If that's the case, try to find whether there is any other
processes that are consuming memory and caused paging.
It's also possible that you may be running into a SQL Server bug. For
instance, http://support.microsoft.com/kb/884593 or
http://support.microsoft.com/kb/918483 are examples. I'm not saying that they
apply to your case, but want to point this out as a possibility.
Linchi
"F" wrote:

> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
> solution. He believes the pagefile settings should be 1.5 times the amount
> of physical RAM which I agree but I seem to find it hard to correlate paging
> with pagefile increase.
> Also under what conditions would one need ot consider increasing the size of
> the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
>
|||I too agree that increasing the pagefile size will not help at all. Your
DBA's first thought should have been "what is causing the paging" and not
how to get around it. SQL Server is designed to do as much as possible to
avoid paging to begin with. It is likely you have other applications on the
server than SQL Server that require some memory and SQL Server is set to use
most of it. If that is the case you may be able to avoid the paging by
setting the MAX Memory setting in SQL Server to leave room for the other
apps. Adding additional memory may also be an option but you still have to
consider how all the apps play together.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.

Pagefile and Paging

My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
solution. He believes the pagefile settings should be 1.5 times the amount
of physical RAM which I agree but I seem to find it hard to correlate paging
with pagefile increase.
Also under what conditions would one need ot consider increasing the size of
the pagefile if its not set to 1.5 * Physical RAM ?
We are using SQL 2000/2005
Thank you."F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
>
He's 1/2 right. You need more memory. But increasing the pagefile won't
make a difference.
Basically SQL Server can page, or it can ask the OS to page to disk. Both
involve disk I/O.
Get more memory or rewrite your queries.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Increasing the page file size generally would not be the solution to hard
paging. I'd first try to determine whether hard paging is from the SQL Server
process (i.e. whether Windows is paging out the workign set of the SQL Server
process). If that's the case, try to find whether there is any other
processes that are consuming memory and caused paging.
It's also possible that you may be running into a SQL Server bug. For
instance, http://support.microsoft.com/kb/884593 or
http://support.microsoft.com/kb/918483 are examples. I'm not saying that they
apply to your case, but want to point this out as a possibility.
Linchi
"F" wrote:
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
> solution. He believes the pagefile settings should be 1.5 times the amount
> of physical RAM which I agree but I seem to find it hard to correlate paging
> with pagefile increase.
> Also under what conditions would one need ot consider increasing the size of
> the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
>|||I too agree that increasing the pagefile size will not help at all. Your
DBA's first thought should have been "what is causing the paging" and not
how to get around it. SQL Server is designed to do as much as possible to
avoid paging to begin with. It is likely you have other applications on the
server than SQL Server that require some memory and SQL Server is set to use
most of it. If that is the case you may be able to avoid the paging by
setting the MAX Memory setting in SQL Server to leave room for the other
apps. Adding additional memory may also be an option but you still have to
consider how all the apps play together.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.

pagefile and memory advice?

Hello, I've been reading about this topic, and I've gotten myself more
confused, not less.

We have a single-processor license SQL Server Standard 2005 (xeon 2.8
ghz) with 4 GB RAM in Windows Server 2003 SP1 Standard.

I turned on the /3GB switch in boot.ini but not PAE or AWE, would it be
good to have either one??

Quote:

Originally Posted by

>From what I have seen so far, these are advanced settings for bigger

servers or databases than we presently have.

The SQL server is for Great Plains accounting, the largest db is around
3 GB now, and probably grows a GB per year.

We have Abra Suite running as well, someday this will get changed over
to a SQL database, it is FoxPro for now.

I know not to have IIS or other apps running on this box, other than a
couple Access files that are accessed by client apps.

We expect to make more use of this server in the coming years, to
contain intranet database files and web app files and maybe even
SharePoint, maybe someday we need a bigger server..

I was advised to remove or reduce the pagefile size, how should I
determine how big the pagefile should be?? I am thinking 1 GB down from
4 GB.

Thank you for reading this, TomOn 19.12.2006 15:48, tlyczko wrote:

Quote:

Originally Posted by

Hello, I've been reading about this topic, and I've gotten myself more
confused, not less.
>
We have a single-processor license SQL Server Standard 2005 (xeon 2.8
ghz) with 4 GB RAM in Windows Server 2003 SP1 Standard.
>
I turned on the /3GB switch in boot.ini but not PAE or AWE, would it be
good to have either one??
>

Quote:

Originally Posted by

>>From what I have seen so far, these are advanced settings for bigger

servers or databases than we presently have.
>
The SQL server is for Great Plains accounting, the largest db is around
3 GB now, and probably grows a GB per year.
>
We have Abra Suite running as well, someday this will get changed over
to a SQL database, it is FoxPro for now.
>
I know not to have IIS or other apps running on this box, other than a
couple Access files that are accessed by client apps.
>
We expect to make more use of this server in the coming years, to
contain intranet database files and web app files and maybe even
SharePoint, maybe someday we need a bigger server..
>
I was advised to remove or reduce the pagefile size, how should I
determine how big the pagefile should be?? I am thinking 1 GB down from
4 GB.

That's not exactly a MS SQL Server question. Generally you should set
max memory *in* SQL Server to be not more than physically available.
Other than that I have no advice to offer.

Regards

robert|||In my experience with ~5GB Great Plains, 2GB memory is more than enough. I
would recommend against sharing an accounting server with any other
non-accounting applications for security reasons.

In advising on pagefile size we would need your disk layout, RAID level,
etc.

I prefer multiple fixed size, unfragmented, paging files spread across all
disk sets except the ones containing the transaction logs and tempdb. I also
size my server so that the paging file is rarely, if ever, used.

The pagefile is by default sized to do a memory dump if the server fails. I
have never met anyone who has actually used this memory dump for server post
mortem, but I'm sure someone has... However, if space is so short that 3GB
would matter then you likely need to get more and/or bigger disks. I prefer

Quote:

Originally Posted by

50% free space on all disk sets.

"tlyczko" <tlyczko@.gmail.comwrote in message
news:1166539739.038505.10810@.80g2000cwy.googlegrou ps.com...

Quote:

Originally Posted by

Hello, I've been reading about this topic, and I've gotten myself more
confused, not less.
>
We have a single-processor license SQL Server Standard 2005 (xeon 2.8
ghz) with 4 GB RAM in Windows Server 2003 SP1 Standard.
>
I turned on the /3GB switch in boot.ini but not PAE or AWE, would it be
good to have either one??
>

Quote:

Originally Posted by

>>From what I have seen so far, these are advanced settings for bigger

servers or databases than we presently have.
>
The SQL server is for Great Plains accounting, the largest db is around
3 GB now, and probably grows a GB per year.
>
We have Abra Suite running as well, someday this will get changed over
to a SQL database, it is FoxPro for now.
>
I know not to have IIS or other apps running on this box, other than a
couple Access files that are accessed by client apps.
>
We expect to make more use of this server in the coming years, to
contain intranet database files and web app files and maybe even
SharePoint, maybe someday we need a bigger server..
>
I was advised to remove or reduce the pagefile size, how should I
determine how big the pagefile should be?? I am thinking 1 GB down from
4 GB.
>
Thank you for reading this, Tom
>

|||Russ Rose wrote:

Quote:

Originally Posted by

In my experience with ~5GB Great Plains, 2GB memory is more than enough. I
would recommend against sharing an accounting server with any other
non-accounting applications for security reasons.
>
In advising on pagefile size we would need your disk layout, RAID level,
etc.
>
I prefer multiple fixed size, unfragmented, paging files spread across all
disk sets except the ones containing the transaction logs and tempdb. I also
size my server so that the paging file is rarely, if ever, used.
>
The pagefile is by default sized to do a memory dump if the server fails. I
have never met anyone who has actually used this memory dump for server post
mortem, but I'm sure someone has... However, if space is so short that 3GB
would matter then you likely need to get more and/or bigger disks. I prefer

Quote:

Originally Posted by

50% free space on all disk sets.

Hello, thank you for replying.

Disk space is not critical per se, I am inquiring about pagefile size
etc., SQL is doing well at managing itself for us, because someone in
another NG suggested I should eliminate or reduce the pagefile, it's
currently 4 GB when the memory has been increased to 4GB.

Thanks, Tom