Setting the Maximum Server Memory for SQL Server

To prevent Microsoft SQL Server from consuming too much memory, you can use the following formula to determine the recommended maximum server memory:

  • Reserve 4GB from the first 16GB of RAM and then 1GB from each additional 8GB of RAM for the operating system and other applications.
  • Configure the remaining memory as the maximum server memory allocated for the Microsoft SQL Server buffer pool.

For example, if the computer hosting the Microsoft SQL Server instance has 32GB of total physical memory, you would reserve 4GB (from first 16 GB) + 1GB (from next 8 GB) + 1 GB (from next 8 GB) for the operating system, then set the Maximum server memory for Microsoft SQL Server to 26GB (32GB – 4GB – 1GB – 1GB = 26).

Reference:

https://msdn.microsoft.com/en-us/library/ms178067(v=sql.105).aspx

To set the maximum server memory for SQL Server:

  1. Open the SQL Server Management Studio, enter the SQL Server properties:
  2. Set the maximum server memory (in MB).