-1

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.

Massimo
  • 70,200
  • 57
  • 200
  • 323
Sourav
  • 7
  • 1
  • 2
  • See: http://serverfault.com/questions/86301/sql-server-memory-usage-high-7gb-page-file-usage-high-7gb-but-cpu-usage-2 and http://serverfault.com/questions/182559/sql-server-memory-constantly-going-up-from-1gb-ram-used-to-7gb-in-a-week and http://serverfault.com/questions/144394/high-sqlservr-exe-memory-usage – Mark Henderson Feb 09 '11 at 20:59

3 Answers3

13

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.

Massimo
  • 70,200
  • 57
  • 200
  • 323
7

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.

TomTom
  • 51,649
  • 7
  • 54
  • 136
  • 2
    Manual is always good but asking here is good as well. You learn either way! – Dave M Feb 09 '11 at 19:37
  • 1
    @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. – Massimo Feb 09 '11 at 19:58
  • 3
    So many angry people on here. That's exactly how I got here.. by googling. Thanks for posting the question... made my "googling" easier. – sam yi Jan 30 '13 at 21:58
0

Here is a good resource regarding SQL Server memory configuration; it talks about releasing memory towards the bottom of the blog.

DaniSQL
  • 1,107
  • 7
  • 12