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
  • Nice post. Thanks Merill.

  • Frank

    Good point. Thx

  • Freesky

    It is a nice post. I tried with local administrator, who set up the SharePoint 2010, to change it with an error-message “User does not have permission to perform this action.” Could you please help me

  • You should instead try using the account that you used for the SQL Server installation.

  • Philip Elder

    I think that there needs to be the following command run after the “reconfigure with overried” step for each command line group:

    >go [Enter]

  • Super comprehensive guide thank you !
    I am getting the error “User does have permission to perform this action”.
    But I am the System Manager!
    Have you further tip. Thank you very much.

  • srisubathra

    Hi i have one doubt , this is u have connected the instance in server , but how do i connect in local machine , using sql authentication yaar ,, pl let me know

  • Chronicfathead

    For all the people reading this, don’t forget to start the SQL Server Management Studio, by right clicking and running as Admin, otherwise you will have issues connecting etc.