SQL Server Performance

Memorey extension using /PAE with /3GB option

Discussion in 'Performance Tuning for Hardware Configurations' started by gkrishn, Oct 3, 2004.

  1. gkrishn New Member

    Hi all, i have a small query . can anyone pls give suggesion?


    server: windows server 2003 enterprise
    memory: 8GB ,using /PAE . but /3GB is not used .
    sql server 2000 enterprise


    i have enabled AWE, and set max memory as 7 GB in SQL server 2000, wil there be any pbm with
    sql server in gettin 7GB,as i havnt mentioned /3GB switch in the Boot.ini?
    Pls tell me ur suggesions...

    always - rajiv

    Rajiv
    SQL-DBA
  2. Argyle New Member

  3. gkrishn New Member

    Hi Argyle,

    thnx for ur reply.

    buthttp://support.microsoft.com/?id=274750#7 not explining anything about the usage of /3GB switch.Its tells to use /PAE only. tells to use /3GB in case of 4GB RAM. But i am wiht 8GB RAM.and i want to give 7GB to SQL server.

    I am very specific in my quesion about the pbm i am facing, can u pls look in to tht and ans me in a short.

    Argyle, tnx in advance


    Rajiv
    SQL-DBA
  4. satya Moderator

    Capture the PERFMON trace for memory, sql server memory, physical disk, processor, process etc for further assessment.

    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.
  5. gkrishn New Member

    I just want to know the Concept behind tht. I just configured for 7GB and askd my client to restart sql server. I can access the machine , only after 1 week.

    so wanna know if i need to add /3GB also in front of /PAE to get 7GB for sql server.

    Rajiv
    SQL-DBA
  6. joechang New Member

    no you don't need the /3GB to get 7GB for SQL.
    the /3GB only controls the size of the address space (2GB default, or 3GB).
    so without 3GB, you have 2GB of address space (directly addressable memory) and 5GB of awe memory,
    with 3GB, 3GB & 4GB of AWE memory.
    there are certain situations where /3GB can cause problems, hence the suggestion above
  7. gkrishn New Member

    sorry still its not clear to me.

    without /3GB, the OS wil hold 2 GB ,hence 1 GB wil go waste for me.
    hence 2GB + 4 GB extended = total 6GB for SQL server
    if i use /3GB,
    i can get 3GB + 4 GB extended = total 7GB for SQL server

    i have done the later, so wil ther be any issue with the usage of /3GB .i set the max memory of sql server to 7168 MB. pls comment on this if any issue.....

    Rajiv
    SQL-DBA
  8. skasam New Member

    There is Virtual Memory and Physical Memory for a Windows Intel machine. The /3GB is controlling virtual memory available for any application to use not just SQL Server.
    There is a total of 4 GB virtual memory available in a 32-bit operating system.
    By default, 2 GB is for Application, 2 GB is for OS.
    The 2GB for application is for any/all applications running on this system.
    Since Windows does Dynamic Memory management, it is transparent to the Applications as to how much physical memory is actually left on the system. All of them think they have 2GB for themselves. Windows uses Paging file as backup if more and more apps needs more memory than is available in the system.

    So, if you set /3GB, all the applications can not have 3GB instead of 2GB (default) and leave just 1GB for OS. BTW, if the server has over 16GB physical memory, you should NOT use /3GB switch in boot.ini

    Coming to your question, if you have set /3GB and /PAE in boot.ini and allocated 7GB for SQL Server in the Max server memory setting, and AWE enabled in sp_configure, then there should be no issues with that at all. It is just that there is lot more virtual memory for SQL Server to play around with which is a good thing.

    Also, as a side note, the AWE memory can ONLY be used by SQLS erver for data and index pages. Not for say procedure cache, plans, lock pages and any other data structures.
    So, if you have set /3GB in the boot.ini, you are allocating more memory to SQL Server for these other pages that may need the extra memory.

    But as always like Satya points out, please monitor your paging, memory usage, CPU etc in Perfmon and then decide what settings you should use in your production servers.



    quote:Originally posted by gkrishn

    sorry still its not clear to me.

    without /3GB, the OS wil hold 2 GB ,hence 1 GB wil go waste for me.
    hence 2GB + 4 GB extended = total 6GB for SQL server
    if i use /3GB,
    i can get 3GB + 4 GB extended = total 7GB for SQL server

    i have done the later, so wil ther be any issue with the usage of /3GB .i set the max memory of sql server to 7168 MB. pls comment on this if any issue.....

    Rajiv
    SQL-DBA

    Thanks,
    -- Sri
  9. SeanP New Member

    Quick Question along these lines:

    We are Windows Server 2003 Standard
    SQL Sever 2000
    We have 4GB memory install.
    Does W2k3 require the boot line /3GB switch to dedicated 3 GB to SQL server ?

    if so then this below is in error correct?
    [operating systems]
    multi(0)disk(0)rdisk(0)partition(1)WINDOWS="Windows Server 2003, Standard" /fastdetect
  10. SeanP New Member

    AHA I mfound the line:

    Note: The maximum amount of memory that can be supported on Windows Server 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of physical RAM by using the Physical Address Extensions (PAE) feature. You can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with Microsoft Windows Server 2003 Datacenter Edition.

    I thought this was corrected in w2k3 where SQL Server would handle this by the max memory setting. Shows how little I know (*correction* showd how much I should listen to others talking up Windows Server 2003).

    So new question: I should apply the /3GB switch then set SQL Server Max memory to what? or leav it at current of MAX 3936MB?

    Also any need for /fastdetect switch? <<-- I'm not familiar with it.
  11. skasam New Member

    So new question: I should apply the /3GB switch then set SQL Server Max memory to what? or leav it at current of MAX 3936MB?
    > Leave the Max Server memory to the default or 3936 MB and have /3GB switch in boot.ini





    quote:Originally posted by SeanP

    AHA I mfound the line:

    Note: The maximum amount of memory that can be supported on Windows Server 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of physical RAM by using the Physical Address Extensions (PAE) feature. You can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with Microsoft Windows Server 2003 Datacenter Edition.

    I thought this was corrected in w2k3 where SQL Server would handle this by the max memory setting. Shows how little I know (*correction* showd how much I should listen to others talking up Windows Server 2003).

    So new question: I should apply the /3GB switch then set SQL Server Max memory to what? or leav it at current of MAX 3936MB?

    Also any need for /fastdetect switch? <<-- I'm not familiar with it.

    Thanks,
    -- Sri
  12. ykchakri New Member

    According to 'Guru's Guide to SQL Server Architecture', it is better to use /AWE instead of /3GB. Because with /3GB switch, the User memory will be set to 3 GB and hence the Kernel memory will shrink to 1 GB. This may affect the overall performance of the server.

    Here's a excerpt from the book:

    AWE and /3GB provide applications mechanisms for accessing memory beyond the standard 2GB user mode partition. The /3GB option actually limits the total amount of physical memory that Windows can manage, so it is generally not recommended. AWE is the more flexible of the two and can make all the physical memory that's visible to the operating system available to applications.

  13. biged123456 New Member

    I agree with ykchakri. I have seen problems where the OS has run out of memory for large file operations (backups of large dumps and large file copies). This article talks more in detail about this -http://support.microsoft.com/?id=311901.
  14. gkrishn New Member

    ****Reffered to SeanP's QUERY****

    If its a dedicated SQL server

    use /3GB switch in the Boot.ini file
    and Just enable AWE in SQL server
    Leave max mem settings to be default.

    This wil give allocated 3 Gb memory for SQL to play around


    I am not expert to give this solution, But i think this is the Correct method to do.IF any one have any suggession Pls let me know.





    Rajiv
    SQL-DBA
  15. gkrishn New Member

    For my problem(see the first post), tnx to so many who comup with solutions.

    I didnt mention /3GB in Boot.ini, But SQL server is takin 7GB outof 8GB. i cant understand tht concept still now.

    pls somone word me a better site or arcticle which explains OS/application memroty sharing.m confused with "2GB by OS and 2GB by Appli" concept. y the concept of virtual memory here,if have enough RAM.y OS need to hold 2GB??? and so on... i think it can be helpful to many site users.

    tnx in advance

    Rajiv
    SQL-DBA
  16. aawara New Member

    Rajiv,
    I see previously someone mentioned about /3GB which forces OS with 1 GB RAM. You will notice a decrease (about 50 - 80%) in your PTEs. If you are an intensive IO system you will see system crashes out of this switch. So the recommendation is to use /PAE upto 8 GB RAM. /3GB upto 4 GB RAM. If you incraese physical RAM, you have to get these switches out of boot.ini. There are some articles in knowledgebase but you really have to pay attention to what they are talking about. Its all about 32 bit and 36 bit memory allocations.
  17. gkrishn New Member

    ok awara, i didnt mention /3GB, means OS is holding 2GB of memory. so balance available for me is only 6 GB.<br /><br />see, my SQL server is using 7GB when i enabled AWE and set 'max memory size' as 7GB. so question is available is only 6GB, usage is 7GB .how ???? <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />tnx in advance.<br /><br />Rajiv<br />SQL-DBA
  18. satya Moderator

    Have you captured the PERFMON stats for assessment on the memory usage?

    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.
  19. aawara New Member

    Well, if you are seeing in task manager that the allocated RAM is 7 GB, it does not mean that SQL is in fact able to use 7. You will have to stress test your server to find out the real ###s. Look at the available bytes - mem and some other counters. If you dont have the /3GB switch, then you could easily find out your paged pool size and nonpagedpool size to figure out how much is at disposal to OS and how much is alocated to application. Howeverm allocation does not mean that it would be available if needed. Even if 6 GB available to application (SQL), the actual max would not be more than about 5 or 5 1/4. Its how the over head is designed to work. Again, the only way to find out is stress test or leak your memory?
  20. aawara New Member

    Also, these 2 microsoft KB articles will help you with the issue:-<br /<a target="_blank" href=http://support.microsoft.com/default.aspx?kbid=316739&amp<img src='/community/emoticons/emotion-4.gif' alt=';p' />roduct=winsvr2003>http://support.microsoft.com/default.aspx?kbid=316739&amp<img src='/community/emoticons/emotion-4.gif' alt=';p' />roduct=winsvr2003</a> and<br /<a target="_blank" href=http://support.microsoft.com/default.aspx?kbid=810371.>http://support.microsoft.com/default.aspx?kbid=810371.</a> Hope that it helps. Remember, you have a lot more granual control with /3gb switch for nonpagedpool size using /Userva in win2003. So dont quit. There is a lot more at stake than available memory. It should be a carefully calculated decision. Just not trial and error if its a real production (24 X7).
  21. gkrishn New Member

    TO sathya : yes,

    total memory manager:Total memory and memory manager:target memory counter of SQL server showin 7GB.

    Rajiv
    SQL-DBA
  22. aawara New Member

    Well, this is from your previous post:-<br />ok awara, i didnt mention /3GB, means OS is holding 2GB of memory. so balance available for me is only 6 GB.<br /><br />see, my SQL server is using 7GB when i enabled AWE and set 'max memory size' as 7GB. so question is available is only 6GB, usage is 7GB .how ???? <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />This is exactly what I was trying to explain. Task manager (processes tab) and target server memory just indicate the memory available (RAM = PAGE FILE). What the actual sql can attain is nothing close to this number as you mentioned yourself that without a /3GB switch, OS is holding 2 GB. SO the max available for any application should not be more than 6 GB. Right? So you have to figure out a way to find out true available memory for an application. Its a tedious process and include calculations of pool pagepoolsize nonpagedpool available bytes. From my experience, the memory managemnt overead is taken from memory itself so for a ^GB RAM system, if you are getting 5.5, you are lucky. But again, its something which you need to stress test on in test lab. You could use leaky app or something silly qruey to make joins in a manner to use RAM. The only way to find out is hands on......

Share This Page