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

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

SharePoint 2010 Service Manager

With the final release of SharePoint 2010, I finally had time to brush-up and release the Service Manager that I wrote sometime back when the 2010 betas was released.

This utility is basically akin to the SQL Server Service Manager of yore.

If you have SharePoint 2010 installed on your local Windows 7 workstation then you will definitely come across instances where your workstation suddenly freezes up and everything starts moving in slow motion. The most likely culprit is usually one of the SharePoint services. At other times the SharePoint services simply eat away at your RAM.

That’s where the SharePoint 2010 Service Manager comes into play. It lets you start and stop all the SharePoint Services running on your workstation with a single-click.

This release handles both the full version of SQL Server as well as SQL Express Edition (the SharePoint instance). There is also an option to permanently disable the SharePoint services from starting up when Windows starts up, hopefully leading to faster boot times.

Here are a couple of screenshots. Get the setup file from CodePlex at http://sharepointserviceman.codeplex.com/.

I’d like to thank my colleagues at UniqueWorld including Neil, Rehman and Dougie who tested the first version and gave valuable feedback.

Please do report any issues you find to merill at merill.net

SharePoint 2010 Service Manager