Setting SQL Server Max Memory Dangerously Low

Hopefully you know the relevance and importance of setting a correct value for max memory on your SQL Server. By default it will be the value 2147483647 which is not a random number but the 8th Mersenne prime! In a computing sense it is the maximum positive value for a 32-bit signed binary integer and that is big.

Anyways, getting back to the point of this post, I personally believe you should be changing this default value. But be sensible, you do want to see messages like “There is insufficient system memory in resource pool ‘internal’ to run this query”.
Out the box SQL Server will be like this.

maxmem

Or you could use TSQL to check the details.

SELECT name, value, value_in_use, [description]
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name

tsqlmem

Like I said before, change it but with some common sense in mind, if you set it too low this could happen.

oom

Naturally you might try and restart SQL Server and then make changes to max server memory setting right?

It might not work.

ssmserror

The error log will show a stack dump too.

stackdump

How much is enough? I suggest reading this blog post: https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

By the way I found a cool behaviour which I can’t really show via screen shots. Please see the quick video. Basically what happens here is I logon to the server via management studio and change max memory to a really silly setting like 50MB and look what happens!

Management studio will override your silly setting with 128MB thus giving you a little more room for manoeuvring. Did you know that? I didn’t!

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