SQL Server Max Memory Best Practices
In this Straight Up SQL Server Tips series, we’re going back to basics. Many of these are findings in our SQL Server Health Assessments or things I bump into on the forums and Q&A sites when I’m out answering questions. Today we’re talking a bit about the SQL Server max memory configuration option.
SQL Server Max Memory: A Primer
This setting in SQL Server configuration options (I blogged about sp_configure a few years ago) is what it sounds like – the max memory SQL Server can consume. Now the one problem with talking about this setting is there are probably readers of this blog here in 2017 still using SQL Server 2005, 2008, 2012, 2014 and 2016. The setting works a slight bit differently in the earlier versions here. It used to be that this only affected the buffer pool – that is to say, the memory allocated by SQL Server to keep data pages in memory.
(what? data pages in memory? Ok so this isn’t the textbook answer but a quick explanation to keep it in line with the series: SQL Server has data. We agree there. That data lives on disk – in the data file. That data in the data file is organized -ultimately- in pages – 8KB blocks of data and extents – blocks of 8 pages. Well anytime you or I want to interact with this data it has to get from disk (slow, even with SSDs still slower than RAM) to memory (faster). SQL has to put that data into memory first. And once it puts that data in memory, it wants to try and keep those pages in memory. In fact, an important memory perfmon counter (for another post) is called “page life expectancy” – how long a page lives in memory. So that’s the buffer pool. The data cache. This is where most of your memory consumed in SQL Server goes)
So in SQL Server 2005 and 2008 Max Server Memory really, for the most part, was just this memory. Memory for things like CLR (yes it needs its own memory), linked servers, connections, the lock managers, etc were typically managed outside of this allocation pool. Starting in SQL Server 2012 this changed – the max memory controls more memory allocation areas. We could very quickly complicate this post by getting into what is and isn’t included, instead, I’ll link to this post from Microsoft describing the change that started in SQL Server 2012. You can click here and see some more differences and read about single page allocations vs multi page allocations and what Max Server Memory controls specifically.
Checking Max Server Memory
For this post, and really for your environment we can keep the advice here a bit simpler. First check and see what your max memory is.Through SQL Server Management Studio you would right click on your instance in object explorer and look at the properties tab. Through a SQL Query you would look at the sys.configurations table:
Through SQL Server Management Studio you would right click on your instance in object explorer and look at the properties tab. Through a SQL Query you would look at the sys.configurations table:
SELECT * FROM sys.configurations
WHERE name = 'Max Server Memory (MB)'
The results of either looking in the GUI or this script should tell you if you are in one of a few categories:
- Best Practice Environment – If the value is calculated to take a few variables described below into play it’s probably set well. Basically some number other than the default and that allows for some RAM for other instances, the OS or other applications. You want to leave some memory but not too little or too much.
- Sort of in the Middle – So the max is set, but maybe way too much memory is left over and when you look at perfmon or even task manager performance tab – you see that memory is never used but you see signs of memory pressure in SQL. Maybe it’s set away from the default but only 2% is left over which means there is potential for memory pressure on the server.
- Worst Practice Environment – One could argue this is less of a concern in SQL Server 2012 and higher – but it is still a setting you should pay attention to. If you see a really high number – 2PB – (2147483647 MB) – then you can know you are running at the default setting and no one has changed this setting ever, that’s probably not a great place to be.
Fixing Max Server Memory
So if you see that you aren’t in the best practices setup, you should analyze your situation. What else is running? Do you need to leave memory for other applications? Do you have multiple instances to leave memory for? Figure this all out and try and leave 10-20% of your memory available for the OS and occasional other use situation and set your max memory appropriately. I always like to keep the number evenly divisible by 1024 and keep the NUMA nodes in mind. I haven’t tested not being so precise with the settings, so your mileage may vary with that approach but it’s always worked out for me.
So for example, if a server was running SQL Server only, one instance only and it had 128GB of RAM with two NUMA nodes – I’d want to leave somewhere between around 10% free at least.
A more accurate calculation as memory increases could be something around 1-2GB for the OS, plus 1GB for every 4GB up to 16GB, then 1GB or so for every 8. For 128GB that would end up being 2 (base) + 4 (1 for every four up to 16) + 14 (1 for every 8 between 16 and 128) or 20GB. 10% would be 12% free, 20% would be 24% free. So you can use a calculation like that but I find that 10-20% range works then watch and tweak if and as needed with data from your environment. So if we go with that 20GB free that means we’d want to leave 108GB for SQL Server max. That works. It’s divisible by the NUMA nodes. So I’d want to set that number to 108GB, but the setting is in MB. So I multiple 108 * 1,024 (MB per GB) and get 110,592MB. I could change this in the GUI or I could use SP_Configure:
SP_CONFIGURE 'Max Server Memory' , 110592
(note if you have never looked at the “advanced options” you’ll get an error saying this setting doesn’t exist or it may be an advanced option. In that case, you would need to change the configuration option “Show Advanced Options” to true or enabled… SP_Configure ‘Advanced Options’, 1 then run RECONFIGURE.)
Finally, this is an online setting. You can change it without restarting SQL Server, but please note when you run it, if you are lowering it, you could and will potentially flush some data from the caches for data cache and or procedure cache. This is normally fine, but it could cause a few more reads to disk when that memory is essentially “refilled” as data is queried. This means SQL could perform like it would after a restart for a little bit. So don’t change this during the busiest part of your day.