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 |
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 GONote: Replace [XX] with the desired value in megabytes (MB).
