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.
Answer
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.
Attribution
Source : Link , Question Author : Sourav , Answer Author : Massimo
