memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.codecomments.com ***> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creat
ing execution plans are
expensive operations. This is why SQL Server caches plans and pages. How wou
ld SQL Server know when
to release something? SQL Server will release memory when the OS becomes pre
ssured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max
server memory).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.
gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>