Limit use of memory cache

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.)

Are there other worthwhile tricks in this area? (I suppose they
could set up replication and then only back up the replicated DB,
but I'm sure that would be overkill. They do some moderately heavy
reporting, but not nearly /that/ heavy.)

Re: Limit use of memory cache

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.

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.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Limit use of memory cache

Erland Sommarskog wrote:

> 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.

Re: Limit use of memory cache

Ed Murphy (emurphy42@socal.rr.com) writes:
> Erland Sommarskog wrote:
>> 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?

Those are cache misses for execution plans, not cache misses for data
buffers.

I will have to confess that I don't really have a recipe out of the
cookbook to offer, but one idea is to study the Buffer Cache Hit Ratio
performance counter. Sample both during day time, and in the wee hours
when the users are complaining.

There is also the DMV sys.dm_os_buffer_descriptors, which looks very
interesting on paper, but when I have used it, the results have not
appeared to be entirely reliable.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Limit use of memory cache

Ed Murphy (emurphy42@socal.rr.com) writes:
> Erland Sommarskog wrote:
>> 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?

There is of course, the traditional route of running a trace to catch
slow procedures and monitor blocking.

That may reveal something that leads you to diffierent track altogether.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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@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.
>