Friday I’m Not Here
This is a short post to warn you about the memory recommendation tab in the SQL Server installer.
Let’s say you’re doing the smart thing and giving your Standard Edition install 192 GB of RAM:
Why is this smart? Because Standard Edition’s 128GB memory limit is only on the buffer pool. SQL Server can use memory over and above that for other stuff, like:
- Query memory grants
- Compressed backups
- Other caches/Lock memory, etc.
The problem is that the installer’s recommendation only reflects the buffer pool limit. It doesn’t actually consider that memory can get used over that, and you end up with this recommendation:
That’s 128GB exactly, which is daft to the back teeth.
The number I usually start with here is 184320, which is 180GB. That gives Windows about 12GB to work with.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Do you find that 12 GB is enough memory for the OS?
I’ve always used the formula to determine how much memory to leave for the OS as:
4GB for the first 16GB
1 GB for every 8 GB of memory above 16 GB.
With 192GB of memory, this would calculate out to leaving 26 GB of memory for the OS.
In your opinion, is this too much for the OS?
Is there some point where this formula no longer applies and 12 GB for the OS is the most it needs?
The problem with hand-me-down things is that by the time you get them they’re full of holes.
So, are you saying, in your experience, you’ve found that 12 GB left for the OS is sufficient and there’s no need to set aside any more than that, regardless of how much memory the system has?
Also, I assume you are referring to a system that is running only SQL Server and not supporting other services like Reporting Services or Analysis Services.
That recommendation is completely bogus. Someone needs to explain to me why it takes 12.5% of your RAM just for memory management. Anyone knowing how virtual memory works would tell this doesn’t make sense.
The VMM uses page tables, and they don’t take up anywhere near 12.5% of your total RAM. If the OS can manage on 4GB (personally I think 8GB might make more sense), then you don’t need any more RAM set aside even if you have 192GB.