Avoid running out of memory on multiple SQL instances

A while ago one of SQL Servers performed poorly and while I was investigating, I found out ALL server memory had been consumed:


 There are 5 instances installed on this server and they used all 32G RAM:


The issue turned out to be these instances are not capped and therefore they used all memory that were available.

BOL suggests to use max server memory to control memory usage. Below is the script that was used to setup 1 instance:

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘max server memory’, 5120;

GO

RECONFIGURE;

GO

sp_configure ‘max server memory’

GO

sp_configure ‘min server memory’

GO



Also, it’s a good practice to setup minimum memory to so database engine will needs less effort to apply more memory. Again, we will use 80% of maximum memory:

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘min server memory’, 4096;

GO

RECONFIGURE;

GO

In the same time, this configuration can be viewed through performance counter using Target Server Memory and Granted Server Memory:


 

It’s important to setup max and min memory for individual instances on same server and also ensure OS has enough memory to run with it. Planing ahead will go a long way.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s