SQL Server Performance Forum – Threads Archive

Memory question

Hi, I can’t work out the exact requirements/config for utilizing more than 2GB RAM. I have just installed a new server for a client. It’s a Proliant DL380 with 2 x 2.4 MhZ Xeon CPU and 3GB RAM + RAID ctrl with 2 x 36GB for system (RAID 10) 2 x 72GB for Data and 2 x 72GB for log. The database is 6.7GB in size I have installed Windows 2000 standard server with the 3GB switch in boot.ini, and installed SQL Server Enterprise Ed. with no additional configuration other than standard installation. The server however refuses to use more than 1.725GB – it goes rather quickly to that amount, but then refuses to use any more than that. I have been trying to look up on Knowledge base for more information on this, but don’t quite understand what i found – can someone "cut it out in cardboard" for me please. Q1. Does Windows 2000 Server Std. only support 2GB (I thought it was 4GB if using the 3GB switch) Q2. What would the exact steps be to enable the full use af the 3GB RAM – based on the above info regarding Hardware. Please help Regards Bjarne Rasmussen

See the following information: Configure Memory for More Than 2 GB in SQL Serverhttp://support.microsoft.com/default.aspx?scid=kb;en-us;274750

Q1. OS needs to be W2K Advanced Server or W2K3 EE to use the 3GB switch.
while the OS can use 4GB with standard edition, each process, ie, sqlservr.exe has only 2GB for user mode code, and the other 2GB is protected OS space.
AS & EE lets you change the split from 2/2 to 3/1
not only that, but you can also use more than 3G with PAE next, for your app to use more than 2GB, you need SQL EE, which you already have, Q2. so you need to upgrade W2K Std Ed to Adv Srv. Ed.

See HOW TO: Configure Memory for More Than 2 GB in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750 HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

OK thank’s I have seen this – and that’s what I don’t quite understand – sorry my english <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I se this part: Windows 2000 Server SQL Server 2000 Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition can use up to 2 GB of physical memory. With the use of the AWE enable option, SQL Server can use up to 4 GB of physical memory. NOTE: You cannot allocate more than 4 GB of physical memory to an application on Windows 2000 Server because Physical Address Extension (PAE) is not available on Microsoft Windows 2000 Server. Also, you cannot use the 3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000 Advanced Server or Microsoft Windows Datacenter versions. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: 291988 A Description of the 4 GB RAM Tuning Feature and the Physical Address Extension Switch ——————————————————– So does that in other words mean that I have to upgrade to at least "Advanced server"?????

Hi again joechang – thank’s for your reply – I understand although my client wont be happy about the news. Doing this will it then (sqlserver.exe) use all of the 3GB – leaving say 128mb for the OS – do you know?? Thanks to you all for the quick replies!!!!

I think having AWE enabled requires 1 GB of memory for the OS. So I guess you’ll have a maximum available memory of 2 GB even if you upgrade to Win2K Advanced Server. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Ok so reading this article again….
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750#7 —- Cut —————————-
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can use the following options: Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 4 GB of memory. NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data Center, make sure that the /3GB switch is not in the Boot.ini file. If the /3GB switch is in the Boot.ini file, Windows 2000 may not be able to address any memory above 16 GB correctly. When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE. back to the top
Example
The following example shows how to enable AWE and configure a limit of 6 GB for the max server memory option: sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
sp_configure ‘awe enabled’, 1
RECONFIGURE
GO
sp_configure ‘max server memory’, 6144
RECONFIGURE
GO -or- -or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to use up to 3 GB of available memory.
—- Cut —————————- Right on the last line -or-Use etc…. If I understand that right.. If I don’t to use the AWE switch, SQL Server will use all it can up to 3GB and so therefore not swallow up 1 GB for the OS????

See the line

quote:When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE

This is what made me statement like that. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

OK thank’s I found this in another post…. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1011&SearchTerms=/3GB</a>, —–Cut————– Justin, yes, set /3GB and not /PAE or /AWE. You only need them for MORE than 4GB. If you set /3GB you should see SQL Server take up to about 2.8Gb.. I never see it take more than that on our system, but it isn’t short of RAM so I’m not concerned about pushing it even further. Tom Pullen DBA, Oxfam GB —–Cut————– So I’ll just try and Upgrade to "Advanced Server" and watch and see Thanks all for your help – it’s been great <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

]]>