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