|
|
Posted by serge on 09/27/83 12:00
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@socal.rr.com) 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.
>
Navigation:
[Reply to this message]
|