February 6, 2009 at 8:05 am
My network administrator recently added 8GB of memory to one of our production SQL Servers, for a total of 12GB.
This server is running Windows Server 2003 R2 Enterprise 32-bit and SQL Server 2005 Standard Edition.
An excerpt from the boot.ini file looks like this:
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB
I've been checking out BOL about AWE and PAE, but I can't say I'm very clear on what I need to do to enable SQL Server to use the extra memory when needed.
So... I'm looking for suggestions! And will a reboot be required?
February 6, 2009 at 8:47 am
This could be a problem..
BOL: "Support for AWE is available only in the Enterprise and Developer editions.."
I'd still like to hear some comments/suggestions..
February 8, 2009 at 2:02 am
For 32-bit installations, the use of address windowing extensions (AWE) memory has been improved so that SQL Server 2005 Standard Edition can use more than 3GB of RAM. Also, with 64-bit processors increasingly more prolific and affordable, the benefits of 64-bit server installations are becoming much more accessible. A 64-bit architecture provides significantly larger directly addressable memory space (compared to 32-bit) for all database operations, and eliminates the need for the AWE mechanism.
http://technet.microsoft.com/en-us/magazine/2006.01.boostperformance.aspx
One of the poster said that it will work in standard edition too:
http://sql-server-performance.com/Community/forums/p/2322/2322.aspx
MJ
February 8, 2009 at 2:16 am
You should be Okay.
SQL Server standard edition can use as much memory as O/S maximun and dynamic AWE support is also available (1).
(1) http://www.microsoft.com/Sqlserver/2005/en/us/enterprise-compare-editions.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 8, 2009 at 2:34 am
Tung
sql2005 standard will address RAM to the operating system max, so no problem there. For a machine with 12GB RAM you may want to remove the /3GB switch from the boot.ini and restart the server you'll need to monitor and act accordingly. How many instances of sql are on this box?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2009 at 9:07 am
Perry Whittle (2/8/2009)
Tungsql2005 standard will address RAM to the operating system max, so no problem there. For a machine with 12GB RAM you may want to remove the /3GB switch from the boot.ini and restart the server you'll need to monitor and act accordingly. How many instances of sql are on this box?
100% agreed.
When I read the post and thought of the /3GB switch my first thought was: "DANGER WILL ROBINSON!!"
If you only allocate 1 GB to the OS Kernel memory (which is what the /3GB switch does) you run the risk of not being able to properly address 12 GB of RAM on a heavy workload.
from Microsoft:
A range of 80,000 to 140,000 system PTEs is available in a standard configuration, while only 40,000 PTEs are available when using the /3GB switch.
MS considers 3,000 Free PTE's as a mimimum. With only 40,000 available you run the risk of starving your OS fairly quickly.
Here's a good article on the topic
My advice is when going 12 GB or higher to ditch the /3GB switch.... apart from that... just get a 64 bit system, it saves this headache.
~BOT
Craig Outcalt
February 8, 2009 at 3:53 pm
SQLBOT (2/8/2009)
My advice is when going 12 GB or higher to ditch the /3GB switch..
the official standpoint is 16GB RAM but i have seen situations where systems with a lot less (i.e. around 12Gb) have had the problems you have mentioned. I tend to believe anything with 12 GB up and you don't use the /3GB switch. Starving the OS of memory can really bring the server to its knees
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2009 at 7:21 pm
There's just one instance of SQL Server on the box. It can handle 64-bit, which I think would be the way to go, but I'm not too excited about rebuilding the image as 64-bit on this production system. I don't have the extra hardware in case of a problem with the installation.
If I can get another server, I would install Win 2003 64-bit and SQL Server 2005 64-bit and then switch servers.
February 9, 2009 at 6:16 am
This is my first post so be gentle pelase. I jsut ran into a similar issueand correct do not use the /3gb switch. I go this information straight from microsoft support. Also you want to allocate the high memory to MSSQL. FOr MSSQL 2000 I used this to allocate min and max memory change min to max to allocate max memory. I installed 16 gb of memory and allocated 12 gb of high memory to MSSQL on a 32 bit machine. Excellent improvenment. I am not sure if 2005 uses the same sp please verify before using.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'min server memory', 12288
RECONFIGURE
GO
February 9, 2009 at 7:32 am
What about /pae switch in boot.ini? Is that needed for AWE to be enabled?
February 9, 2009 at 7:37 am
As per Microsoft pae is already part of 2003 enterprise that I am using but it does not hurt to leave it there. So yes keep the /pae switch
February 9, 2009 at 10:08 am
I'll toss in another vote that in the long run, you'd probably be better off running 64 bit OS and SQL server on that box.
If you are still in the process of getting the system up and running, NOW would be the time to make that decision, because you are not likely to reconfigure it later once it's in service.
February 9, 2009 at 10:37 am
It's been in service for over a year now, and it's used as our data warehouse and reporting server, but additionally functions as our intranet server.
February 9, 2009 at 2:40 pm
huh must have misread things.. it just sounded to me like a new setup you were trying to debug..
just call me emeliy lattella then.. "Never Mind"
February 11, 2009 at 7:46 am
An update for the curious:
I removed the /3GB switch from the boot.ini, and added /PAE, although from what I've read, it's not necessary.
I enabled AWE on SQL Server, and set minimum memory to 2 GB, max to 10 GB (we have 12 GB total).
System Information shows now that we have 12,282.64 MB physical memory, and 580.63 MB is available, so it looks like it's using the memory, which is good!
So BOL is probably not quite accurate in saying:
Support for AWE is available only in the Enterprise and Developer editions and only applies to 32-bit operating systems.
Again, we are using SS2005 Standard Edition 32-bit on Windows Server 2003 Enterprise.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply