Limit SQL Server memory usage on your workstation, laptop or VM

Here’s a neat tip I learnt over the weekend.

All SQL Server instances are by default set up to use all the memory available on your workstation.

This is ideal when you have SQL Server running on it’s own dedicated server hosting, not so ideal when you have SQL Server installed on your laptop, workstation or even on a SharePoint VM.

Here’s what MSDN says

if SQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL Server. In these cases, you can use the min server memory and max server memory options to control how much memory SQL Server can use.

In the Server Memory Options page they go on to say:

When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory
  • Use max server memory to control memory usage.
  • Use min server memory to control memory usage.
  • Do nothing (not recommended).

Which brings us to how we can set the maximum limit. Quite easy. Just connect to each SQL Server instance and set the maximum memory to a more palatable value.

Here’s a visual walk through to limit the maximum memory usage to 512MB for your SharePoint 2010 instance (if you installed it on Windows 7).

  1. Start SQL Server Management Studio (or SSMS Express) and connect to your SQL Server instance (SharePoint in this case): localhost\SharePoint

  1. Right-click on the instance node and select Properties.

  1. Click on the Memory node you’ll notice that the Maximum Server Memory is set to 2,147,483,647MB change it to a lower limit like 256 or 512MB. Click OK and your all set.

If you prefer SQL the same can be done with the following commands.


Enable advanced options:

USE master

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE


Set the maximum amount of memory to 512 MB:

USE master

EXEC sp_configure 'max server memory (MB)', 512

RECONFIGURE WITH OVERRIDE


Display the newly set configuration:

USE master

EXEC sp_configure 'max server memory (MB)'


Set ‘show advanced options’ back to default:

USE master

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

Written on May 19, 2010