Q & A: I can’t make SQL Server 2005 32 bit version to use all memory on the machine


I’m currently using the RTM+SP1 x86-SQL2005 build on a server which has 32GB of physical memory.  I notice that during ramp-up the memory usage (as seen under task manager) increases to about 28GB (i.e. “Available memory” reduces to 4GB) and stays there.   


 



  1. Is there a way to get around this and make SQL use at least a part of the remaining 4GB?So far for the “max server memory (MB)” I tried values of 2147483647(default setting) and 30000.  Both resulted in similar behavior as described above.  Attached is output of my sp_configure. I also tried to set min=max without any success

 



  1.  As all of you know limited amount of Virtual Address Space, VAS, might be a problem on 32bit systems.  For  SQL Server 2005 we made a decision to minimize VAS issues at the cost of amount of max physical memory SQL Server can actually use. The reason we made such decision is because for SQL Server VAS problems are much more severe usually than limitations of physical memory.  As you might expect, for SQL server to use additional physical memory it does need to allocate extra VAS regions. It means that for larger amounts of physical memory SQL Server will use more of VAS. If you want SQL Server 2005 to make use of all physical memory, basically get behavior of SQL Server 2000, you can use trace flag 836 during SQL Server startup. Remember this behavior is very 32bit specific

 


Hope this helps

Comments (6)

  1. TravisOwens says:

    Perhaps I’m stating the obvious, and mix in the fact is always easy to tell somebody to "just upgrade" because it’s not my money, despite…

    Anybody that needs this much ram needs to upgrade to 64bit SQL and 64bit Windows.

  2. mike vessey says:

    i think you should have a look at this

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx

    it states

    When allocating SQL Server AWE memory on a 32-GB system, Windows 2000 may require at least 1 GB of available memory to manage AWE. Therefore, when starting an instance of SQL Server with AWE enabled, it is recommend you do not use the default max server memory setting, but instead limit it to 31 GB or less.

    but you also can’t use the 3GB switch above 16Gb – so 2Gb for kernel and 1Gb for awe management – – unless i’m mistaken that’s 29GB??  Travis is right though – upgrade to 64bit

  3. slavao says:

    Mike, you are exactly right that AWE management requires extra OS resources. However the point of this post is to let you guys know that SQL Server favors Virtual Address Space to physical memory. This choice internally precludes us from using some of the physical memory that otherwise is available to us.  The above mentioned trace flag will reverse SQL Server internal behavior.  

    Also I would like to make sure that all of us on the same page here: 2GB is a size of kernel’s VAS not amount of actual RAM consumed by kernel.

  4. Well the MOSS 2007 x64 downloads are available via MSDN and MVLS. On MSDN, the ISO contains both x86