SQL Server Health Check

SQL Server Max Memory Parameter

Updated
3 min read
Written by
Mark Varnas

Why should you care about the max memory setting in SQL Server?

SQL Server RAM max memory default setting is terrible.

The server must have the correct amount of free memory, or the OS may enter “high memory pressure mode.”

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

Operations expected to be fast and in-memory will frequently read and write to disk.

How do I check SQL Server max memory?

SQL Server max memory is set at the instance level.

You can check it using SQL Server Management Studio (SSMS):

  • Right-click on your SQL Server and click Properties.
  • Memory, and it’s Maximum server memory.

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

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

Memory Available (GB)SQL Server 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 does the SQL Server need 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, after that, we can have a ballpark estimate.

Normally when there is memory contention at OS level, we recommend adding more memory to your server, not reducing memory from SQL Server unless it is 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 Remote Desktop Protocol (RDP) to run effectively.

You can set the value using the SSMS GUI above or use the T-SQL below:

SP_CONFIGURE 'Max Server Memory'
	,XX GO

RECONFIGURE GO

Note: Replace [XX] with the desired value in megabytes (MB).

More information

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Leave a Comment

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials