High Memory Utilisation by SQL 2008 R2

Question

We have installed SQL 2008 R2 Enterprise on Window 2008 server.It has 24 GB of RAM.SQL server grabs all the memory during the optimization job and does not release it once the job has been completed.The only resolution is to restart the SQL services.Please advise how to resolve this issue.

Solution 1

This is by design.

SQL Server uses all the memory it can get hold of unless you explicitly limit the maximum memory usage in its configuration; it doesn't release it automatically when it's done using it, because it would anyway have to allocate it again anyway sooner or later. However, if the O.S. is in need of memory for other applications, SQL Server will happily release it; so you should not worry about this.

You can set a hard limit on maximum SQL Server memory usage, if you think there is a need for this; here is some documentation: http://msdn.microsoft.com/en-us/library/ms178067.aspx.

Solution 2

RTFM. Really. READ THE MANUAL.

SQL Server NEVER releases memory. It caches what it can from the database in case it needs it at a later stage. If you want to limit SQL Server, put a limit into the startup parameters. Even then, it will not release memroy unless other applications need it. And for enterprise level sql server, other applications is an epty set (i.e. there are none on the same server).

Normally SQL Servers are configured to use their memory as good as possible. Releasing memory means that you have to go back to the disc, if you need the data, and that is EXPENSIVE. As Disc IO is one main limiting factor of larger databases. Caching is good. Disc is slow. I have seen servers using 256gb memory and users being happy about it.

So, get more RAM (32, 64gb) it if makes sense. Be happy SQL Server uses it to the full degree. And at one point soon read the documentation ;)

Or: If you actually have configured SQL Server to releae memory AND (!) another app asks for it and does not get it - raise a bug with Microsoft.

Comments

[MrGigu]

See: serverfault.com/questions/86301/… and serverfault.com/questions/182559/… and serverfault.com/questions/144394/high-sqlservr-exe-memory-us‌​age

[Dave M]

Manual is always good but asking here is good as well. You learn either way!

[Massimo]

@Dave: this really is one of the most common questions ever asked about SQL Server. Googling for "SQL Server memory usage" really isn't that difficult.

[sam yi]

So many angry people on here. That's exactly how I got here.. by googling. Thanks for posting the question... made my "googling" easier.