Another memory question

SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron
You will also have to turn on the /PAE switch in BOOT.INI. Use sp_configure
to turn on AWE and to set the max server memory.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron
|||Do not use /3gb switch if you want sql server to use 4GB. You could
probably go up to 5.5-6GB or so if this is a dedicated machine.
Occasionally observe pages/sec performance monitor counter to see if sql is
taking too much ram.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> Server has 8GB RAM - SQL Server currently takes 1.7GB
> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> posts and replies and I think I may have all the info, taking a little
> from
> several posts. I'm just not positive as I haven't seen a step by step (in
> the proper order) document or posting. Not sure if Microsoft has a step
> by
> step. I've been through books on-line and have seen all these topics.
> Are these the steps in the proper order?
> 1-Update the boot.ini to add the /3gb switch
> 2-Enable awe (via SSMS)
> 3-Select running values or configured values? (via SSMS)
> 4-Set the SQL Server max memory to 4gb (via SSMS)
> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> 6-Reboot server
> Anything else?
> Thanks
> Ron
>
>
|||While I also have to ask why you want to limit it to 4GB when you have 8GB
total I don't agree with the statement not to use the /3GB. Since you will
have to use AWE to access anything over 3GB anyway that is not a factor. The
question is can you benefit from the extra 1GB of directly addressable
memory or not. Since the data buffer pool is the only thing that can use AWE
memory you need to see if you could use that extra GB for things like proc
cache, connection memory etc.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13stt0f7jhtnk14@.corp.supernews.com...
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql
> is taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>
|||If something should not work correctly can I simply change these settings
back to the original configuration and reboot?
Thanks, I'll remove the /3gb and replace it with the /pae.
"TheSQLGuru" wrote:

> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql is
> taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>
>
|||Your right, I should have said I wanted to get it to a minimum of 4gb. I
will be setting it at 5.5gb as recommended.
So do I want both /3gb and /pae?
"Andrew J. Kelly" wrote:

> While I also have to ask why you want to limit it to 4GB when you have 8GB
> total I don't agree with the statement not to use the /3GB. Since you will
> have to use AWE to access anything over 3GB anyway that is not a factor. The
> question is can you benefit from the extra 1GB of directly addressable
> memory or not. Since the data buffer pool is the only thing that can use AWE
> memory you need to see if you could use that extra GB for things like proc
> cache, connection memory etc.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13stt0f7jhtnk14@.corp.supernews.com...
>
|||Depending on what server you have, you may or may not need to specify PAE in
boot.ini. For instance, most, if not all the, current HP ProLiant servers
support hot add memory, and don't require PAE. Regardless, if you can already
see 8GB at the OS level, you are all set, and don't have to be bothered with
setting PAE or not.
See this KB for additional info: http://support.microsoft.com/kb/283037
For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
slightly more to the buffer pool, and generally don't use 3GB unless
otherwise needed.
Linchi
"Ron" wrote:
[vbcol=seagreen]
> Your right, I should have said I wanted to get it to a minimum of 4gb. I
> will be setting it at 5.5gb as recommended.
> So do I want both /3gb and /pae?
>
> "Andrew J. Kelly" wrote:
|||Linchi,
That's interesting. The /PAE is an operating system feature so how does the
new Proliant machines get around not having to set this? I know Windows 2003
has the ability to set it automatically but I haven't heard of the hardware
doing this. Can you elaborate?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C00A3183-DE64-4F34-93D8-0FE342EC411E@.microsoft.com...[vbcol=seagreen]
> Depending on what server you have, you may or may not need to specify PAE
> in
> boot.ini. For instance, most, if not all the, current HP ProLiant servers
> support hot add memory, and don't require PAE. Regardless, if you can
> already
> see 8GB at the OS level, you are all set, and don't have to be bothered
> with
> setting PAE or not.
> See this KB for additional info: http://support.microsoft.com/kb/283037
> For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
> slightly more to the buffer pool, and generally don't use 3GB unless
> otherwise needed.
> Linchi
> "Ron" wrote: