Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server > SQL Server Forums > SQL Server Database Engine > Make use of wasted memory

Answered Make use of wasted memory

    •  
      Monday, April 30, 2012 4:39 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Our production database server currently has 56GB of installed physical memory. But it has Windows Server 2008 R2 standard Edition on X64bit. So only 32GB of memory is available for use based on the limitations from MS. When I check the properties of the computer it shows that the installed memory is 56GB(32GB is usable). IS there any other way that I can make use of the 24GB of memory that is being wasted? 

      The version of SQL Server I have is SQL Server 2008 R2 SE. 
       

    Answers

    • 18 hours 25 minutes ago
      Moderator
       
       Answered
      Sign In to Vote
      1
      Sign In to Vote

      SQL Server can only use the RAM available from the OS.   Windows 2008 R2 Standard edition only supports a maximum memory of 32GBs.  The rest is wasted and unavailable to all applications.  SQL Server 2008 R2 cannot use >32GBs of RAM on your server because the OS limitation, not SQL Server limitation.

      The extra RAM can only be used by SQL Server if you update your OS version to Windows 2008 R2 Enterprise.


       

    All Replies

    • 23 hours 45 minutes ago
       
       
      Sign In to Vote
      1
      Sign In to Vote

      So only 32GB of memory is available for use based on the limitations from MS.

      SQL Server 2008 R2 Standard edition can use up to 64 GB, so I don't understand, what's your problme?

      See http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx

      Olaf Helper
      * cogito ergo sum * errare humanum est * quote erat demonstrandum *
      Wenn ich denke, ist das ein Fehler und das beweise ich täglich
      Blog Xing

      • Proposed As Answer by jgardner04 23 hours 13 minutes ago
      • Unproposed As Answer by jgardner04 18 hours 20 minutes ago
      •  
       
    • 21 hours 12 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote
      I think I got it wrong..thanks for you clarification...when I saw that only 32GB of memory is usable I assumed SQL server 2008 R2 SE installed on this server is also limited to use memory out of this 32GB of physical memory. My question was if SQL can make use of the 56 GB memory so that I can set the max memory setting for sql server to atleast 50GB.
       
    • 21 hours 5 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Sapen2,

      The answer is, it depends.  If you do not have the Max Server Memory setting configured, SQL Server will use the memory if it needs it. 

       
    • 21 hours 3 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Yes according to below Standard edition can make use of max memory available to OS

      http://www.brentozar.com/archive/2010/06/sql-server-r-standard-supports-less-memory/

      Regards,
      Ahmed Ibrahim
      SQL Server Setup Team
      This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
      This can be beneficial to other community members reading the thread.

       
    • 20 hours 50 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Thanks for helping out guys...I am a little confused.. Below is a screenshot from MS website where it is highlighted that the windows server 2008 R2 SE is limited to use 32GB of memory. Even though 56GB of physical memory is installed the server shows that only 32 GB is usable per the screenshot below...Does SQL Server 2008 R2 SE still has access to 56 GB of RAM or just 32 GB? If it is 56GB I am planning on assigning the max server setting to 50GB if not I want to assign this to 29 GB....


       
    • 20 hours 44 minutes ago
       
       Proposed Answer
      Sign In to Vote
      1
      Sign In to Vote

      Sapen2,

      It looks like the screen shot you have is incorrect.  The marketing material differs from what is in BOL.  That is what Brent was talking about in the article you mentioned above.  You can see on this link that the BOL say that it is 64GB for SE 

      http://msdn.microsoft.com/en-us/library/ms143506(v=sql.105).aspx#SEx64

       
    • 20 hours 38 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote

      That is correct

      SQL Server 2008 R2 Standard (64-bit) x64

      The following table shows system requirements for SQL Server 2008 R2 Standard (64-bit) x64:

      Component

      Requirement

      Processor

      Processor type:

      • Minimum: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support

      Processor speed:

      • Minimum: 1.4 GHz

      • Recommended: 2.0 GHz or faster

      Operating system2

      Windows XP Professional SP2 x64

      Windows Server 2003 SP2 64-bit x64 Datacenter

      Windows Server 2003 SP2 64-bit x64 Enterprise

      Windows Server 2003 SP2 64-bit x64 Standard

      Windows Server 2003 R2 SP2 64-bit x64 Datacenter

      Windows Server 2003 R2 SP2 64-bit x64 Enterprise

      Windows Server 2003 R2 SP2 64-bit x64 Standard

      Windows Vista SP2 Ultimate x64

      Windows Vista SP2 Enterprise x64

      Windows Vista SP2 Business x64

      Windows Server 2008 SP2 x64 Datacenter, Windows Server 2008 SP2 x64 Datacenter without Hyper-V

      Windows Server 2008 SP2 x64 Enterprise, Windows Server 2008 SP2 x64 Enterprise without Hyper-V

      Windows Server 2008 SP2 x64 Standard, Windows Server 2008 SP2 x64 Standard without Hyper-V

      Windows Server 2008 SP2 x64 Web

      Windows Server 2008 SP2 for Windows Essential Server Solutions2

      Windows Server 2008 SP2 x64 Foundation Server

      Windows 7 x64 Ultimate

      Windows 7 x64 Enterprise

      Windows 7 x64 Professional

      Windows Server 2008 R2 64-bit x64 Datacenter

      Windows Server 2008 R2 64-bit x64 Enterprise

      Windows Server 2008 R2 64-bit x64 Standard

      Windows Server 2008 R2 64-bit x64 Web

      Windows Server 2008 R2 x64 for Windows Essential Server Solutions

      Windows Server 2008 R2 64-bit x64 Foundation Server

      Memory

      RAM:

      • Minimum:1 GB

      • Recommended: 4 GB or more

      • Maximum: 64 GB

      Regards,
      Ahmed Ibrahim
      SQL Server Setup Team
      This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
      This can be beneficial to other community members reading the thread.


       
    • 20 hours 32 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote

      But why does the server show only 32Gb is usable. Please find the screen shot of  server's properties below:


       
    • 20 hours 21 minutes ago
       
        Has Code
      Sign In to Vote
      0
      Sign In to Vote

      If you run

      sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
      EXEC sp_configure;
      GO

      What is the max server memory option set to?

       
    • 18 hours 58 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote
      2147483647
      • Proposed As Answer by jgardner04 18 hours 21 minutes ago
      • Unproposed As Answer by jgardner04 18 hours 21 minutes ago
      •  
       
    • 18 hours 25 minutes ago
      Moderator
       
       Answered
      Sign In to Vote
      1
      Sign In to Vote

      SQL Server can only use the RAM available from the OS.   Windows 2008 R2 Standard edition only supports a maximum memory of 32GBs.  The rest is wasted and unavailable to all applications.  SQL Server 2008 R2 cannot use >32GBs of RAM on your server because the OS limitation, not SQL Server limitation.

      The extra RAM can only be used by SQL Server if you update your OS version to Windows 2008 R2 Enterprise.


       
    • 18 hours 21 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote

      SQL Server can only use the RAM available from the OS.   Windows 2008 R2 Standard edition only supports a maximum memory of 32GBs.  The rest is wasted and unavailable to all applications.  SQL Server 2008 R2 cannot use >32GBs of RAM on your server because the OS limitation, not SQL Server limitation.

      The extra RAM can only be used if you update your OS version to Windows 2008 R2 Enterprise.

      Tom,

      You are correct. I didn't catch that on first read of the question.  The limit here is the OS not SQL Server. 

       
    • 18 hours 18 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote

      My understanding is also that Tom said it right.

      Josh

       
    • 13 hours 19 minutes ago
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Thanks Tom for understanding and your prompt reply...Thanks everyone