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.
3 thoughts on “Max server memory configuration survey results”
I have quick question.
If I configure the min and max memory to same value (lets say x mb) then what will be performance impact apart from dynamic acquiring/freeing memory? The reason I want to fix both the value to same number to avoid the dynamic change in memory.
I have a server of 48 GB RAM where I am going to configure 36 GB for SQL server and 12 GB will be left for OS. Monitoring the memory usage can tell me either I need to increase the limit from 36 GB to higher.
I just need your advice that how you will view this configuration?
You don’t need to configure min memory, just configure max memory and leave it alone. If you see free memory on the Windows server then you can increase the SQL max memory.
PhysMemKB PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
14679604 790124 9914212 604356 10240 3000
Above is my server setup, i am facing Buffer Cache Hit Ratio of SQL instance “MSSQLSERVER” on computer error.. Can you please suggest possible solution