SQL Server Performance Forum – Threads Archive

Using more than 2GB with W2K Std and SQL2K EE???

I’ve seen conflicting information on this and would like to know what the general concensus is on getting SQL to use more than 2GB of RAM on Windows 2000 Standard. I have a DL580 with 4GB of Ram and would like to have SQL use 3GB of it as opposed to the default 2GB. What switches can I set or enable to do it, or do I just have to go to Windows 2000 Advanced or Windows 2003 Standard in order to do it[?]

Windows 2000 Std can manage up to 4Gby.
AWE Memory and System Resources
Instances of SQL Server 2000 do not dynamically manage the size of the address space when you enable AWE memory. Therefore, when you enable AWE memory and start an instance of SQL Server 2000, one of the following occurs: If sp_configure max server memory has been set and there are at least 3 gigabytes (GB) of free memory available on the computer, the instance acquires the amount of memory specified in max server memory. If the amount of memory available on the computer is less than max server memory (but more than 3 GB), then the instance acquires almost all of the available memory and may leave only up to 128 megabytes (MB) of memory free.
If max server memory has not been set and there is at least 3 GB of free memory available on the computer, then the instance acquires almost all of the available memory and may leave only up to 128 MB of memory free.
If there is less than 3 GB of free memory available on the computer, memory is dynamically allocated and, regardless of the parameter setting for awe enabled, SQL Server will run in non-AWE mode.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Also, you need to have SQL Server Enterprise Edition for it to be able to use more than 2GB.
Look under the topic ‘Maximum Capacity Specifications’ in BOL.

You also need to have the /3GB switch in the boot.ini file or 2gb is reserved for the OS. the /3GB switch switches it to use 1GB for the OS and 3GB for applications. If you do this, you should disable any services you don’t need. You should also make sure you don’t have any system critical applications running on the server since it’s a dedicated SQL Server (it sounds like). MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Article and thread for reference:
http://www.sql-server-performance.com/awe_memory.asp
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4143 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Just so I’m clear. I have my Win2K (Not advanced) with SQL 2K EE I want to use more than the default 2GB of memory. I just have to set max memory to 3072, right? No /3gb, No AWE

You need to have the /3gb switch in the boot.ini startup file. You don’t need awe or the /pae switch. You don’t need to set the max memory for SQL Server at all. Just let it dynamically manage it. the other 2gb isn’t available for applications at all. It’s reserved for the OS. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

From what I have seen, you cannot use the /3GB switch with Windows 2000 standard.
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750#3
—
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.
—

My bad on that one. The only thing I have inhouse is Windows 2000 Advanced Server and 2003. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

I just want to clarify a situation that I have where I am at.
I have a Windows 2000 server (not advanced) with SQL 2000 standard edition. This server has 4GB of ram in it. If I go to SQL server properties in the enterprise manager and go to the memory tab it shows the following. SQL is set to dynamically configure SQL server memory. The minimum is set to 0MB and the maximum is set to 3920MB. Even though the enterprise manager shows that almost 4GB of memory is available it will never use above 2GB. SQL will not dynamically grow the memory past 2GB. Is this correct? If I want the system to use all 4GB of memory I will have to go with SQL 2000 enterprise edition.

You should be able to get SQL to use 3GB by setting the switch in the boot.ini. That shouldn’t be dependent on the application, as it is an inherent Windows design limitation that you’re changing by using that switch Cheers
Twan

You are right dlepak. Twan, he can only use that switch on datacenter or advanced. If you want to use all 4gb of memory, you will need to have Windows 2003, Windows 2000 Advanced, or Windows 2000 Data Center (which of course you won’t get <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ). You will also need SQL 2000 Enterprise Edition. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

ah that’s right it wasn’t until windows 2003 that the 3gb switch was allowed for the standard edition… Twan

Thanks for the replies!

]]>