SQL Server – Max Server Memory


There have been a lot of performance tuning and debugging lately on SQL Server, and one recurrent factor is the lack of configuration regarding the SQL server setting ‘Max Server Memory’.  This setting tells SQL Server how much memory it can use. The rest of the memory is reserved for operating system and other applications.

There is no perfect “rule of thumb” for this value, but a reasonable value can be set using the calculation for how much OS memory that should be allocated:

  1. Reserve 2GB for the OS
  2. Reserve 1GB for each 4GB of RAM installed  from 4-16GB
  3. Reserve 1 GB for every 8GB installed above 16GB RAM

Based on this, the following numbers can be used:

GB MB OS SQL %
4 4 096 2048 2048 50 %
8 8 192 3072 5120 63 %
12 12 288 4096 8192 67 %
16 16 384 5120 11264 69 %
24 24 576 6144 18432 75 %
32 32 768 7168 25600 78 %
40 40 960 8192 32768 80 %
48 49 152 9216 39936 81 %
56 57 344 10240 47104 82 %
64 65 536 11264 54272 83 %
72 73 728 12288 61440 83 %
80 81 920 13312 68608 84 %
88 90 112 14336 75776 84 %
96 98 304 15360 82944 84 %
104 106 496 16384 90112 85 %
112 114 688 17408 97280 85 %
120 122 880 18432 104448 85 %
128 131 072 19456 111616 85 %

A simpler “rule of thumb” is to set “Max Server Memory” to a number between 70-80% of total server memory. As you can see from the table, the 80% kicks in from 32GB total memory and up.  This is not an exact science, and the main rule should be not to give SQL Server all available memory on the server, because SQL Server is greedy 🙂

Advertisements

2 thoughts on “SQL Server – Max Server Memory

  1. Pingback: Setting “SQL Max Memory” with Powershell | Sveroa's Developer Blog

  2. Pingback: Powershell – CalculateSQLMaximumMemory v2 | Sveroa's Developer Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s