x64 SQL Server2008 R2 on Windows Server 2008 R2 - Max Memory usage value not honoured?

JimLad () writes:

I have been told by someone who ought to know that setting Max Memory
on a SQL server instance through the Properties-->Memory tab in SSMS
has no effect in the above environment. Is this correct?

No. "Max Server Memory" is always honored. Alhtough what you really control
is the size of the buffer cache, and SQL Server may use memory outside of
that. But that is usually not a lot.

Although, myself, I always use sp_configure directly, but that's the same
thing.

We have a Windows 2008 R2 box with IIS 7.5 and SQL 2008 R2 x64 on it
and I want to ensure SQL doesn't grab all the memory leaving IIS and
the .NET service struggling. Do I need to do anything or is the memory
management up to the task these days?

Even if SQL Server is the only process on the machine, it's usually better
to limit the memory anyway, even if it theory it should not be necessary.


Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sql...14207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sql...95970.aspx

On 2011-07-04 19:37, Erland Sommarskog wrote:

Even if SQL Server is the only process on the machine, it's usually better
to limit the memory anyway, even if it theory it should not be necessary.

You'll have to elaborate on that one, Erland. I don't recall seeing this in
any recommendation anywhere, and frankly I don't see why this is useful.
I've never had a server where auto-managed memory was a problem, except of
course the ones running Analysis Services and other memory hogs (for which
the setting is intended).

J.

On 2011-07-04 19:37, Erland Sommarskog wrote:

JimLad () writes:

I have been told by someone who ought to know that setting Max Memory
on a SQL server instance through the Properties-->Memory tab in SSMS
has no effect in the above environment. Is this correct?

No. "Max Server Memory" is always honored. Alhtough what you really control
is the size of the buffer cache, and SQL Server may use memory outside of
that. But that is usually not a lot.

Compared to the buffer pool it's not a lot, but it's not so small you can
ignore it if you're going to play around with the memory settings for a
reason.
http://blogs.technet.com/b/askperf/...ed-it.aspx
contains a detailed breakdown of the memory SQL Server uses aside from the
buffer pool -- along with some ideas of why you might want to limit max
server memory if you're running any sort of other application on the machine
(which still doesn't negate my observation that not setting it works just
fine if no significant other applications are present).

J.

Jeroen Mostert () writes:

You'll have to elaborate on that one, Erland. I don't recall seeing this
in any recommendation anywhere, and frankly I don't see why this is
useful. I've never had a server where auto-managed memory was a problem,
except of course the ones running Analysis Services and other memory
hogs (for which the setting is intended).

I have seen weird behaviours where the Lazywriter was taking a lot of CPU,
and which immediately went to rest when I set Max Server memory to 6 GB (on
an 8GB machine). Granted this was 32-bit SQL Server, but I also see my MVP
mates who are more into server configuration than I am to make the same
recommendation.


Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sql...14207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sql...95970.aspx