By default, SQL Server’s max memory is 2147483647. With this default setting, SQL Server will “commit” as much memory as possible until there’s none left for the operating system to run efficiently.
If the operating system has no memory available, it will start using the page file instead of RAM. Using the page file in place of memory will result in poor system performance.
SQL Server really does need as much memory as possible. However, that doesn’t mean you should leave SQL Server’s max memory set to “unlimited.” If you starve the OS for memory, then Windows itself may not get the memory it needs.
How To Fix The Problem
Set the max memory that SQL Server can use in your environment. To change your SQL Server Memory:
1. Log into SSMS
2. Right click on the instance name, navigate to the bottom of the menu, and click on “Properties”
Change the max memory settings to allow room for efficient operating system performance, so it doesn’t use the page file in place of RAM. For recommendations on SQL Server Max Memory settings, check out Glenn Berry’s Suggested Max Memory Settings.
4. Click the “Save” button and restart SQL Services for changes to take effect.
Until next time, happy SQL Server performance tuning!
