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 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.

Best practices indicate around 4-6 GB less than the amount of RAM installed.

To make this more robust, you can setup Perfmon monitoring that records min memory values throughout business hours.  Then you can take away more memory from Windows.

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

Finally, you can set the value using the SSMS Gui above or use the TSQL:

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

More information

Microsoft – Server Memory Configuration Options

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.