Mini Post: Change SQL Server Instance Min/Max Memory Settings

Hey everyone, just a quick note on how to change the min/max memory settings on your SQL Server instance.

By design, your SQL Server service will attempt to use as much memory as it sees fit in order to avoid going back to the disk to store the data it wants to. It will instead attempt to store as much data as possible in memory.
Not setting a max limit on the amount of memory SQL Server should use can greatly negatively impact the service's performance as well as the server's performance and you may start getting memory errors such as:

Error 802
There is insufficient memory available in the buffer pool.


To avoid this, you can set memory usage as follows (please note that you may need to restart the service for the changes to take effect):

• Log onto your intance via SQL Server Management Studio.
• Right click your instance, select Properties.
• In the Properties window, select the Memory tab on the left.
• Set your Min/Max settings to suit (Fig. 1 shows the default settings).

Fig. 1: Min/Max Memory default settings.

It is usually recommended that your production instance(s) runs on a machine dedicated to SQL Server and max memory settings should be set to the server's total amount of memory minus the amount of memory needed to run the server's OS and other SQL Instances.

Further reading here.
And here.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)