SQL Service Consuming Too Much Memory

Over the last few months I’ve been working with a lot of VM’s running CRM for development. Most of these VM’s are a one box solution where a single VM is used to run both CRM and SQL. On a few of these boxes the SQL Server Service has ballooned to consume more than 50% of the available memory on the box. This has lead to the M becoming unresponsive and difficult to use.

I’ve started to routinely limit the amount of memory SQL is allowed to use, and this seems to make life run a little more smoothly on the development VM’s. The setting for this is with SQL itself and is updated by running a script against the master db of the SQL instance. Pasted in below is the script I use to set the maximum memory usage to be 1Gb.

Note that the ‘max server memory’ element is an advance option, so advanced options need to be enabled first before making the change.

 

USE [master]

GO

 

— Set the advanced options to On

sp_configure
‘show advanced options’, 1;

GO

 

— Call reconfigure to commit the change

RECONFIGURE;

GO

 

— Update the maximum memory (to 1Gb in this case)

sp_configure
‘max server memory’, 1024;

GO

 

— Call reconfigure with override to commit without needing to restart the service

RECONFIGURE
WITH
OVERRIDE;

GO

 

— View the updated values

EXEC
sp_configure
‘max server memory (MB)’

GO

 

— Turn Advance options off

sp_configure
‘show advanced options’, 0;

GO

 

— Call reconfigure to commit the change

RECONFIGURE;

GO

 

See the following links for further information:

SQL server memory options: http://msdn.microsoft.com/en-us/library/ms178067.aspx

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!