locked
Memory 32bit vs 64bit, /PAE, AWE yes/no/maybe RRS feed

  • Question

  • The facts:
    64bit Windows 2003 Ent Ed with 64 gb of memory.
    No /PAE in the boot.ini file.
    32bit Windows SqlServer Ent Ed.

    I have scanned blogs, msdn, this forum and other places trying to get an handle on this. I'm not a Win Server guy, but I have a little bit of knowledge on Sql Server.

    It appears to me that:
    WinServer2003 sees 64 gigs as shown in task manager.
    SQLServer according to xp_msver sees 64 gigs
    SQLServer has topped out at ~3.7 gigs according to DBCC_MEMORYSTATUS.
    SQLServer Activity Monitor shows a lot of CX_PACKET, LOGFILEBUFFER, and various IO type waits.
    I currently have Max Degree of Parallelism set to 2, the box has 4 dual core processors.
    Below are listed the outputs the various folks have asked for when attempting to help others with similar questions.
    Mine is simple, how cam I get SQLServer to make use of more than 3.7 gigs?

    Thanks to all.

    Bruce


    Results of xp_msver are:

    1 ProductName NULL Microsoft SQL Server
    2 ProductVersion 589824 9.00.4035.00
    3 Language 1033 English (United States)
    4 Platform NULL NT INTEL X86
    5 Comments NULL NT INTEL X86
    6 CompanyName NULL Microsoft Corporation
    7 FileDescription NULL SQL Server Windows NT
    8 FileVersion NULL 2005.090.4035.00
    9 InternalName NULL SQLSERVR
    10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
    11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
    12 OriginalFilename NULL SQLSERVR.EXE
    13 PrivateBuild NULL NULL
    14 SpecialBuild 264437760 NULL
    15 WindowsVersion 248381957 5.2 (3790)
    16 ProcessorCount 8 8
    17 ProcessorActiveMask 255 000000ff
    18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
    19 PhysicalMemory 65531 65531 (68714168320)
    20 Product ID NULL NULL

    dbcc memorystatus shows
    Memory Manager
    VM Reserved 3687448
    VM Committed 3686432
    AWE Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0

    Memory node Id=0
    VM Reserved 3683288
    VM Committed 3682424
    AWE Allocated 0
    MultiPage Allocator 11640
    SinglePage Allocator 206016

    Server Properties/Memory
    Use AWE is not checked
    Min Server Memory = 0
    Max Server Memory = 2147483647
    Index Creation memory = 0
    Minimum memory per query = 1024


    Performance counter Private bytes instance=sqlservr average = 3834818560
    Performance counter Working Set instance=sqlservr average = 38286604928 






    Thursday, November 5, 2009 4:45 PM

Answers


  • In general you only need to set min and max memory if you have issues or run in a cluster.  But my experience has been that on heavily used systems you may want to so you can reserve enough memory for the OS.

    Read these articles for more information on SQL memory architecture
    Read the section Dynamic Memory Management and Memory Architecture of SQL 2005
    http://networkadminkb.com/kb/Knowledge%20Base/SQL/SQL%20Error%20A%20significant%20part%20of%20sql%20server%20process%20memory%20has%20been%20paged%20out.aspx


    How to configure SQL Server 2005 to use more than 2GB memory - the was written for 32bit OS, but the information about AWE is valid as it applies to the 32bit SQL...the information after step 5) is most usefull to you
    http://networkadminkb.com/kb/Knowledge%20Base/SQL/How%20to%20configure%20SQL%20Server%202005%20to%20use%20more%20than%202GB%20memory.aspx

    Configuring SQL 2005 Maximum Server Memory
    http://networkadminkb.com/kb/Knowledge%20Base/SQL/Configuring%20SQL%202005%20Maximum%20Server%20Memory.aspx

    You can probably install a second instance of SQL 2005 64bit...as far as i know they can run on the same server.
    Friday, November 6, 2009 2:48 PM
  • I'm pretty sure since you are running 32 bit SQL you need to enable AWE to use more memory.  Otherwise 32bit SQL can't reference beyond the 4GB limit of that architecture.  You really need to run 64bit SQL to get fully use of that memory.

    AWE is essentially the programming API that allows 32bit SQL to access memory beyond th 4GB limitation of 32bit system.  PAE was the 32bit implementation that allow the Hardware to access beyond the 4GB limitation of a 32bit system.  PAE is not needed on 64bit systems.

    Essentially a 32bit processor is limited by 2 to the 32nd power (2^32 = 4GB)  as all the memory it can reference.  Thus the workaround mention above.

    Thursday, November 5, 2009 10:35 PM
  • If you're enabling AWE then it's advisable to cap the memory usage to ensure the OS has room to breathe.
    You can't of course upgrade over an existing 32-bit installation with 64-bit, but you'll be able to carry out a side-by-side install and move the database(s) either using backup/restore or detach and attach, without any issues.
    Your 64-bit access driver problem will remain if you choose to upgrade, however, unless MS (or a third party) release a 64-bit driver.



    ajmer dhariwal || eraofdata.com
    Friday, November 6, 2009 7:55 PM
    Answerer
  • I was just thinking about this some more.... I can't think of a reason you would want to keep 32-bit SQL on 64-bit Windows, but you should be able to just enable AWE to have SQL see and use the extra RAM.
    Friday, November 6, 2009 1:12 AM
  • Thanks to all who have helped me through this. I enabled AWE and rebooted SQL this morning. Now my target server memory shows 55377920, and right now total server memory is sitting at 10374400(K).

    Thanks again,

    Bruce
    Wednesday, November 11, 2009 6:16 PM

