Limit the Amount of Memory used by an SQL Instance

By Default, an instance of SQL will attempt to use every free MB of memory it can get its hands on.  This is by design, the more database records available in memory, the lower the disk I/O activity.  However there are times when you may want to limit the amount of memory utilised by an SQL Instance.  For example you may have an SQL Clustered environment running AlwaysOn Availability which is responsible for hosting multiple SQL Instances.  In this case you may what to limit the memory utilisation of each instance.  Another scenarios may exist where you are monitoring your servers available memory using a monitoring product such as SCOM and configure the monitoring product to trigger an alert when 90% memory utilisation on a server is hit.  As SQL always utilises all available memory, this will cause many false alerts.

How do we do this?

First you need to connect to the SQL Instance you wish to configure using SQL Management Console, in my example it is SQL 2012.

Next right click the instance (in my example my SQL Instance is called SCCM) and click Properties.

On the left of the properties screen click Memory.  There will be a value by default under Maximum server memory (in MB) set to 2147483647 is equivalent to 2048TB of memory (no server has this!).

Set it to something more practical like 1024MB.  Please note if you  have a busy SQL application you will need to assign an appropriate amount of memory for the given application.