I am configuring a server with Windows Server 2003 Standard Edition and SQL Server 2000 Enterprise Edition. The server has 4GB memory. From what I have read the AWE/PAE switch only works with Advanced Server or Datacenter Editions, which are not an option for me. What is needed for SQL Server to use 3GB of the memory for 2003 SE?

Thanks, Dave

Take a look:

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10811



Luis Martin
Moderator
SQL-Server-Performance.com

One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
Bertrand Russell


All postings are provided “AS IS” with no warranties for accuracy.

Thanks Luis,

So it's /3gb / pae. I found an article on Microsoft's site "How to edit the Boot.ini file in Windows Server 2003". To make things more confusing for me I also found the following caution.

Caution Microsoft supports using the /3GB switch in Windows Server 2003, Standard Edition in a production environment for use by Active Directory. For other applications, Microsoft supports using the /3GB switch in Windows Server 2003, Standard Edition only in a production environment if the application vendor has tested in this environment and if the vendor is willing to support the customer who is using this functionality. Microsoft Exchange Server 2003 and Microsoft SQL Server 2000 are supported in production using this functionality. Contact your application vendor regarding their application. The /3GB switch can cause some applications to have problems that are related to address dependencies or to a reduction in kernel space. Except in the cases described here, the /3GB switch in Windows Server 2003, Standard Edition is only for development and testing purposes.

I need to know exactly what can cause the application problems and determine if this is worth the risk.

Dave

I believe such issues are related from operating system, so may check with Windows newsgroups with your question.

---

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.

I called Microsoft for clarification since we are crunched for time. The call didn't start out well since the tech said she was unfamiliar with using the 3GB switch with SQL Server 2000 Enterprise Edition, but said it is supported with SQL 2000 Standard Edition. I said she had it reversed and we spent about 10 minutes reviewing articles indicating how Standard Edition would not go above 2GB. Once we made it past that point it was explained to me that most applications run in user mode, not kernel mode, so the chances of 1GB of the kernel being used on my server were between slim and none. The only applications running on my server are SQL Server 2000 EE, Outlook 2003, Microsoft Operations Manager, Cisco Security Agent and Doubletake, a file replication package for DR.

I was told when the 3GB switch is used the OS allocates 3GB to each application running on the server. This means SQL Server can use up to 3GB, Outlook 2003 up to 3GB, MOM... etc... If 3GB of physical memory has already been used, it will go to disk for the additional memory. My only concern is how to prevent Outlook, MOM, CSA and Doubletake from getting out of control and requesting 3GB of memory. The odds of this happening is slim, but it sounds like a possibility.

Dave

Here's an update.

The 3GB switch will only allow SQL Server to use "virtual memory". I want to use 3GB of the physcial memory. I've been on the phone with Microsoft the past two days trying to get an explanation of how Windows Server 2003 Standard Edition can allow SQL Server Enterprise Edition to use 3GB of physical memory, but the three technicians I've spoken with (2 windows & 1 SQL Server) appear just as confused as me.

I've been told Windows Server 2003 is PAE enabled (assuming hot-add memory is used ), but then have been told PAE only applies to more the 4GB of physical memory and that only 2003 EE and Data Center are automatically enabled. I've also read where AWE only applies to more then 4GB. They currently have me on hold trying to determine if SQL Server can use 3GB of physical memory.

Do you have anything to add regarding 3GB of physical memory or any links that explain if SQL Server can or cannot use 3GB of physical memory on a 4GB server (Windows Server 2003 SE)?

I appreciate the help.

Dave

Below KBAs & links for your information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
http://support.microsoft.com/support/kb/articles/Q291/9/88.ASP
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56145

HTH

---

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.

Thanks Satya, however I've already seen the two documents and read the post. One of the documents does not pertain to Windows Server 2003 and the second speaks primarily to 2003 EE and Datacenter and addresses the /3GB switch, which according to Microsoft is "virtual memory", not physical.

I'm not concerned as much about the /3GB switch and the 1GB of kernel memory since I do not believe anything on my server should be using 1GB of kernel memory. I'm more concerned with understanding how to assign SQL Server the 3GB of physical memory on the server. I spoke with MS for 2 hours yesterday and talked to 3 engineers and none where able to address my question. They contradicted various MS technical documents, which I had to point out to them. Once they saw the documents they weren't able to provide me with an answer.

Dave

Here is an update. The information below comes from a Microsoft Senior Support rep for Windows Server 2003. I received this information after speaking with 4 MS support reps, none of which fully understood /3GB, PAE and AWE. I hope this information helps someone.

/3gb switch is a combination of physical and paged memory, it simply allows an /3gb aware application to allocate up to 3GB of virtual memory.

If you want to dedicate chunks of physical memory, increase the physical memory and use /PAE and configure SQL to use AWE memory

If you are running Windows Server 2003 Standard Edition, you are not able to increase physical memory above 4GB at this time.

With that limitation, IF you need more than 2 GB of memory, then the /3gb switch is the only option.

With the 3GB switch, use the /userva=2950 to give some of the memory back to the kernel memory, specifically to the Free System Page table entries.

Since /3GB reduces kernel address space by 1 GB, it is always advisable to change this configuration only if there is evidence of memory pressure. We always advice our customers to test the memory configuration changes to see what works best for their environment.

The following are some of the most common errors we see reported in the SQL error log which might indicate either MemToLeave or Bpool memory pressure.

WARNING: Clearing procedure cache to free contiguous memory

WARNING: Failed to [reserve | commit] contiguous memory

Error 17802/Unable to spawn xxx thread

Error 701 - There is insufficient system memory to run this query

Error 7399 - OLE DB provider <rovider> reported an error. The provider ran out of memory.

