Best practice for Maximum Server Memory Value in SQL Server

As we have covered recently why MAXDOP value should not be left to default and must be changed after installation of SQL Server(Why MAXDOP shouldn’t be left to default(0) and Must Be Changed). Similarly, when Maximum Server Value should be looked after doing the fresh installation.

We can check the value of this by right click on instance of SQL Server and going into Memory tab as shown below:

The default value is set as “2147483647 MB”, this comes somewhere around 2047TB. You may get converted value by dividing by 1024 for converting to GB and further by 1024 to convert to TB.

The best practice is to change this value to 90% of the memory allocated to server and remaining 10% should be left for operating system. Changing this value takes effect immediately(depending on open transaction) and dynamically – meaning service reboot is not required for this to take effect.

Please note that, the unit in this tab for Minimum and Maximum memory is in Megabytes and should be dealt with caution because server memory is often represented in Gigabytes.

Now, the question is why should we change it and what if we leave it as it is to its default value – If we do so, SQL server doesn’t know where to stop and it will consume all memory as much is allocated to server leaving nothing for operating system. And if the operating system has no memory left to operate, it would start using page files, which would result in deteriorating performance dramatically.

Also note that, we should not keep the same value for Minimum and Maximum server memory, better to leave minimum to its default value and shouldn’t be changed. As per below guideline from Bob Dorr(From Micosoft):

Use the min server memory setting with care.   This is a floor to SQL Server.   Once committed memory to reach the min server memory setting SQL Server won’t release memory below the mark.   If you set max server memory to 59GB and min server memory to 56GB, but the server needs to back SQL Server down to 53GB SQL Server won’t drop below 56GB.    When you combine this setting with locked pages in memory the memory can’t be paged.  This can lead to unwanted performance behaviors and allocation failures.

We have seen above how to change the value of maximum memory value through user interface, same can also be changed using SQL command as below:

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC sys.sp_configure ‘max server memory (MB)’, ‘2048’;
RECONFIGURE;
EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE;

Please leave your comment if you liked this post or have any feedback.