A long time ago, in a galaxy far, far away I kicked off a survey about memory configuration. Actually it was back at the start of January and I've been terribly remiss about posting the survey results!
I was interested in how the setting of Max Server Memory (which controls the maximum size of the buffer pool) related to the physical memory available on the server.
Thanks to the people who sent me data from 525 servers worldwide.
Here is the data, presented in two charts.
Firstly, when Max Server Memory is actually set:
I had a few data points at the 512GB and 768GB sizes, and their Max Server Memory settings were all valid.
What's interesting in this graph is the wide variety of Max Server Memory settings for any specific amount of physical server memory.
Rather than me explaining how you figure out how much physical memory to reserve for the operating system and other SQL Server memory uses, Jonathan just posted an explanation and loose formula over on his blog – so I'll point you there.
There were a disturbingly large number of SQL Servers that did *not* have Max Server Memory set at all:
These systems may suffer performance problems when the operating system has to pressure the SQL Server buffer pool to give back some memory – it's always better to set a Max Server Memory value – again, see Jonathan's post.
The large spike at 8GB in the graph above is because one person sent me a few hundred sets of results for 8GB servers without Max Server Memory set.
Here's the same set of results without the 8GB spike:
Quick summary: make sure you have an appropriate Max Server Memory setting for your servers to avoid performance problems.