How to Limit the Amount of Memory Used by SQL

Expand / Collapse
 

How to Limit the Amount of Memory Used by SQL


Article ID: 52267 - Last Review: April 30, 2015

INFORMATION

By default, SQL will consume as much memory as is needed and available. Occasionally, SQL will consume all of the available memory on the CCM Server, resulting in poor CCM Server performance.

In these situations, it may be beneficial to limit the amount of memory SQL is allowed to consume. This can be done within SQL itself.


INSTRUCTIONS

1. Open SQL Management Studio.

SQL Management Studio is an optional, but recommended, component of SQL. It is needed for this operation, and for many troubleshooting functions. If it is absent, you will can download the version of SQL Management Studio which is compatible with your version of SQL from Microsoft. It is a free download.

For new installations of SQL Express, we recommend that you always use the install package marked “Full” or “WT” [With Tools] in order to ensure that you get SQL Management Studio.


2.
In Management Studio, in the top-left corner, click on the SQL Instance Name and select Properties (as shown):



3. In the Server Properties window, on the left, click “Memory”.

 


4.  Set the Maximum server memory (in MB). 

2048 is generally a good value for most sites.   For more information about how SQL uses memory, please see this Microsoft TechNet article: https://support.microsoft.com/en-us/kb/321363


5. Click OK.


EXPECTED OUTCOME

The amount of memory being consumed by the SQLServer.exe process will now gradually decrease.  No service restarts are necessary.



APPLIES TO

SQL 2008 R2, 2012, and 2014


Keywords

SQL, memory, RAM, limit, consumes, excessive



Rate this Article:
     

Add Your Comments


Name: *
Email Address:
Web Address:
Verification Code:
*
 

Details
Last Modified:Thursday, April 30, 2015
Last Modified By: cdoyle
Type: HOWTO
Article not rated yet.
Article has been viewed 5,528 times.
Options