Sign in
 
首頁文件庫學習園地下載支援社群論壇
IT 專業人員的技術資源 > 論壇首頁 > SQL Server Database Engine > Make use of wasted memory

已答覆 Make use of wasted memory

    •  
      2012年4月30日 下午 04:39
       
       
      登入以投票
      0
      登入以投票

      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. 
       

    解答

    • 2012年4月30日 下午 10:21
      版主
       
       已答覆
      登入以投票
      1
      登入以投票

      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.


       

    所有回覆

    • 2012年4月30日 下午 05:00
       
       
      登入以投票
      1
      登入以投票

      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

      • 已提議為解答 jgardner04 2012年4月30日 下午 05:32
      • 已取消提議為解答 jgardner04 2012年4月30日 下午 10:25
      •  
       
    • 2012年4月30日 下午 07:34
       
       
      登入以投票
      0
      登入以投票
      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.
       
    • 2012年4月30日 下午 07:40
       
       
      登入以投票
      0
      登入以投票

      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. 

       
    • 2012年4月30日 下午 07:43
       
       
      登入以投票
      0
      登入以投票

      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.

       
    • 2012年4月30日 下午 07:56
       
       
      登入以投票
      0
      登入以投票

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


       
    • 2012年4月30日 下午 08:02
       
       提議的解答
      登入以投票
      1
      登入以投票

      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

       
    • 2012年4月30日 下午 08:08
       
       
      登入以投票
      0
      登入以投票

      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.


       
    • 2012年4月30日 下午 08:14
       
       
      登入以投票
      0
      登入以投票

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


       
    • 2012年4月30日 下午 08:24
       
        包含代碼
      登入以投票
      0
      登入以投票

      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?

       
    • 2012年4月30日 下午 09:48
       
       
      登入以投票
      0
      登入以投票
      2147483647
      • 已提議為解答 jgardner04 2012年4月30日 下午 10:25
      • 已取消提議為解答 jgardner04 2012年4月30日 下午 10:25
      •  
       
    • 2012年4月30日 下午 10:21
      版主
       
       已答覆
      登入以投票
      1
      登入以投票

      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.


       
    • 2012年4月30日 下午 10:25
       
       
      登入以投票
      0
      登入以投票

      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. 

       
    • 2012年4月30日 下午 10:28
       
       
      登入以投票
      0
      登入以投票

      My understanding is also that Tom said it right.

      Josh

       
    • 2012年5月1日 上午 03:27
       
       
      登入以投票
      0
      登入以投票
      Thanks Tom for understanding and your prompt reply...Thanks everyone