SQL Server Performance

/PAE and 4GB RAM?

Discussion in 'Performance Tuning for Hardware Configurations' started by Mazie, Dec 1, 2003.

  1. Mazie New Member

    I've had this issue on 3 new Intel server boards lately. Using WS2k3 Standard, SQL2k Standard. Machine has 4GB RAM. OS sees only 3.3 to 3.8 GB of RAM (depending on mainboard model). After including /PAE switch OS reports 4GB RAM. What are the implications for SQL here? If the OS sees less than 4GB RAM is the total split between the kernel and the application pools or does one pool get 2GB? Should I use the /3GB switch depending on how the memory is divided?

    Machines are Supermicro servers using all Intel certified RAM etc. thus I don't think it's a hardware compatibility issue.

    Thanks
  2. bradmcgehee New Member

    If your server has only 4GB of RAM, you should use the /3GB switch, not the /PAE switch.

    See:http://www.sql-server-performance.com/awe_memory.asp

    Also, SQL Server 2000 Standard will only recognize 2GB of RAM. You need Enterprise if you want to use more than 2GB of RAM.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. Mazie New Member

    Understood that standard edition uses only 2GB of RAM. Since this is a dedicated SQL server, do I gain anything by using the /3GB switch? Also, why does the OS not see the full 4GB of RAM unless I use the /PAE switch? Should I omit the switch even though I would appear to lose some RAM?
  4. satya Moderator

    Try to capture perfmon counters to check whether OS is captivating configured memory or not.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. bradmcgehee New Member

    The /3GB switch will not affect SQL Server, but it will affect the OS, as the OS can see up to 4GB of RAM. I would go ahead and use the /3GB switch, so the OS will make use of it, otherwise it is wasted. You don't need to use the /PAE switch unless you have more than 4GB, which is not your case.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  6. Mazie New Member

    I thought the /3GB switch reserved 3 GB for applications and 1 GB for the OS?
  7. Luis Martin Moderator

    With /3GB switch, OS can see up to 4GB. SQL will use 2GB dynamically. More used more memory up to 2GB.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  8. bradmcgehee New Member

    If you have 4GB in a server, you need to use the /3GB switch to make use of it, or its wasted. Even though SQL Server can't use the extra memory, the OS can, leaving more regular RAM available for SQL Server.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  9. Mazie New Member

    Thanks, I think I understand the need for the /3GB switch better now.

    Any thoughts on why the OS does not see all the RAM unless I use the /PAE switch?
  10. bradmcgehee New Member

    What method are you using to determine if the OS is using or not using all of the physical RAM? With only 4GB of RAM, and using the /3GB switch, the OS should be able to see all the RAM. The /PAE switch is not needed. But, in some cases, the tools used to see the RAM don't always work correctly with the /3GB switch is used.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  11. Mazie New Member

    Even without the /3GB switch the OS reports about 3.6 GB of RAM. I get this number from My Computer/Properties. Adding the /PAE switch makes this issue go away. The reported RAM does not change when I add or remove the /3GB switch.
  12. bradmcgehee New Member

    What value do you see for memory in Task Manager, both ways?

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  13. Mazie New Member

    I get 3,802,196k both with and without the /3GB switch. I would expect 4.1 GB or so. It would seem I'm not getting about 200MB. I get similar readings on 2 other machines. hmm...
  14. bradmcgehee New Member

    Memory is calculated differently by different software, which can be confusing. I think you are OK.

    On my server with 4GB and the /3GB switch, Task Manager tells me that I have 3,833,352 RAM.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  15. Mazie New Member

    Thanks for all your help. I have a much better understanding now. I'm relatively new to SQL and expect to get much use from this forum.
  16. simas New Member

    Good morning,
    I am setting failover cluster (active-active, Windows 2000 for OS and SQL Server 2000 for RDMS) and had some thoughts/questions about memory configuration. Each of the cluster nodes has 4 Gb of RAM, my databases are ~250 Gb in size.

    My goals
    a) maximize performance of each of the nodes under normal, non-failover conditions.
    b) make certain that in case of failover , second node is able to start.
    I am willing to accept possibility of performance degradation as a trade-off for faster performance under normal scenario as long as I understand the risks and issues involved.

    The options I see before me
    1) Enable /3 Gb switch on each node and do not set max server memory. This would give OS 1 Gb of RAM and leave 3 Gb to the SQL Server on each node. So each node can use 75% of total RAM.
    Issues with this scenario - I am not sure how memory would be allocated/reallocated during the failover, how much would the second node receive and would it be able to start

    2) Enable /AWE switch on each node and set max server memory setting at 3,5 Gb leaving 0.5 GB of RAM for the OS. It would allow >85% of total memory usage (and hopefully faster system) but I am not sure how it would fare in the failover.

    3) Enable /3 Gb switch on each node and set max server memory at 1.5 Gb. So if failover does happen, each node would receive 1.5 Gb of RAM, 1 GB for the OS and everything would function. However, under normal conditions each node only uses 1.5 out of 4 Gb available - less than 40%.

    4) Enable /AWE on each node and set max server memory to 1.75 - higher RAM utilization, not sure how it would perform in failover.


    Any thoughts, advice, suggestions, choices made in similar situations,etc?

    Thank you in advance

    Simeon

  17. bradmcgehee New Member

    As I have recommended before, you won't want to use AWE memory, as you only have 4GB in each server. Instead, only use the /3GB switch.

    If you set the both nodes of the active/active cluster to dynamic, so that each takes as much memory as it needs, you will have a potential performance issue whenever a failover occurs. Although the memory is set to dynamic, SQL Server doesn't easily give up any RAM it has taken, although it will over time. So if a failover should occur, the instance of SQL Server that failed over will have very little RAM to grab. It should start, but because it will not be able to grab much RAM at first, it will run very slow. Eventually, the other instance of SQL Server will give up some, but until then, performance of the failed over instance will be poor.

    One way around this is to limit the maximum amount of RAM that SQL Server can use. This would then leave some RAM available for any instance that fails over. Your option 3 in the previous post is along this line of thinking.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  18. simas New Member

    Thank you Brad,
    I am considering option 3 as well (even if it would mean that under normal condition each node only utilizes 37% of total RAM available).
    You mention that first node/instance will 'eventually ...give up some' memory- being in IT and therefore technically inclined, I am curious if there is any documentation/discussion on how that process works in details. It is handled by the operating system or internally within SQL Server? Has anyone tried done testing of this scenarios to put some numbers on the words 'eventually', 'over time',etc? Are results consistent enought to be significant?

    Simeon
  19. bradmcgehee New Member

    I was recently discussing this exact issue with an expert SQL Server performance guru at Microsoft. I was told that SQL Server does have the ability to negotiate the use of RAM, if the other application also understands the same negotiating protocol. Unfortunately, virtually no other applications, other than SQL Server, understand this protocol. Because of this, once SQL Server graps RAM, it rarely gives it up, as it is described in the BOL. This is even true with multiple instances of SQL Server on the same box. Because of this, I was told that you should not count on SQL Server giving up RAM easily, and if you need it, to limit what SQL Server has access to in the first place.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  20. simas New Member

    Found this on MSDN
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_0fub.asp

    Dynamically Managing Memory Between Multiple Instances
    When multiple instances of SQL Server are running on the same computer, each instance independently uses the standard dynamic memory management algorithm. There is no need for the instances to communicate with each other to cooperatively manage memory. When all but 4 MB to 10 MB of the memory on a computer is allocated, the amount of memory allocated to each specific instance of the database engine is driven by the relative workload of each instance. The instances with higher workloads acquire more memory, while instances processing lighter workloads acquire less memory. Regardless of the number of instances of SQL Server on a computer, the algorithm ensures:

    The overall amount of allocated memory remains under the level that would generate Windows NT® or Windows® 2000 page I/Os.


    The computer memory is efficiently distributed between the instances of SQL Server based on their relative workloads.


    The memory allocations are dynamic and can immediately adjust to changes in the workloads of individual instances of SQL Server.
    The interactions can be illustrated on a computer running two instances, but the same principles apply when several instances are running
  21. bradmcgehee New Member

    I am going to try to reach my Microsoft contact and see what she thinks about this article. If I hear back from her, I will let you know.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  22. fullbrij New Member

    Don't use the /3gb switch on 2000 standard, you'll be sorry. Likewise, don't use the /pae switch if you have 4GB of RAM.

    The /3gb switch, on 2000 Advanced, severely restricts the number of available page table entries. This can cause IO errors, backups can fail, and potentally DB corruption can occur. If you have 4GB of ram, and use the /3gb switch, you'll want to adjust the number of available system pages. You may also want to adjust the heapdecommitfreeblockthreshold to prevent excessive memory fragmentation.

    The /pae switch causes each pte to consume twice as much RAM. Using them both can really get ugly.


    http://support.microsoft.com/default.aspx?scid=kb;en-us;311901

    http://support.microsoft.com/default.aspx?scid=kb;en-us;274750

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;268230

    http://support.microsoft.com/default.aspx?scid=kb;en-us;304101


    W2k3 introduces the /USERVA switch. You can use this to "fine tune" the /3gb switch.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;316739






    John
    MOSMWNMTK
  23. biged123456 New Member

    I did recently see problems with large file copies and backups with the /3G and /PAE switches enabled. We were having problems with a 55G file copy from one set of local drives to another set of local drives and even backing up the file. As the previous post indicates, there are some MS articles that describe what is happening. Here is a quick summary:
    A range of 80,000 to 140,000 system Page Table Entries is available in a standard configuration, while only 40,000 Page Table Entries are available when using the /3GB switch. Using the /PAE switch instructs the memory manager to use two PTEs to map every page of memory (in effect, limiting to ~20,000 usable PTEs).

    This only really came into play for us with large files. I still wouldn't necessarily recommend against AWE, giving more memory to SQL is a good idea 99% of the time.
  24. jeremy New Member

    Hello all,

    I am working at a new company that runs many sql servers all with SQL2000 enterprise ontop of windows2000 advanced server, they all also have 4GB of RAM. All my understanding of sql leads me to believe that these servers need just the /3GB switch turned on, but the NTadmin at this company uses the /3GB /PAE switch. Does anyone know if there is any adverse effects using the /3GB /PAE instead of just /3GB switch.

    Thank you for your time,

    jeremy
  25. fullbrij New Member

    Yes, as listed in the links in this thread.



    John
    MOSMWNMTK
  26. bradmcgehee New Member

    While I have never personally tried using /3GB /PAE, I don't think it will have any negative effect as there is not enough memory for it to even work. But I could be wrong. Personally, I would remove this unnecessary switch now, and next time when you reboot, then they new settings will take effect. This is not enough of an issue to worry about rebooting now.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

Share This Page