W2008R2, Huge ammount of memory allocated for sqlserv.exe

Question

Hi

We're running MSSQL2008R2 with sp1 on a W2008 standard server with 16GB RAM and we noticed that the sqlserv.exe proces is allocating al its max reserved memory (max is set to 14GB in Server properties). It's the only instance.

This can't be normal I think because other DB Servers (MSSQL2005 on W2003) are not showing this kind of behaviour.

What to do to find out what is wrong. I hope you can help.

Sincerly

Edward

Answer 1

I would start with setting 12 GB mac memory parameter to SQL Server. BTW do not forget to add an account that SQL Server runs under to Locked Page in Memory  local policy group

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

It is normal .. 

SQL Server grabs memory as on when required and is bit picky when it comes to releasing it..

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

hmm why 12?

Running on 64bit, I thought the Locked Page in Memory policy  is not necessary.

Answer 4

Really? on MSSQL 2005 it is not. Is this specific to 2008?

Answer 5

hmm why 12?

Running on 64bit, I thought the Locked Page in Memory policy  is not necessary.

No.. I think you are wrong.. 

Please read 

http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/ 

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 6

Really? on MSSQL 2005 it is not. Is this specific to 2008?

if this to me.. 

When i said SQL is bit picky when it comes to releasing memory..  my understanding unless there is some sort of memory pressure.. sql server will keep the memory with it..

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 7

What you are seeing is completely normal and expected behavior.

Please see:

http://support.microsoft.com/kb/321363

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

Answer 8

Hi Ed,

What you're describing is typical of SQL Server.  The amount of memory consumed by the SQL Server service ramps up over a period of time and tends to hold on to it unless the OS requests it back.  Setting Lock Pages in Memory prevents the OS from hard-trimming the SQL Server working set (as per the really good link that VT posted), causing it to be paged out to disk, which is much slower than memory.  However, if you don't leave enough room for the OS to breath, i.e. by setting a conservative max server memory value, OS intentsive operations will slow your server down as well - so it's about striking that fine balance.  You can always adjust the setting until you can get available RAM stablised at about 150-200MB.

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 9

Yes but it's on a virtualized environment (did not mention it) as a lot of servers are these days. Its impossible for the virtual machine manager to share the RAM across VM's id mssql is hogging it.

Answer 10

VM for SQL Server requires special considerations.

Please see this whitepaper on setting up SQL in VMWare.

http://www.vmware.com/files/pdf/sql_server_virt_bp.pdf

Answer 11

Thanks!