Releasing the Memory Consumed by stubborn SQL Server at Runtime

Introduction

Unless you constrain it, SQL will gobble up all your memory, leaving your Windows application or service with no room to maneuver. SQL best practices suggest that any SQL Server installation be a dedicated server. But this is not always possible. Consultants and marketers working on a portable infrastructure like a laptop essentially must run the entire application, including its database, on that single machine.

While one can train an advanced user to expect and manage this, an end user typically doesn't want or need to be concerned with this technical aspect. In most cases, users will blame the application interacting with SQL, instead of the real culprit, SQL Server.

Configuring SQL limits

You can configure a maximum and minimum memory limit within SQL management studio, for the specific server you are connected to.

This can prevent SQL from taking all available memory, however if you limit the size SQL can cache, you essentially slow it down from its optimal performance, so you basically trade memory for performance. You need to configure a middle ground and therefore can never have the best of both worlds, or can you?

The solution: Forcing SQL out at runtime

SQL supposedly provides ways of cleaning out buffers and clearing cached items. Some examples are FREESYSTEMCACHE, FREEPROCCACHE and DROPCLEANBUFFERS, however none of these appear to result in an effective release of substantial memory and cannot restore SQL to the same status as the traditional system restart.

There is a different way though. SQL allows you to modify these limits at runtime using:

sp_configure 'max server memory (MB)'

By reducing this value at runtime, SQL will reduce its memory footprint to match the new limitation, within seconds. You can then reset this value to the original limit, allowing SQL to re-acquire memory when it needs to. In my experience, a delay of 5 seconds between reducing the value and re-setting it to its original value seems to work on SQL 2008R2, but for 2012 and 2014, 10 seconds is needed for SQL to respond to the reduced limit.

The code you will need to change the value is below. First you enable the advanced options, then you make the change, then you turn off the advanced options again:

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'max server memory (MB)', @val

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

A few things to remember

• This does not work in versions prior to SQL 2008
• On SQL 2008R2, you can safely reduce the value to 1GB and it should be fine, but on SQL 2014 SQL tends to fail if you go below 2GB.