Memory usage grows and grows

Hi!
I have an issue with sql server 2005. It weems that the memory usage grows
forever and never decreases. If I get users on they use a certain amount of
memory. If they all get off the system, the memory usage never goes down. If
they get back on it then grows. Eventually the machine starts to thrash
because it runs out of physical memory. What's up with that?
Joe
Joe D. wrote:
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount of
> memory. If they all get off the system, the memory usage never goes down. If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that?
> Joe
That's normal behavior for SQL Server - it caches data pages in memory
to minimize disk I/O, and won't release memory unless the O/S requests
it. Part of your job, as the admin, is to balance the memory use
between SQL and the O/S to prevent that "thrashing".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The first half of the description is more or less the expected behavior, and
not a problem. But the following is not the expected behavior:

> Eventually the machine starts to thrash because it runs out of physical memory.
Could you be more specific about 'thrash' and 'runs out of physical memory'?
Linchi
"Joe D." wrote:

> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount of
> memory. If they all get off the system, the memory usage never goes down. If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that?
> Joe
|||Yes...
It appears that it starts using swap space. The CPU load jumps and the
machine slows to a crawl. If I get everyone off and restart the instance,
all gets back to normal. This normally takes about 4 - 5 hours to degrade to
the point of restart.
Joe
"Linchi Shea" wrote:
[vbcol=seagreen]
> The first half of the description is more or less the expected behavior, and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical memory'?
> Linchi
> "Joe D." wrote:
|||The expected behavior is for SQL Server to release memory when the
system becomes memory constrained, just as it is expected to keep
grabbing more when it is available. However for situations like you
describe SQL Server has a configuration setting to limit the total
memory it will use, and it sounds like you need to use it to allow
enough memory for whatever else is on the system.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Jan 2007 12:01:02 -0800, Joe D.
<JoeD@.discussions.microsoft.com> wrote:

>Yes...
>It appears that it starts using swap space. The CPU load jumps and the
>machine slows to a crawl. If I get everyone off and restart the instance,
>all gets back to normal. This normally takes about 4 - 5 hours to degrade to
>the point of restart.
>Joe
|||This is a very common problem and I wish the OS and SQL groups would address
it. SQL Server will hold onto all memory until there is 5 MB of free
memory. It appears as if the disk cache is included in the free memory and
is usually much larger than 5 MB. Therefore, SQL Server never releases
memory. The disk cache will swap the SQL buffer out of memory to disk in
order to make more room for the disk cache if the disk cache is getting too
small. This means that SQL Server will NEVER release memory as long as you
have swap space left. Your only option is to set the max memory of SQL
server to a lower amount so that there is reserved free space for the
OS/disk cache.
I've experienced this myself numerous times including on a 32 GB 4 way
server running the 64 bit version of SQL 2005. This was a box for a data
warehouse and the users complained that it sometimes was very slow. Looking
at the max cache size, it was sometimes using 4 GB of RAM for the disk
cache, but SQL was using 30 GB of RAM (30 + 4 isn't 32). I had to set the
max memory of SQL to 27 GB and their performance improved dramatically.
You can use "Memory:Cache Bytes Peak" to see the max amount of memory used
for cache and "Memory:Cache Bytes" to see the current amount. You can also
look for the committed bytes + cache being higher than physical memory.
This indicates that you are having memory pressure and probably need to
adjust something.
Thanks,
Stephen Mills
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:D305F0C7-355A-461E-8E1A-1C2B2413E87D@.microsoft.com...[vbcol=seagreen]
> The first half of the description is more or less the expected behavior,
> and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical
> memory'?
> Linchi
> "Joe D." wrote: