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.”
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) |
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
How much memory the SQL Server needs to handle your workload?
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:
- Change [XX] to the value in (MB).
SP_CONFIGURE 'Max Server Memory',XX
GORECONFIGUREGO
