SQL Server max memory parameter

Why should you care about Max memory setting 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.

By the way, this check is a part of our SQL Server Health Check service.

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

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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