Freigeben über

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

  • Artikel

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

  • Anonymous
    August 03, 2006
    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.

  • Anonymous
    August 21, 2006
    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




  • Anonymous
    August 21, 2006
    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.

  • Anonymous
    January 11, 2007
    Well the MOSS 2007 x64 downloads are available via MSDN and MVLS. On MSDN, the ISO contains both x86

  • Anonymous
    July 27, 2008
    The comment has been removed

  • Anonymous
    May 28, 2009
    PingBack from http://paidsurveyshub.info/story.php?title=slava-oks-s-weblog-q-amp-a-i-can-t-make-sql-server-2005-32-bit-version

  • Anonymous
    July 14, 2009
    Is there any way to find out how much is the actual usage of memory by SQL: Server 2005 Enterprise Edition in 32 bit platform with AWE and Locked page enabled. I can see that even if the level of activity is not enough and there are no waits SQL Serevr is using all allocated memory. Can I trust SQLServer:Buffer Manager  Database Pages with more than 1500 Page Life Expectency and 0 free page stall/sec? Ran DBCC memorystatus and below is the excrept with 6GB Max Server Memory configuration:

Memory Manager                  KB

VM Reserved                    1743280 VM Committed                   122496 AWE Allocated                  6291456 Reserved Memory                1024 Reserved Memory In Use         0 (5 row(s) affected) Memory node Id = 0              KB

VM Reserved                    1739120 VM Committed                   118488 AWE Allocated                  6291456 MultiPage Allocator            29912 SinglePage Allocator           804824 (5 row(s) affected) MEMORYCLERK_SQLGENERAL (Total)                                    KB

VM Reserved                                                     0 VM Committed                                                    0 AWE Allocated                                                   0 SM Reserved                                                     0 SM Commited                                                     0 SinglePage Allocator                                            8008 MultiPage Allocator                                             2936 (7 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (Total)                                 KB

VM Reserved                                                     1552696 VM Committed                                                    64156 AWE Allocated                                                   6291456 SM Reserved                                                     0 SM Commited                                                     0 SinglePage Allocator                                            0 MultiPage Allocator                                             1960