memory usage

Hi,
On a webserver I have a small sharepoint-application with SQL Server 2005
Express:
As users click through the web-app I can easily see that the amount of the
memory usage by the SQL Server is constantly increasing. When the application
is idle or all web-users are away from the application, the memory usage of
SQL Server remains on the last level without decreasing. Why?
Can we decrease the memory usage by SQL Server as soon as "nothing happens"
on the wep-application?
Can we decrease this memory usage permanently for example every minute?
Thanks for your help.
regard
PatrickSQL Server caches data and index pages after reading them from disk, for performance reasons.
Decreasing memory usage and hence removing this from cache would defeat caching in the first place.
You can set a max server memory (see sp_configure).
Have a look at:
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:C4E886E0-3010-4D64-A3AE-16CCBC28D68E@.microsoft.com...
> Hi,
> On a webserver I have a small sharepoint-application with SQL Server 2005
> Express:
> As users click through the web-app I can easily see that the amount of the
> memory usage by the SQL Server is constantly increasing. When the application
> is idle or all web-users are away from the application, the memory usage of
> SQL Server remains on the last level without decreasing. Why?
> Can we decrease the memory usage by SQL Server as soon as "nothing happens"
> on the wep-application?
> Can we decrease this memory usage permanently for example every minute?
> Thanks for your help.
> regard
> Patrick|||This isn't an area I've gotten into much, but I would bet that the increased
memory is in the buffer cache - the place in memory where SQL Server is
keeping the pages read from disk.
The reason for this is that a data page that was requested from disk, is
very likely to be requested again and by keeping it in the buffer cache
(memory) a future request for it can be done much faster.
Now, the problem I've always had is: how to I allocate as much memory as I
can to the buffer cache, not how do I minimize, or clear it. However, I know
that 'sp_configure' controls these type of things and that I've seen a
reference to something that will clear the cache (for testing).
Search BOL for "Buffer Management" and read it. Then, look at the page for
"DBCC DROPCLEANBUFFERS".
Jay
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:C4E886E0-3010-4D64-A3AE-16CCBC28D68E@.microsoft.com...
> Hi,
> On a webserver I have a small sharepoint-application with SQL Server 2005
> Express:
> As users click through the web-app I can easily see that the amount of the
> memory usage by the SQL Server is constantly increasing. When the
> application
> is idle or all web-users are away from the application, the memory usage
> of
> SQL Server remains on the last level without decreasing. Why?
> Can we decrease the memory usage by SQL Server as soon as "nothing
> happens"
> on the wep-application?
> Can we decrease this memory usage permanently for example every minute?
> Thanks for your help.
> regard
> Patrick