Re: Limit use of memory cache

I experienced the same problem on a SQL Server with 16GB
of RAM configured MIN 4GB and MAX 12GB.
When the backup started to run at night to backup about 200GB
..bak file over the network, the server's memory started to shrink
and sqlservr.exe memory started to shrink and file system's cache
was using it all.

A manual file copy over the network had the same effect.

The problem seems to have been resolved after I've configured
the RAM for SQL Server to be MIN/MAX 12GB! Now there
seem to be always 2-2.5GB of free memory available and the
backup job is no longer causing it to steal memory and have
the system cache grow to 12-14GB!

If you Google "FileSystemCache" among others you will see
about this problem. It is basically the OS when reading a
file, it puts it in the cache just in case it needs to read that data again.
So, there are apparently ways to disable "BUFFER" copying
by changing the OS setting: My computer, properties, advanced,
.... the setting for memory "application" or "system cache" I believe.
There were also articles where you are supposed to change
your network connection's FILE AND PRINT Services for
Microsoft Network to the fourth option "...for Network Applications",
in fact the same is written in SQL 2005 Books Online Memory
page I believe.

In any case the problem got fixed by setting SQL RAM to
MIN/MAX 12 and not the changes of the other settings.

Now, I'm trying to determine whether to set it to
14/14, or MIN 13 and MAX 14 to use up more RAM
for the SQL Server. I'll post it now as a new thread.




Ed Murphy (emurphy42@xxxxxxxxxxxx) writes:
A couple users at one client are complaining of early morning
slowness in their OLTP system. The first thing that came to
mind is that the nightly backup might be using close to 100% of
the memory cache. (IIRC, they have 16 GB of RAM, 12 of which
is reserved for MSSQL, while the DB is roughly 30 GB.)

Is there a way to limit how much memory cache the backup uses? (It
only takes about 15 minutes now, so we can afford to slow it down
in order to speed user operations up.)
I would first analyse whether the problem is really due to that cache having been flushed.

How would I go about that? Profiler trace, looking for lots of cache
misses during the early morning?

If that really is the case, I would investigate what might be flushing the
cache. I don't know for sure, but I don't think it's the backup. They could
have some nightly batch jobs that slurps memory. Or for that matter, they
may be defragmenting tables every night. (And who knows: may be the
reindexing has started to spill into early morning, and that's why the
users find the system slow.)

There are some nightly batch jobs, though nothing comes to mind that
would obviously have this level of impact. I'll have to poke at it
some more this coming week.


.

Relevant Pages

  • Re: 300MB DB uses 1.4GB RAM!?
    ... DBCC MEMORYSTATUS will tell you how SQL Server memory manager has ... So, if every page was in memory, there's 300mb. ... the more ram SQL takes the better!! ... allocation for SQL Server. ...
    (microsoft.public.sqlserver.clustering)
  • Re: memory usage/allocation/paging question
    ... paging to disk more often if one reduces the amount of available RAM ... the more data and execution plans SQL Server can hold in cache, ... If other applications on the same box calls for more memory, ... If you are running IIS on the same box, ...
    (comp.databases.ms-sqlserver)
  • Re: Low on Virual Memory in SBS 2003
    ... Am I supposed to be putting a cap on the maximum amount of RAM that Exchange ... I don't think a memory leak is the only explanation for this. ... Although the SQL Server has been running since the first day, ... The SQL server logs seem to be clean and I am not sure what to look ...
    (microsoft.public.windows.server.sbs)
  • Re: Backup fails with volsnap error
    ... amount of RAM for Trend to be using, since based on what you're posting here ... it seems to be using half the RAM on the server. ... using NtBackup via the SBS backup. ... Obviously the TrendMicro AV is also a memory hog. ...
    (microsoft.public.windows.server.sbs)
  • Re: Planning for a new server
    ... > The current server only has only 1GB of RAM, of which, like you say, the ... > and IIS are going to compete for. ... >> you probably don't need all of that in memory all the time. ... >> are using Std Edition of SQL Server which can only use 2GB of memory. ...
    (microsoft.public.sqlserver.setup)