SQL Server “Max Server Memory” Config Value

6. October 2008 by Bennett.

I learned an interesting tidbit while reading Inside Microsoft SQL Server 2005: Query Tuning and Optimization.  The Max Server Memory configuration value applies to the cache buffer pool and not SQL Server as a whole.  Here is a link to an MSDN article that explains it:  http://msdn.microsoft.com/en-us/library/ms180797.aspx

This book goes on to discuss how to detect, measure, and remedy various forms memory pressure.  I followed some of the examples in the book and came up with these statistics on one server:

memory_stats1.html

One thing that is kind of interesting about these statistics is that it clearly shows that the configured server memory is significantly less than the memory used by sqlserver.exe.

Posted in SQL Server | Print | No Comments »