Memory Settings in sp_configure
Published: 3rd September 2013
Now this is a fun thing that I REALLY should have known, but have never really played with the therefore was mystified when it came up in conversation recently. I’ve asked a few other SQL guys and they were unaware of the true meaning of this setting either (which made me feel a little better). It’s not something I’ll be forgetting anytime soon though.

Basically we all know what “max server memory (MB)” is and hopefully everyone uses it, but what about the similar but different “min server memory (MB)” option?

Now I imagine there’s quite a lot of people who already know what this does, how to use it, and who set it accordingly, but I have to admit that I wasn’t one of those and neither were a good few guys I spoke to about this. However, now that I’m aware of its role, I’ll definitely be setting it on all servers in the future.

max server memory (MB)

As a recap for anyone who doesn’t know… this NEEDS to be set. Seriously. Go and run this code on all your servers now:

select name, value, minimum, maximum
from sys.configurations
where name = 'max server memory (MB)'


If your output has a value of 2147483647 then you NEED to change this.

SQL Server is a greedy piece of software and it will take your server memory and it will not give it back without a fight. Therefore if you have this option set to the max value (which it is by default) then what this means is that SQL Server will start to pull data into memory as queries run and data is required and will keep on doing so until it has utilised every last scrap of RAM in your server.

This is a bad thing for Windows Server as it will then be in a fight for resources every time it needs to do anything. As a result your server can become sluggish and slow to respond. Also if you have any other applications running on your server then they will also have their RAM squeezed by SQL Server. Not good.

Therefore please set this to something sensible. For example, in a server with 64GB RAM you might want to set the limit to 56000 which gives SQL Server ample RAM whilst also leaving a decent portion for the OS to utilise as it pleases. There is no set in stone figure, but anything sensible will work nicely.

To set your value to 56000 you would run the following code:

sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'max server memory (MB)', 56000
go
reconfigure
go


min server memory (MB)

Now, here’s the fun one… I thought this was simply the figure below which you didn’t want SQL running. Ie. If you think it can’t cope with less than 5GB RAM then you would set this to 5000 and bingo… on startup SQL will grab 5GB RAM and that’s your minimum. Therefore I always left this at the default value of 1GB. I also believed that once SQL Server had RAM it would never let it go again (unless you lower the max memory sp_configure setting).

As I found out, that’s total rubbish.

The actual purpose of this setting is the limit to which SQL Server will yield memory back to the OS if Windows has memory pressure and starts to request some back.

Therefore, when you start SQL Server it will simply take bare minimum RAM from the OS. Once queries start to run SQL will increase its RAM holding up to the max limit discussed above.

However, what could now happen is that you run a process on the OS which is very memory hungry. It will start to request more and more RAM. When this happens the OS will go to SQL Server and request RAM back. SQL will yield this memory back to the OS (at a cost to performance etc as it has to throw data pages out of memory and can obviously then not hold as much going forward).

However, SQL Server will only yield memory until it hits the min server memory (MB) limit, at which point it will refuse to hand any more back.

Therefore, knowing this, I now set my min server memory (MB) setting on each server so that if anyone does run anything on the servers without my knowledge, I know it can only have limited impact on my SQL Server instances.
Comments:
NB: Comments will only appear once they have been moderated.