Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
  • 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?

share|improve this question
    
How big is your database? –  SuperCoolMoss Sep 17 '09 at 20:20
    
so, what was the answer to this? –  djangofan Dec 10 '09 at 21:10

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
SQL Server can release memory back to the OS if the OS is under memory pressure –  Nick Kavadias Aug 12 '09 at 9:09
    
I see, I don't have load now so I'll just have to wait until it goes up. Thanks! –  ArjanP Aug 12 '09 at 10:14
    
The part about "it will retain that forever" is not true on virtual servers. On VMware (like this question), there's a balloon driver running inside the OS that will periodically request more memory, faking OS memory pressure, in order to get other apps to let go of memory. This helps virtual machines share memory better, but it hurts SQL performance. Just an FYI. –  Brent Ozar Jan 29 '10 at 12:45

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.

share|improve this answer
    
I did that now, set min and max to 12 GB It's only using 380 MB now instead of 3.6 GB.. isn't it supposed to take 12 GB straight away? –  ArjanP Aug 12 '09 at 4:13

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.