Error 8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query

Error 8651 - Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option.

Error 8902 - Memory allocation error during DBCC processing.


The following two articles explain various memory options in great detail which might be helpful:

Inside SQL Server 2000's Memory Management Facilities:

<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp>

How to configure memory for more than 2 GB in SQL Server:

<http://support.microsoft.com/default.aspx?scid=KB;%5bLN%5d;274750>



Important Notes:

/3GB vs. AWE (from Inside SQL Server 2000's Memory Management Facilities article)

The ability to increase the private process address space by as much as 50 percent via application memory tuning is certainly a handy and welcome enhancement to Windows memory management facilities; however, the Windows AWE facility is far more flexible and scalable. As I said earlier, when you increase the private process address space by a gigabyte, that gigabyte comes from the kernel mode address space, which shrinks from 2GB to 1GB. Since the kernel mode code is already cramped for space even when it has the full 2GB to work with, shrinking this space means that certain internal kernel structures must also shrink. Chief among these is the table Windows uses to manage the physical memory in the machine. When you shrink the kernel mode partition to 1GB, you limit the size of this table such that it can manage a maximum of only 16GB of physical memory. For example, if you're running under Windows 2000 Data Center on a machine with 64GB of physical memory and you boot with the /3GB option, you'll be able to access only 25 percent of the machine's RAM-the remaining 48GB will not be usable by the operating system or applications. AWE also allows you to access far more memory than /3GB does.

Obviously, you get just one additional gigabyte of private process space via /3GB. This additional space is made available to applications that are large-address aware automatically and transparently, but it is limited to just 1GB. AWE, by contrast, can make the entirety of the physical RAM that's available to the operating system available to an application provided the application has been coded to make use of the AWE Win32 API functions. So, while AWE is more trouble to use and access, it's far more flexible and open-ended.

This isn't to say that there aren't situations where /3GB is preferable to AWE-there certainly are. For example, if you need more space for memory allocations that cannot reside in AWE memory (thread stacks, lock memory, procedure plans), you may find that /3GB is a better fit.

Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003 The maximum amount of physical memory addressable by a 32-bit addressing mode is 4 GB. All processors based on the IA-32 architecture that begin with the Intel Pentium Pro, support a new 36-bit physical addressing mode known as Physical Address Extension (PAE). PAE allows up to 8 GB of physical memory on Windows 2000 Advanced Server and up to 32 GB of physical memory on Windows 2000 Datacenter Server. This is because the tested memory limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel requires an Intel architecture processor, Pentium Pro, or later and either Windows 2000 Advanced Server or Windows 2000 Datacenter.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

268230 <http://support.microsoft.com/kb/268230> Scaling out versus scaling up with Intel Physical Addressing Extensions (PAE)


SQL Server 2000

· Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can use the PAE and AWE.

· The PAE use requires Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003.

· 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.

· Windows NT 4.0 Enterprise Edition does not support the AWE memory architecture model so AWE support is not available.


SQL 7.0

· AWE isn#%92t supported in SQL 7.0
· SQL Server 7.0 Enterprise edition can use /3GB
· The extended memory option in SQL Server 7.0 is not available on Windows 2000 Advanced Server or Windows Datacenter.

About 3GB (4 GT) (fromhttp://support.microsoft.com/kb/171793):

User-mode Address Selection: When 4GT is enabled, the highest bit of a virtual address cannot be used to differentiate user-mode addresses from kernel-mode addresses.

Memory Allocation Issues: Some dynamic link library (DLL) files load near the 2 GB boundary; therefore, there is a region of the 2 GB space in which contiguous memory cannot be allocated using VirtualAlloc.

Effects Visible in Kernel Mode: Kernel-mode code can no longer assume the
user/kernel boundary is at 0x80000000 or at any other number. Code that uses
ProbeForRead or ProbeForWrite macros must be rebuilt using new headers that no longer contain assumptions about kernel space starting at 0x80000000.

If you restart with the 3 GB switch, also known as "4 Gig tuning," the amount of non-paged pool is reduced to 128 MB from 256 MB.

Fromhttp://support.microsoft.com/kb/247904/EN-US/

The two types of data that share a portion of this 2 GB address area are Paged Pool allocations, and kernel stack allocations. If the operating system runs out of space in one of those areas, the other area cannot donate space to it, and programs may begin to encounter unexpected errors. Therefore, when you encounter a Windows 2000-based computer that is experiencing unexpected errors or an inability to accept new logins, and the computer does not have some other resource limitation such as Central Processing Unit (CPU) or disk bottlenecks, it is highly likely that the Paged Pool or System PTE areas are becoming low on disk space. Because, by default, the System PTE area is sized to be as large as possible on a computer with Terminal Services enabled, the limitation will usually be due to insufficient Paged Pool address space. In this case, 3GB will not be a good idea.

Wow thats great Dave, thanks for update.
It seems most the KBAs were referred before and being repetitive to look for same type of solution.

---

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.

If you are using AWE , then you should set max server memory otherwise SQL Server will take up all the available memory. Also for the account under which SQL Server service is running, you should give rights as "Lock pages in memory". You can see this in local secfurity policy under Adminstrative tools.

On behalf of anyone using SQL server I would like to thank Dave for his research. Maybe you should work at MS so we can all get accurate info. I am using Windows 2000 Advanced Server with 8Gb of RAM and SQL 2000 with SP3. How could I use the switch to add another Gb for SQL?

Also, Dave I see that you mention the use of DoubleTake, are you happy with it? Are using it for SQL only or do you use it for IIS?

Thanks, Dennis

Dave, Thank You so much for all of your determination and fortitude in bringing this information to light - it has helped me and my organization immensely.

--jim