All replies

  • It sounds like you have everything set up right.... there's no need for pae or awe on 64-bit win in order for SQL to see and use the ram.  I think you may just need to load some big data sets in order for SQL to start consuming RAM.  It will only take what it needs.
    Thursday, November 5, 2009 4:56 PM
  • Take a look at perfmon memory counters sql server total server memory and target server memory and let us know what you see there.  Those tell what sql is set to consume and what it is consuming.   Also, as Tome said, it may be the case that you just haven't loaded things into memory yet.  In perfmon, what's the buffer cache hit ratio look like?

    Also, did you enable lock pages in memory for the sql server service account--nothing to do with this but something you should be aware of.  
    Thursday, November 5, 2009 6:14 PM
  • Hmmm, it seems I have no SQLServer counters in perfmon. I'll try and see what the problem could be.
    SQL runs under a user called gfmbservice, it's part of the admin group, which has Lock Pages In Memory rights. I did however add that specific user to the list, just in case it makes a difference.

    I'll get back after I figure out how to enable the SqlServer counters.

    EDIT Got my SQL counters back.

    Total Server Memory (KB) 3604480  All the values are the same Last, Ave, Min, Max.
    Target Server Memory (KB) 3604480.
    Buffer cache hit ratio 99.671 average.


    Bruce
    Thursday, November 5, 2009 6:48 PM
  • I'm pretty sure since you are running 32 bit SQL you need to enable AWE to use more memory.  Otherwise 32bit SQL can't reference beyond the 4GB limit of that architecture.  You really need to run 64bit SQL to get fully use of that memory.

    AWE is essentially the programming API that allows 32bit SQL to access memory beyond th 4GB limitation of 32bit system.  PAE was the 32bit implementation that allow the Hardware to access beyond the 4GB limitation of a 32bit system.  PAE is not needed on 64bit systems.

    Essentially a 32bit processor is limited by 2 to the 32nd power (2^32 = 4GB)  as all the memory it can reference.  Thus the workaround mention above.

    Thursday, November 5, 2009 10:35 PM
  • The OS might be 64-bit, but the process concerned is 32-bit, so it will be running under WOW64 and constrained to a 4 GB address space.
    Please see Overview of the compatibility considerations for 32-bit programs on 64-bit versions of Windows Server 2003 and Windows XP and Performance and Memory Consumption Under WOW64.

    Just wondering why awe hasn't been enabled?


    ajmer dhariwal || eraofdata.com
    Thursday, November 5, 2009 10:56 PM
    Answerer
  • eee gads... I didn't notice that in the original post... you should definitely run 64-bit sql.
    Thursday, November 5, 2009 11:46 PM
  • I was just thinking about this some more.... I can't think of a reason you would want to keep 32-bit SQL on 64-bit Windows, but you should be able to just enable AWE to have SQL see and use the extra RAM.
    Friday, November 6, 2009 1:12 AM
  • OK Two followup questions.

    To enable AWE I select the Use AWE to allocate memory check box. Should I then set min and max memory, I read some posts which say yes, other say no. I assume I'll need to restart the server for it to take effect.

    Is is possible to upgrade my 32bit sql to 64bit, or do I need to unistall 32bit and then install 64bit? We're looking to move to SQL2008 next year and one of our problems we need to solve is no 64bit driver for MSACCESS. Are we facing the same problem if we go to 64bit SQL2005?

    Thanks for all the help and information, I'm sort in over my head right now just in case you couldn't tell.


    Bruce
    Friday, November 6, 2009 12:08 PM

  • In general you only need to set min and max memory if you have issues or run in a cluster.  But my experience has been that on heavily used systems you may want to so you can reserve enough memory for the OS.

    Read these articles for more information on SQL memory architecture
    Read the section Dynamic Memory Management and Memory Architecture of SQL 2005
    http://networkadminkb.com/kb/Knowledge%20Base/SQL/SQL%20Error%20A%20significant%20part%20of%20sql%20server%20process%20memory%20has%20been%20paged%20out.aspx


    How to configure SQL Server 2005 to use more than 2GB memory - the was written for 32bit OS, but the information about AWE is valid as it applies to the 32bit SQL...the information after step 5) is most usefull to you
    http://networkadminkb.com/kb/Knowledge%20Base/SQL/How%20to%20configure%20SQL%20Server%202005%20to%20use%20more%20than%202GB%20memory.aspx

    Configuring SQL 2005 Maximum Server Memory
    http://networkadminkb.com/kb/Knowledge%20Base/SQL/Configuring%20SQL%202005%20Maximum%20Server%20Memory.aspx

    You can probably install a second instance of SQL 2005 64bit...as far as i know they can run on the same server.
    Friday, November 6, 2009 2:48 PM
  • If you're enabling AWE then it's advisable to cap the memory usage to ensure the OS has room to breathe.
    You can't of course upgrade over an existing 32-bit installation with 64-bit, but you'll be able to carry out a side-by-side install and move the database(s) either using backup/restore or detach and attach, without any issues.
    Your 64-bit access driver problem will remain if you choose to upgrade, however, unless MS (or a third party) release a 64-bit driver.



    ajmer dhariwal || eraofdata.com
    Friday, November 6, 2009 7:55 PM
    Answerer
  • Thanks to all who have helped me through this. I enabled AWE and rebooted SQL this morning. Now my target server memory shows 55377920, and right now total server memory is sitting at 10374400(K).

    Thanks again,

    Bruce
    Wednesday, November 11, 2009 6:16 PM