SQL Server 2005 64bit on 16GB machine uses 3.6GB memory only

Question

• Maximum server memory is set to the maxvalue
• AWE is disabled (should not be needed in 64 bit anyway)
• Windows Server 2008 Enterprise SP2
• It is a virtual server using VMWare

If I look in Task Manager the sqlservr.exe process only uses about 3.6 GB of memory. Is that number not real? Shouldn't it attempt to use all available memory?

If I run DBCC MEMORYSTATUS I get:

VM Reserved 16670136
VM Committed    3640664

It looks like a memory limit I shouldn't be seeing in a 64 bit environment.. how can I get SQL2005 to use more memory?

Answer 1

No.If you restart SQL server service memory will be given back to OS.What the setting means is once the memory reaches 12 GB consumed by SQL server,SQL server will retain that memory.Even if now you do not require the data and transaction is complete,still the memory will remain with SQL server.So once SQL server reaches 12 GB consumption,it will retain that forever till you restart the service.

Answer 2

Have you set the minimum server memory?

http://msdn.microsoft.com/en-us/library/ms178067.aspx

If you set minimum server memory and maximum server memory to the same value, SQL Server will allocate that amount of memory (if available) to the buffer pool. Of course, you don't really want to allow SQL Server to use all of the memory on your server - you need to reserve at least some of it for the OS (the amount depends upon whether or not you are running anything else in the same machine).

HTH.

Answer 3

You can take a look at target server memory in PerfMon to see how much memory SQL server is requesting. A little bit offtopic, you can compare total server memory and target server memory to see if you have memory constraints. When target i greater than total you need to add more memory.