In this entry I am going to cover a handful of common myths and misconceptions about how SQL Server handles memory. Each of these comes from repeated instances of confusion in my dealings with other SQL Server users. Many of these have come up from even very seasoned DBAs so don’t feel bad if you’ve been laboring under a misconception yourself.
I logged in and found my SQL Server is running at 95% memory utilization. I restarted the box which fixed it temporarily, but it grew and grew right back to 95% within a few hours!! SQL Server must have a memory leak! This needs to be fixed!
It’s not a memory leak. It’s also probably not a problem. SQL Server will allocate memory from the OS as needed and hold that memory essentially forever. SQL Server rarely gives memory back to the OS once it has been allocated. If let to run free SQL Server will eventually strangle the OS by taking all of the available memory leaving nothing for the OS to use for normal operation.
There is a reprieve from this risk, however. The max server memory setting (see screenshot below) allows a DBA to put a cap on the amount of memory that SQL Server will allocate to itself. Most SQL Servers will spend most of their time sitting right at this maximum value so I recommend setting this number below the total system memory amount to allow the operating system some room to operate. At least 4GB difference is recommended, but often more can be required depending on overall server load. If the OS is running at or over 99% memory utilization or the page file is being used heavily, consider reducing the max server memory so that the OS has the ability to properly operate.
UPDATE: The 4GB difference is good for servers up to about 32GB of total RAM, but additional RAM should be left for other processes at a rate of about 1GB per 8GB of system memory above 32GB. Even more should be left if there are other processes like SSRS or SSMS that will need memory.
If I set the min server memory to the same value as the max server memory then SQL Server won’t need to allocate or deallocate memory. It can be fixed at the proper amount at startup and never move.
This is one of the most common misconceptions I hear. SQL Server cannot be fixed to a memory amount in this way. In the following screenshots I have set the min server memory setting to the same as the max server memory setting, 4GB. My laptop has 8GB.
|Find this screen by right clicking on a server name in SSMS Object Explorer and choosing Properties.|
I restarted the service and waited several hours. The memory utilization by the SQL Server service never approaches the min server memory setting.
Immediately after service restart.
1 hour later
Several hours later. Nowhere near 4GB yet.
This is because memory is generally only allocated to SQL Server by requests. If there are no requests, there is no memory allocation. In the words of Microsoft…
The amount of memory acquired by the Database Engine is entirely dependent on the workload placed on the instance. A SQL Server instance that is not processing many requests may never reach min server memory. –https://technet.microsoft.com/en-us/library/ms180797(v=sql.105).aspx
So what does the min server memory setting mean then? The min server memory setting means tell the Database Engine how much memory to keep in a scenario where it would otherwise return memory to the OS. If SQL Server had allocated more memory than the minimum server memory amount and needed to return some to the OS for some reason, it would NOT release RAM below the minimum amount. If a request comes in to SQL Server to release RAM and it hasn’t yet reached the minimum, it will not comply with the request.
In the modern VM world it is used by the balloon memory driver. If the balloon memory driver decides that due to an over-allocation on the host and a sudden need for more RAM that some VMs are going to have to lose some of their currently allocated memory, the balloon memory driver will respect the min server memory setting and make sure to leave the VM with at least that much memory. As such, I recommend that this setting be set slightly below the max server memory to allow the balloon memory driver some memory in this hopefully-rare scenario.
My VM administrator says that I’m not using all the memory I asked for. In fact, 70% of it is idle at any given time. We’re going to return that memory to the resource pool to better utilize it on other VMs.
The VM administrators are not lying or misinterpreting what is on their screen. The metrics displayed on their management tool (Microsoft Hyper V Manager or VMWare vSphere Client) are lying to them. When the VM management tool is checking on memory activity it asks the OS. The OS only knows that this memory was allocated to SQL Server a long time ago and that it hasn’t moved since. It appears to the OS to be stagnant and unused and this is what it reports to the VM management tool. However, this memory, once allocated to SQL Server, is in the domain of SQLOS which is likely very actively using this memory in a way that is largely invisible to the OS, and by extension, the VM management tool and eventually the VM administrators themselves. The VM tools are not yet smart enough to ask SQLOS what it is going on with the memory and report falsely that the memory is not being effectively utilized.
What should I check instead? There are many memory metrics available that, when combined, tell the whole memory story, but I am partial to Page Life Expectancy(PLE), especially in OLTP environments. This one statistics give a very macro look at the memory health of a SQL Server. Read more about PLE and what constitutes a good vs bad value here. This query will show PLE values for an instance of SQL Server.
SELECT [object_name], counter_name, instance_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Page life expectancy’
SQL Server doesn’t want more memory. My VM is set to auto allocate more memory if the need arises and it hasn’t done that. If I really needed more memory that would have happened by now.
Consider the facts from the first myth on this page. SQL Server’s memory allocation cannot grow beyond the max server memory setting. Since SQL Server knows that it can’t ask the OS for more, it doesn’t. Only a non-SQL Server operation could cause an apparent memory need to make its way to the VM host to be resolved via a larger memory grant and, by rule, this memory could not find its way to SQL Server anyway.