 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Another 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.  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau 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. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > SQL2005 SP2, 32-bit Windows Server 2003 EE SP2 > Server has 8GB RAM - SQL Server currently takes 1.7GB [quoted text clipped - 21 lines] > > 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. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > 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. [quoted text clipped - 27 lines] >> >> Ron 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? > 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 [quoted text clipped - 35 lines] > >> > >> Ron 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 > 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. [quoted text clipped - 40 lines] > > >> > > >> Ron 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? SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Depending on what server you have, you may or may not need to specify PAE > in [quoted text clipped - 69 lines] >> > >> >> > >> Ron 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. > 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. [quoted text clipped - 26 lines] > > > > Ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.