What you need to know about memory limits on SQL Server

Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation:

Reminder: Max Server Memory is *not* just for the buffer pool. The RAM limit Standard Edition can use *is* just for the buffer pool.

Joey D’Antoni was quick to remind me that this only applies to SQL Server 2012 and higher, which I gratefully acknowledge. For the sake of this post, I am talking specifically about SQL Server 2012 and higher.

Let’s break this down.

On all editions of SQL Server (Enterprise, Standard, Web, Express), there is a configuration setting called Max Server Memory. This value should not be left at the default 2.1 petabytes, because at that value SQL Server will use all the memory you allow it (plus a little bit more if it needs to), and once it has it, it doesn’t release it back to the operating system without a fight.

What does Max Server memory control, though? According to the documentation, it’s the buffer pool, compilation memory, all the caches including the plan cache, and a bunch of other stuff. Here’s a quote:

Max Server Memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.

The main takeaway here is that Max Server Memory is not limited to the buffer pool. There’s a lot of other stuff going on too.

Now let’s consider the RAM limit of all non-Enterprise editions of SQL Server, as specified in the license agreement. At the time of this writing, using Standard Edition as an example, the license has a RAM limit of 128GB, which is specifically the maximum memory available for the buffer pool only. (This clarification in the documentation occurred after the release of SQL Server 2016 Service Pack 1 last year, but the license limit has referred to the buffer pool since at least SQL Server 2012.)

This means that in a server with Standard Edition and a lot of RAM, you could set the Max Server Memory configuration setting higher than 128GB comfortably, and SQL Server will use more than 128GB perfectly legally, because there’s a lot more to Max Server Memory than just the buffer pool.

So when I read in forums and elsewhere that the license limit of 128GB for Standard Edition is the highest you can set Max Server Memory, this is demonstrably wrong.

To make this more interesting, SQL Server 2016 and higher supports ColumnStore and In-Memory OLTP in non-Enterprise editions anyway, which means that you can easily consume all the physical RAM on a server, even if your buffer pool is using the legal limit.

Tell me your licensing story on Twitter at @bornsql.

Max Server Memory and SQL Server 2016 Service Pack 1

Everything changed for SQL Server Standard Edition on 16 November 2016, and how memory limits work.

On that day, a slew of Enterprise Edition features made their way into editions across the board, including Express Edition and LocalDB.

The memory limit of 128GB RAM applies only to the buffer pool (the 8KB data pages that are read from disk into memory — in other words, the database itself).

For servers containing more than 128GB of physical RAM, and running SQL Server 2016 with Service Pack 1 or higher, we now have options.

The max server memory setting always did only refer to the buffer pool, but for many reasons there was misunderstanding from a lot of people that it included other caches as well.

Because ColumnStore and In-Memory OLTP have their own cache limits over and above the 128GB buffer pool limit, the guidance around assigning max server memory is no longer simple.

ColumnStore now gets an extra 32GB of RAM per instance, while In-Memory OLTP gets an extra 32GB of RAM per database.

With that in mind, you are still welcome to use the Max Server Memory Matrix and associated calculator script for lower versions of SQL Server (up to and including 2014), but I will not be maintaining it further, unless someone finds a bug.

How much should I assign to max server memory? It depends.

It would be very easy to spec a server with 256GB RAM, install a single instance of SQL Server 2016 Standard Edition (with Service Pack 1, of course), have 128GB for the buffer pool, 32GB for the ColumnStore cache, three databases with 32GB of RAM each for In-Memory OLTP, and still run out of memory.

This is a brave new world of memory management. Tread carefully.

If you’d like to share your thoughts, find me on Twitter at @bornsql.

Updated Max Server Memory Script

Earlier this year I released a free T-SQL script that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance.

After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team’s GitHub repository and discovered something similar, but not quite the same, in the Maintenance Solution folder.

I have taken the best ideas from their Database Server Options script and merged them into my Max Server Memory Calculator script.

New Features

The SQL Server thread stack is now taken into account. This value depends on the CPU architecture (32-bit, or x64 / IA64) and the maximum worker threads configured for the SQL Server instance.

On my 64-bit laptop with 16GB RAM, the new recommended amount for Max Server Memory has dropped from 11,264 MB to 10,112 MB (1,125 MB of RAM is now reserved for the thread stack).

Improvements

By default, the generated script will enable show advanced options before trying to set the max server memory (MB) value.

The @ProductVersion parameter uses a new method to calculate the major SQL Server version.  Previously it was a hack based on the string returned by the @@VERSION function, but now it uses the @@MICROSOFTVERSION function.

This code is also from the Tiger team’s repository, and I’m sharing it here because I think it’s pretty clever how it works.

-- Get SQL Server Major Version
SELECT CONVERT(INT, (@@MICROSOFTVERSION / 0x1000000) & 0xFF);

I have also added a note on the Max Server Memory Matrix page to note that the script now accounts for the thread stack.

I hope you enjoy this new version of the script. If you have any comments or suggestions, please contact me on Twitter at @bornsql .

Max Server Memory Revisited

Some time ago, I added a free resource to this site to help DBAs know how much max server memory should be assigned to SQL Server, based on an algorithm by Jonathan Kehayias.

On 19 August 2016, I went one step further and wrote a free script to perform the calculation for your own instance, and even provided some T-SQL code to make the change as well. The only thing you have to do is run it.

Download the script from GitHub

You can visit the download page right here.

Run the script on your SQL Server instance

It’s perfectly safe to do so, as all it does is run a series of SELECT statements. Because this script intends to help you modify a server-level setting, you must have the necessary sysadmin permissions.

NOTE: You may have to enable advanced options first. This is a requirement before the Max Server Memory setting can be changed.

Here’s the script running on SQL Server 2008 R2 (click to enlarge):

msm1

And here’s the same script running on SQL Server 2016 (click to enlarge):

msm2

Copy the configuration change script from the [Script] column

-- Show Advanced Options might
-- need to be enabled first.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
-- The output from this new script
EXEC sp_configure 'max server memory (MB)', 11264;
RECONFIGURE WITH OVERRIDE;
GO
-- Turn off Show Advanced as best practice
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

Make that change!

If you did it right, you’ll see something like this in the Messages pane.

Configuration option 'max server memory (MB)' changed from 2147483647 to 11264. Run the RECONFIGURE statement to install.

This should make your life easier when setting up a new server or reconfiguring a virtual machine when you need to manage the RAM allocation.

If you have any questions or comments, please find me on Twitter, at @bornsql .

Max Server Memory Matrix

When we perform diagnostics on an existing SQL Server instance, or if we are setting up a new machine, it’s very important to make sure that we change the Max Server Memory setting to an appropriate level.

If we don’t, then there’s a good chance (especially on Enterprise Edition) that SQL Server will use all the available memory on the server. Unfortunately, this means the operating system can be starved of RAM, which creates a performance bottleneck.

Additionally, other components (including other applications, services, and hardware on the machine) may have specific memory requirements, which need to be taken into account.

To get a baseline, I use a formula developed by Jonathan Kehayias (blog | twitter) which he wrote in his book, Troubleshooting SQL Server: A Guide for the Accidental DBA, and repeated in his blog post, “How much memory does my SQL Server actually need?”.

After many months of confusing myself with mental arithmetic, I decided to create a straightforward list based on Jonathan’s algorithm, in 4 GB increments, for my own reference. I would like to share this with you today.

You can bask in the glory that is my Max Server Memory Matrix at https://bornsql.ca/memory/.

Next time you (or I) need to check if the Max Server Memory setting is too high, simply open that link and check the matrix. Over time I will make it look prettier, but for now it is already helping one of my clients.

Let me know what you think on Twitter, with @bornsql.