Hi,

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?

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?

Cheers,

James

JimLad (jamesdbirch (AT) yahoo (DOT) co.uk) writes:
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.

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, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 2011-07-04 19:37, Erland Sommarskog wrote:
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:
ignore it if you're going to play around with the memory settings for a
reason.
http://blogs.technet.com/b/askperf/a...y-need-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 (jmostert (AT) xs4all (DOT) nl) writes:
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, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx