SQL Server Settings: Memory Configuration
SQL Server is designed to efficiently manage memory on its own. It requests memory when it’s needed and releases memory back to the system when it’s not. However, the default settings may not be ideal for your situation. An experienced database administrator will usually fine-tune these settings to optimize performance and server stability.
Minimum server memory
The minimum server memory setting in SQL Server determines the smallest amount of RAM that SQL Server will grab. SQL Server will always have at least this much memory available to use, even when the system is under heavy load. The default setting for minimum server memory is usually very low, and SQL Server dynamically adjusts its memory usage based on its workload.
Maximum server memory
The maximum server memory setting in SQL Server limits how much of your computer’s memory SQL Server can access. By default, SQL Server grabs as much memory as possible, which can lead to problems. If SQL Server takes up all the available RAM, it slows down other programs running on the same server (such as SSIS, SSRS, or SSAS), making everything feel sluggish.
Configuring minimum server memory
Configuring minimum server memory is usually easy because, in most cases, it’s best to leave it set to the default. Sometimes people will set the minimum and maximum server memory settings to the same value to “lock in” the memory. I’ve seen this in virtualized environments as an attempt to prevent the hypervisor from “ballooning” and taking away the resources that are supposed to be allocated to SQL Server. The risk with this type of configuration is that during a restart, SQL Server will not come online until it has claimed all of the RAM listed in the minimum server memory setting.
You can configure your server’s minimum server memory setting in SQL Server Management Studio. Find the server properties section, then look for the minimum server memory option.
You can also make this change by running the following script:
sp_configure 'min server memory', <desired memory amount (mb)>;
GOConfiguring maximum server memory
The default maximum server memory setting can sometimes cause performance issues. By limiting the RAM SQL Server uses, you can ensure there’s enough memory for the operating system and other applications to run smoothly.
Honing in on the exact number can be tricky, as it depends greatly on the load your SQL Server is under, as well as any other applications that may be running on it. However, configuring the maximum server memory appropriately ensures SQL Server has enough memory to perform efficiently without starving the operating system or other applications of the resources they need. This balance helps maintain system stability and performance.
For example, if you have SSAS installed on the SQL Server, by default it will claim 20% of the RAM on the server, leaving only 80% available for SQL. I’ve seen scenarios where SQL Server crashed with an “insufficient system memory” error every time it tried to go above 80% RAM usage because SSAS refused to release its 20%.
To configure maximum server memory, start by following the same steps as you did above. You can configure your server’s minimum server memory setting in SQL Server Management Studio. Find the server properties section, then look for the maximum server memory option.
You can also make this change by running the following script:
sp_configure 'max server memory', <desired memory amount (mb)>;
GOFinal thoughts and next steps
Although SQL Server will manage its memory dynamically right out of the box, its default settings may not suit every scenario. Fine-tuning minimum and maximum memory settings means efficient server performance without depriving the operating system and other applications of resources. Careful memory configuration can help you achieve an optimized and balanced server environment.
Further reading
- How Much Memory is “Normal” for SQL Servers? – Brent Ozar
- Memory management architecture guide – Microsoft Learn
Previously in this series
Work with us
Want help optimizing your SQL Servers? Schedule a free call with us to learn more and get started!