locked
Using RAM up to 3 Gb for SQL SERVER 2005 RRS feed

  • Question

  • hi,

     

    I have windows 2003 server Enterprise Edition,SQLSERVER 2005 Enterprise Edition,4 GB ram,I want my SQL SERVER 2005 to use Physical memory up to 3 GB(RAM) insted of 2GB which it is currently using now, I tried Following Commands

     

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
     
    sp_configure 'awe enabled', 1
    RECONFIGURE

     

    After  restarting

    sp_configure 'min server memory', 1024
    RECONFIGURE
    GO
     
    sp_configure 'max server memory', 3072
    RECONFIGURE
    GO

     

    But this Option is not working.

    Also tried "/fastdetect /NoExecute=OptOut /3GB" in  boot.ini FILE

     

    If possible please give solution for the problem
     

    Thanks and  regards

    vishal nikam(nikam.vishal@indiatimes.com)

    Friday, September 25, 2009 2:28 PM

Answers

  • Hi vishal,

    All you done is fine.

    Only one thing to be confirmed, did you restart the server after adding "/3gb" to the boot.ini?

    Please try to restart the server again to solve the issue.

    For more information, please see:
    Enabling AWE Memory for SQL Server:http://msdn.microsoft.com/en-us/library/ms190673(SQL.90).aspx
    How to configure SQL Server to use more than 2 GB of physical memory: http://support.microsoft.com/kb/274750

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, September 28, 2009 8:31 AM
  • SQL Server will never use all 3GB.  With the /3GB switch enabled on the OS (you don't need AWE turned on), you'll use about 2.8 GB.  But, SQL Server is only going to allocate memory as it needs it.  If it doesn't need all 2.8GB, then it isn't going to allocate that much.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, September 29, 2009 3:46 PM
  • Running a bunch of queries, just to force SQL Server to allocate more memory is rather pointless.  It is going to use what it needs to use and as long as something else doesn't need the memory, it will keep it allocated.  Just leave it configured and run whatever processing you need to run.  If it ever needs that much memory, it will allocate it.  If all your current processing needs is 75MB of RAM, then SQL Server isn't going to run any faster with 100MB of memory allocated than it will with 2.8GB of memory allocated.  Unused memory is simply that, unused - in other words "I don't need the memory space for anything at all, so I'm not going to allocate it in the first place".
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Wednesday, September 30, 2009 12:12 PM

All replies

  • Hi vishal,

    All you done is fine.

    Only one thing to be confirmed, did you restart the server after adding "/3gb" to the boot.ini?

    Please try to restart the server again to solve the issue.

    For more information, please see:
    Enabling AWE Memory for SQL Server:http://msdn.microsoft.com/en-us/library/ms190673(SQL.90).aspx
    How to configure SQL Server to use more than 2 GB of physical memory: http://support.microsoft.com/kb/274750

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, September 28, 2009 8:31 AM
  • Hi Jin Chen, First of all thanks for your reply, ok i restarted the server after adding "/3gb" to the boot.ini but then also problem is not solved, actually with AWE option i am able to use 73,268 KB of RAM (SHOWN in TASK MANAGER), and without AWE option i am able to use up to 1.5 GB RAM, but not able to reach up to 3 gb, Dont know exactly what i am missing can i have any other alternative ? THANKS AND REGARDS VISHAL NIKAM
    Tuesday, September 29, 2009 12:19 PM
  • SQL Server will never use all 3GB.  With the /3GB switch enabled on the OS (you don't need AWE turned on), you'll use about 2.8 GB.  But, SQL Server is only going to allocate memory as it needs it.  If it doesn't need all 2.8GB, then it isn't going to allocate that much.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, September 29, 2009 3:46 PM
  • ok thanks for Information , i will try to use some complex queries to reach up to 2.8 GB ram
    Wednesday, September 30, 2009 7:30 AM
  • Running a bunch of queries, just to force SQL Server to allocate more memory is rather pointless.  It is going to use what it needs to use and as long as something else doesn't need the memory, it will keep it allocated.  Just leave it configured and run whatever processing you need to run.  If it ever needs that much memory, it will allocate it.  If all your current processing needs is 75MB of RAM, then SQL Server isn't going to run any faster with 100MB of memory allocated than it will with 2.8GB of memory allocated.  Unused memory is simply that, unused - in other words "I don't need the memory space for anything at all, so I'm not going to allocate it in the first place".
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Wednesday, September 30, 2009 12:12 PM