This is one subject I have seen numerous times in various forums. Just what exactly do these settings mean and should you configure them in your SQL Server instances?
To answer the question whether you should configure them, it’s an emphatic yes. For the purpose of this post, we are going to assume a single SQL Server instance with 8GB RAM installed. I’ll cover the SQL Server versions as we delve into each point.
Before we discuss how a change can impact SQL Server, let us first look at how we can modify these parameters.
We can use the GUI to perform this task by right clicking on the database instance and choosing Properties. This will open the Server Properties box and you can then select the Memory Page.
Secondly, you can make changes using sp_configure. To make changes to memory, you must first set show advanced options to true.
sp_configure 'min server memory (MB)', 512 go sp_configure 'max server memory (MB)', 4096 go reconfigure go
You should receive two messages:
Configuration option ‘min server memory (MB)’ changed from 2147483647 to 512. Run the RECONFIGURE statement to install.
Configuration option ‘max server memory (MB)’ changed from 2147483647 to 4096. Run the RECONFIGURE statement to install.
To review the configuration
sp_configure 'min server memory (MB)' go sp_configure 'max server memory (MB)' go
I’ll kick of with min memory. There is a very popular misconception here that if I allocate 4GB (4096MB) as the minimum memory, SQL Server will immediately allocate 4GB on start up. It doesn’t. The memory footprint will be small and it will grow in parallel with your workload. Once the workload is sufficient to require 4GB or more, then the min setting kicks in and SQL Server will not free up memory if it means the instance would fall below the 4GB setting. This can be overridden by lowing the min setting manually.
The max memory is also one which is misunderstood and it caught me out too. Contrary to popular belief, the max memory is not what is allocated to SQL Server. In fact, prior to SQL Server 2014, the max memory value controlled the size of the buffer cache. With the release of SQL Server 2014, it controls the memory allocation for the buffer cache, clr memory, lock memory and all other caches (as well as some more). Like the min setting, if you set the max memory to 4GB, SQL Server will not take all of this memory on startup. Memory will be allocated as the workload increases on the instance.
In both cases, you can change the memory settings on the fly, however, this is not a recommended practice as the performance of your instance (and other instances on the server) could be severely affected.
So, now you know what the min and max actually does, what should you set the values to? The answer is “it depends”. Sorry, but it’s true. It will vary from instance to instance and is fully dependent on the database and workload. I’d recommend reading the fantastic Troubleshooting SQL Server; A Guide for the Accidental DBA book by Jonathan Kehayias, specifically page 145.
Please remember the settings I have used are very simplistic and based on a single 8GB instance. This is an advanced setting and can cause a lot of problems if it is not configured correctly. I’ve been burnt in the past.