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 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:
- Change [XX] to the value in (MB).
SP_CONFIGURE 'Max Server Memory',XX GO RECONFIGURE GO