SQL Server max memory parameter

Category: Performance

Item:  Default max memory setting.

Why should you care about max memory in SQL Server?

SQL RAM max memory default setting is terrible.

The server has to have a correct amount of free memory, or the OS may go into “high memory pressure mode”.

This way, Windows will force other processes to give up memory by paging at unpredictable times.

Operations that should be fast and in memory will read and write to disk regularly.

How do I check SQL Server max memory?

SQL Max Server Memory is set at the instance level. You can check it using SSMS.

  • Right-click on your SQL Server and click Properties.
  • Memory, and it’s “Maximum server memory.”
Figure 1 – SQL Server max memory GUI.

By default, SQL Server maximum server memory is set to 214,7483,647 MB. It’s a lot more than you have.

How much memory from total memory available should be reserved for the SQL Server Instance? 

Memory Available (GB) SQL Min  Memory (MB) SQL Max  Memory (MB)
                                              4                                       1,024                                       2,048
                                              8                                       4,096                                       5,120
                                            16                                       8,196                                     12,288
                                            32                                    16,384                                     26,624
                                            64                                    32,768                                     55,296
                                            96                                    49,152                                     81,920
                                          128                                    65,536                                   112,640
(Considering that the server has only one instance and is dedicated to SQL Server).

How much memory the SQL Server needs to handle your workload?

Well, this is a tough question, and there is no rule here.
This will depend on many variables, and the best way is to monitor the memory usage from different perspectives, and after that, we can have a ball-park estimate.
Normally when there is memory contention at OS level, we recommend adding more memory to your server, not reducing memory from SQL unless it is absolutely misconfigured.

And remember that it’s the peak periods that matter the most.

How do I fix it?

Set a value that leaves enough memory for the Operating System itself and tools commonly used, such as  SQL Management Studio (SSMS) and RDP (Remote desktop) to run effectively.

You can set the value using the SSMS Gui above or use the TSQL:

  1.  Change [XX] to the value in (MB).
  1. SP_CONFIGURE 'Max Server Memory',XX 
  2. GO
  4. GO

More information

Microsoft – Server Memory Configuration Options



I am an Enterprise SQL Server Database Administrator with 10+ years of experience working with complex transactional environments.

Leave a Reply

Your email address will not be published. Required fields are marked *

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: