In this article, let’s understand the Minimum and Maximum server memory settings of SQL Server. The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the SQL Server Database Engine. The buffer pool starts with only the memory required to initialize. As the workload increases, it keeps acquiring the memory required to support the workload.
When you peek into the SQL Server box for memory, you almost always find it
is hogging all the available memory. This is because SQL Server is given the
freedom to do so. The default Memory setting on SQL Server is as shown in the
figure below; the min server memory is 0 and the max server memory is 2147483647
MB; that is the equivalent of nearly 2 PB. This is huge and hence will eat up
all of the available memory. Though, SQL Server changes its memory requirements
dynamically based on available system resources.
The maximum memory needs to be changed
based on what other instances are installed on that server for other
applications and the available RAM. Generally speaking, on a machine where there
are no other applications apart from SQL Server, 75%
of the maximum available RAM should be allocated for SQL Server. So if
you have a 16GB RAM machine, 75% of 16 = 12 GB should be allocated as the
maximum memory to SQL Server and the remaining will be used by operating systems
processes and other processes.
Minimum memory is also an important
setting. If the operating system needs to reclaim memory from SQL Server, it
will request that SQL Server release memory. SQL Server will release the memory
back to the Operating System until the amount of memory in use reaches the
minimum server setting that is 0 by default. So if in the case that there is a
huge file that needs to be copied then the operating system will keep claiming
memory until SQL Server is allowed to release memory. This makes SharePoint
performance slower. Hence, the
minimum memory setting also needs to be changed to at least 50% of the available
RAM. So if you have a 16GB RAM machine, 50% of 16 = 8 GB should be
allocated as the minimum memory to SQL Server.
The Min server memory
setting is used to guarantee a minimum amount of memory available to the SQL
Server Memory Manager for an instance of SQL Server. SQL Server will not
immediately allocate the amount of memory specified in min server memory on
startup, neither will it take up all the min server memory setting value. If SQL
Server is able to run on a lesser amount of memory then it will do so. But,
after memory usage has reached this value due to client load, SQL Server cannot
free memory unless the value of min server memory is reduced.
If the
setting for both min server memory and max server memory is the same, then once
the memory allocated to the SQL Server reaches that value, the Database Engine
stops dynamically freeing and acquiring memory for the buffer pool.
On a
SQL Server machine where multiple instances are loaded, for Max server memory
setting, establish maximum settings for each instance, being cognizant of the
fact that the total allowance is not more than the total physical memory of the
machine. You might want to provide each instance of memory proportional to its
expected workload or database size. This approach has the advantage that when
new processes or instances start up, free memory will be available to them
immediately. The drawback is that if you are not running all of the instances,
none of the running instances will be able to utilize the remaining free memory.
On a SQL Server machine where multiple instances are loaded, for Min
server memory setting, establish minimum settings for each instance, so that the
sum of these minimums is 1-2 GB less than the total physical memory on your
machine. Again, you may establish these minimums proportionately to the expected
load of that instance.
This approach has the advantage that if not all
instances are running at the same time then the ones that are running can use
the remaining free memory. This approach is also useful when there is another
memory-intensive process on the computer, since it would insure that SQL Server
would at least get a reasonable amount of memory. The drawback is that when a
new instance or any other process starts, it may take some time for the running
instances to release memory, especially if they must write modified pages back
to their databases to do so.
Server
Memory Server Configuration Options
In the next article we will see some
more SQL Server Settings relevant to SharePoint performance.
sp_configure ‘show advanced options‘, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ‘max degree of parallelism‘, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ‘max server memory‘, 12288; --12*1024
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ‘min server memory‘, 8192; --8*1024
GO
RECONFIGURE WITH OVERRIDE;
GO