Microsoft SQL Server – Limiting Memory Usage

To limit SQL servers’ memory usage SQL Server has a setting called “Maximum server memory (in MB)”. This setting is used to limit the amount of memory SQL server can use. By default, it can consume all memory and will release memory as other processes open on the system.

When multiple instances of SQL are running on the same system it is a good idea to set this value on each so one doesn’t consume all memory on its own.

I also typically set this setting on my development system as I don’t want all my memory consumed by SQL. I set mine to 1GB.

For a production environment this setting may be left alone (typically) as SQL Server is typically hosted on its own system and applications are on another.

Steps to set “Max server memory (MB)”:

1. Open SQL Server Management Studio
2. Login as a sysadmin
3. Right click the SQL Server and choose properties in the object explorer
4. On the server properties window choose “Memory” and set “Maximum server memory (in MB)”.

Additional information on this setting:

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16