locked
Memory management RRS feed

  • Question

  • Hi,

    My SQL server should utilize maximum memory , please let me know how to achieve this.

     

    Hardware Config : Dell Power Edge R710 , 96GB RAM , 4*600GB HDD (RAID-5)

    Software : Windows server 2003 Enterprise Edition 64bit & SQL 2005


    Karuna
    Friday, July 15, 2011 2:20 AM

Answers

  • Hi Shankar,

    I guess you are using Win 2003 ,SQL Server 64 bit 

    If You are using both 64 bit applications then you can utilize your total memory with out limits (32 bit apps has limitation)

    Open SQL Server Instance Properties >>memory tab>>set Minimum server memory and maximum server memory as per your requirements

     

    Same you can perform using below code.

    EXEC sys.sp_configure N'Min server memory (MB)', N'100'

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    EXEC sys.sp_configure N'max server memory (MB)', N'600'

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    Before configuring memory in production consider any applications running in same server,leave enough memory for OS as well.

    If you are using SQL Server 32 bit ,Above solution involves AWE.Which is a different topic.

    Please come back with your findings  

     

     


    Sivaprasad.L Together We can Achieve
    • Marked as answer by Stephanie Lv Friday, July 22, 2011 11:01 AM
    Friday, July 15, 2011 3:02 AM
  • You can also use Management Studio http://msdn.microsoft.com/en-us/library/ms191144.aspx
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Molly Bryant Monday, July 18, 2011 5:22 AM
    • Marked as answer by Stephanie Lv Friday, July 22, 2011 11:00 AM
    Friday, July 15, 2011 3:43 PM
    Answerer

All replies

  • What edition of SQL Server 2005 are you using? Also is it the 64x bit version?

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Friday, July 15, 2011 2:48 AM
  • Hi Shankar,

    I guess you are using Win 2003 ,SQL Server 64 bit 

    If You are using both 64 bit applications then you can utilize your total memory with out limits (32 bit apps has limitation)

    Open SQL Server Instance Properties >>memory tab>>set Minimum server memory and maximum server memory as per your requirements

     

    Same you can perform using below code.

    EXEC sys.sp_configure N'Min server memory (MB)', N'100'

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    EXEC sys.sp_configure N'max server memory (MB)', N'600'

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    Before configuring memory in production consider any applications running in same server,leave enough memory for OS as well.

    If you are using SQL Server 32 bit ,Above solution involves AWE.Which is a different topic.

    Please come back with your findings  

     

     


    Sivaprasad.L Together We can Achieve
    • Marked as answer by Stephanie Lv Friday, July 22, 2011 11:01 AM
    Friday, July 15, 2011 3:02 AM
  • You can also use Management Studio http://msdn.microsoft.com/en-us/library/ms191144.aspx
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Molly Bryant Monday, July 18, 2011 5:22 AM
    • Marked as answer by Stephanie Lv Friday, July 22, 2011 11:00 AM
    Friday, July 15, 2011 3:43 PM
    Answerer
  • Hi Karunashankar,

    You can leave the SQL Server max memory option to default if you want, since you have 96GB RAM but its best to set it to a number which make SQL use the maximum memory but like Siva1983 said make sure you leave enough for your OS.

    Another important recommendation is to enable "lock pages in memory option". This will improve your SQL Server performance.

    To enable this option check out this link http://msdn.microsoft.com/en-us/library/ms190730.aspx

    Sunday, July 17, 2011 11:10 PM