When is Max Memory in SQL Server not Max Memory?

When thinking about configuration parameters for SQL Server, one of the first ones to look at is the sp_configure option ‘max server memory’.  In versions up to SQL Server 2008 R2, max memory referred to physical memory. The versions indicated that the max memory that it could use was 64GB (note: SQL 2008 R2 and below).

Max memory

Changes to Max Memory

In SQL Server 2012, according to this KB Article in the bottom paragraph of the article, after it explains the changes in SQL 2012 for what is included in the governance of max server memory, it explains that in SQL Server 2012 the limitation of the max physical memory is all about Buffer Pool (database cache) and not other things.

In other words, if I have a server with 96 GB of RAM and I put SQL 2012 Standard edition on it, I could set ‘max server memory’ to a number higher than 64 GB and SQL Server would limit Buffer Pool to 64 GB and could use memory higher than 64GB for other things outside the buffer pool. This may not sound like a big deal, but think about wanting more buffer pool but still needing to fit inside the memory limits.

This new allocation allows SQL Server 2012 and higher to use up to the physical limit specified by SQL Server 2012 (Standard = 64GB) and SQL Server 2014 (Standard = 128GB), and to specify a higher number for max server memory than the specified limit of RAM. In this configuration SQL Server will use up to 64GB/128GB for Buffer Pool and memory above that for other parts of SQL Server allocations.

In Enterprise Edition you are limited in RAM only in the limitations of the Operating Systems ability to address that amount of RAM so other editions of SQL Server are the benefactors of this feature.

I would not think of this as a silver bullet, but I think in many cases you can extend the usefulness of the feature if your servers have more RAM than SQL Server can address for the Buffer Pool if you are using Standard and below.

Please share this

Leave a Reply

Related Articles