Search FAQs

Video: How To Limit SQL Server Memory Usage?

Issue

SQL Server is using a large amount of RAM memory which is resulting in poor system performance.

Product Line

Power Monitoring Expert (PME)
Struxureware Power Monitoring (SPM)
ION Enterprise

Environment

SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

Cause

By default, SQL Server will utilize as much RAM memory as possible.

Resolution

It is possible to set limits on how much RAM memory SQL is allowed to request from the Operating System.

*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio.*

1. Start SQL Server Management Studio and log in to your SQL Server instance.

2. Right-click the SQL Server name and click Properties in the menu to open the Server Properties dialog.

3. Select Memory in the left pane and adjust the value in the Maximum server memory field.
Note the following recommendations for setting SQL server memory options on the server where you install a supported edition of SQL Server:

Where only SQL Server is running on the host server, set the SQL Server maximum memory to the system physical memory less at least 2GB for the Windows operating system. For example, if your server has 8GB of memory, set the SQL Server maximum memory to no more than 6GB. This leaves at least 2GB for the operating system.
Where the SQL Server is sharing the host server with other server processes, including Internet Information Services (IIS) and ION services, set the SQL Server maximum memory to no more than half the physical memory on the server. For example, if you server has 8GB of memory, set the SQL Server memory to no more than 4GB. This leaves at least 4GB for the operating system and all other server processes.

4. Click OK to apply the changes and close the dialog.

5. Close SQL Server Management Studio.